If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
create query def and create recordset.. help
I am a new access user migrating from Approach. I discovered this code in a
previous posting by Van T. Dinh... I have a form based on a table=newhire, I have another table = required, both have a field named "rec_" there are 6 fields of data that I want to auto fill for the user on the afterupdate event of the "rec_" field on my form if a rec_ exists. the code errors at.... Set rc = Qr.OpenRecordset(dbOpenDynaset) The error is 'runtime error '424' : Object required... Any guidance would be greatly appreciated. Below is the entire code... Private Sub REC__AfterUpdate() Dim strSql As String Dim rc As DAO.Recordset If IsNull(Me.REC_) Then Me.JOB_NAME = Null Me.DICIPLINE = Null Me.ST = Null Me.OT = Null Me.PD = Null Me.BONUS_TRAV = Null Else strSql = "SELECT required.job_name, required.disc, required.st, required.ot, required.pd, required.bonustrave, FROM Required WHERE rec_ = Me.rec_ & """"" Set rc = Qr.OpenRecordset(dbOpenDynaset) If rc.RecordCount 0 Then Me.JOB_NAME = rc!JOB_NAME Me.ST = rc!ST Me.OT = rc!OT Me.PD = rc!PD Me.BONUS_TRAV = rc!BONUS_TRAVE End If rc.Close End If Set rc = Nothing End Sub Thanks Brian |
#2
|
|||
|
|||
create query def and create recordset.. help
My spider sense tells me that when you "copy" a record like that, you
actually should be using the relational abilities of the database system, and NOT have to copy the same data over an over (this sounds like a normalizing issue). However, perahps these fields are just defaults you want..... Having said the above,,,you code could be Private Sub REC__AfterUpdate() Dim strSql As String Dim rc As DAO.Recordset If IsNull(Me.REC_) Then Me.JOB_NAME = Null Me.DICIPLINE = Null Me.ST = Null Me.OT = Null Me.PD = Null Me.BONUS_TRAV = Null Else strSql = "SELECT job_name, disc, st, ot, pd, bonus_trave " & _ " FROM Required WHERE rec_ = " & Me.rec_ now, if me.rec_ is a text type field, then the above needs to be " FROM Required WHERE rec_ = '" & Me.rec_ & "'" note, for readability...I willl put extra spaces in the above string...but remove them " FROM Required WHERE rec_ = ' " & Me.rec_ & " ' " So, the above has extra spaces in it so you read this post better.... (again, ONLY use quotes if the me.rec_ is a text type field). set rc = currentdb.OpenReocordSet(strSql) If rc.RecordCount 0 Then Me.JOB_NAME = rc!JOB_NAME Me.ST = rc!ST Me.OT = rc!OT Me.PD = rc!PD Me.BONUS_TRAV = rc!BONUS_TRAVE End If rc.Close Set rc = Nothing End If End Sub I should also note that strSql = "SELECT required.job_name, required.disc, required.st, required.ot, required.pd, required.bonustrave, FROM Required WHERE rec_ = Me.rec_ & """"" Set rc = Qr.OpenRecordset(dbOpenDynaset) how does the set rc have any relation to the strsql text? (it does not...... So, when you use the openrecordset, you need to supply it with either a table name, or raw sql eg: set rc = currentdb.openreocrdset("select * from Required") -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#3
|
|||
|
|||
create query def and create recordset.. help
Thanks for the detailed response. Your spider sense is very correct. We are
currently passing data between 2 systems until they are both re-written (approach to Access). I have refined my code as suggested. (Some field names were not correct as well)There seems to be a problem with my SQL string. Also, My rec_ fields in both tables are numeric. I do not understand the &"""" at the end of the string. my current error is- Runtime error 3141 - "The select statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect" strSql = "SELECT required.job, required.disc, required.st, required.ot, required.pd, required.bonustrave, FROM Required WHERE rec_ =Me.rec_ & """"" Set rc = CurrentDb.OpenRecordset(strSql) ......Attached.... Entire new code below. Private Sub REC__AfterUpdate() Dim strSql As String Dim rc As DAO.Recordset If IsNull(Me.REC_) Then Me.JOB_NAME = Null Me.DICIPLINE = Null Me.ST = Null Me.OT = Null Me.PD = Null Me.BONUS_TRAV = Null Else ' my string 'strSql = "SELECT job, disc, st, ot, pd, bonustrave FROM Required WHERE rec_= Me.rec_" strSql = "SELECT required.job, required.disc, required.st, required.ot, required.pd, required.bonustrave, FROM Required WHERE rec_ =Me.rec_ & """"" 'Rec_ is numeric in both tables Set rc = CurrentDb.OpenRecordset(strSql) If rc.RecordCount 0 Then Me.JOB_NAME = rc!JOB Me.ST = rc!ST Me.OT = rc!OT Me.PD = rc!PD Me.DICIPLINE = rc!DISC Me.BONUS_TRAV = rc!BONUSTRAVE End If rc.Close Set rc = Nothing End If End Sub "Albert D.Kallal" wrote: My spider sense tells me that when you "copy" a record like that, you actually should be using the relational abilities of the database system, and NOT have to copy the same data over an over (this sounds like a normalizing issue). However, perahps these fields are just defaults you want..... Having said the above,,,you code could be Private Sub REC__AfterUpdate() Dim strSql As String Dim rc As DAO.Recordset If IsNull(Me.REC_) Then Me.JOB_NAME = Null Me.DICIPLINE = Null Me.ST = Null Me.OT = Null Me.PD = Null Me.BONUS_TRAV = Null Else strSql = "SELECT job_name, disc, st, ot, pd, bonus_trave " & _ " FROM Required WHERE rec_ = " & Me.rec_ now, if me.rec_ is a text type field, then the above needs to be " FROM Required WHERE rec_ = '" & Me.rec_ & "'" note, for readability...I willl put extra spaces in the above string...but remove them " FROM Required WHERE rec_ = ' " & Me.rec_ & " ' " So, the above has extra spaces in it so you read this post better.... (again, ONLY use quotes if the me.rec_ is a text type field). set rc = currentdb.OpenReocordSet(strSql) If rc.RecordCount 0 Then Me.JOB_NAME = rc!JOB_NAME Me.ST = rc!ST Me.OT = rc!OT Me.PD = rc!PD Me.BONUS_TRAV = rc!BONUS_TRAVE End If rc.Close Set rc = Nothing End If End Sub I should also note that strSql = "SELECT required.job_name, required.disc, required.st, required.ot, required.pd, required.bonustrave, FROM Required WHERE rec_ = Me.rec_ & """"" Set rc = Qr.OpenRecordset(dbOpenDynaset) how does the set rc have any relation to the strsql text? (it does not...... So, when you use the openrecordset, you need to supply it with either a table name, or raw sql eg: set rc = currentdb.openreocrdset("select * from Required") -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#4
|
|||
|
|||
create query def and create recordset.. help
. I do not understand the &"""" at the end of the
string. You don't need it....and my example has not such. Remember, in sql, when you type in the sql, for numbers, you do NOT need quotes. select * from tblCustomer where id = 123 However, to select a text field, you need quotes select * from tblCustomer where City = 'Edmonton' So, the "reason" why you need the 'quotes' is to simply form some correct sql... strSql = "SELECT required.job, required.disc, required.st, required.ot, required.pd, required.bonustrave, FROM Required WHERE rec_ =Me.rec_ & """"" to test the above...in your code, place a msgbox as follwing msgbox strSql The above expression will produce SELECT required.job, required.disc, required.st, required.ot, required.pd, required.bonustrave, FROM Required WHERE rec_ =Me.rec_ " You can well see that the above is incorrect...you even have a stray " at the end... Do you really want to search for a rec_ =me.rec_ ? how about Do you really want to search for a rec_ =zoo ? what happens if you did in fact have a value of Me.rec_ in me.rec_? How would ms-access know to search for a the value of Me.rec_ ? Is that the text called Me.rec_...or do you want the value INSIDE of me.rec_ ? You need to learn how to write code to build a string... strSql = "hello" msgbox strSql ---hello strSql = strSql & "how are" msgbox strSql ---hello how are strSql = strSql & " ' " & "you" & " ' " msgbox strSql ---hello how are ' you ' note the resulting string. So, you are building up a LEGAL sql string.... so, your stament of strSql = "SELECT required.job, required.disc, required.st, required.ot, required.pd, required.bonustrave, FROM Required WHERE rec_ =Me.rec_ & """"" is wrong. try strSql = "SELECT job, disc, st, ot, pd, bonustrave " & _ " FROM Required WHERE rec_ = " & Me.rec_ msgbox strSql debug.print strSql (remember, once you get the code working....you can remove the msgbox command, and the debug.print) note also... required.pd, required.bonustrave, FROM Required WHERE rec_ =Me.rec_ & """"" -----------------------------------^ You have a extra comma. Simply replace the above with my example code..... So, try the above..and also use the debug.print. after you run the code...you can do a ctrl-g..and look at what the sql looks like...you can even cut and paste the sql into the query builder...or you next response here. So, when you use the debug.print......now you can cut and paste the sql into your response here...what does the sql look like that debug.print produced? (this assumes you first fix the sql code). -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
Thread Tools | |
Display Modes | |
|
|