create query def and create recordset.. help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SMA007 - 02 Mar 2006 00:12 GMT

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

Reply to this Message

Albert D.Kallal - 02 Mar 2006 02:57 GMT

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")

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal

Reply to this Message

SMA007 - 02 Mar 2006 16:29 GMT

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

> 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,
[quoted text clipped - 58 lines]
>
> set rc = currentdb.openreocrdset("select * from Required")

Reply to this Message

Albert D.Kallal - 02 Mar 2006 18:12 GMT

>. 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).

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal

Reply to this Message