Object variable or With block variable not set (??)

[Lee]

Hi guys,
Can you see what I've done wrong in the code below? I'm
trying to create an 'insert into' query in sql and then
run it but I keep getting the above error. Do you know
why? Here's the main code:
Dim db As Database
Dim qdf As QueryDef

stSQL = "INSERT INTO tbl_LettersInsp (InspectorID, ToID,
FromID, LetterDate, LetterNotes)"
stSQL = stSQL & "VALUES (Forms!frm_LettersInspectors!
txtID, '2', '3', Now(), 'NEW TEST');"
Set qdf = db.CreateQueryDef("qry_AddLetter", stSQL)
DoCmd.OpenQuery qdf.Name

Any help or ideas would be greatly appreciated!

Regards,

Lee

 

[JulieD]

Hi lee

add the line

set db=currentdb

after you dim & before you start to build your sql statement

Cheers
JulieD

 

[Jeff Boyce]

Lee

I don't see where you "Set" your database object.

You didn't mention which version of Access you are using. Is there a chance
Access is confused about whether your "Database" is DAO or ADO?

 

[Lee]

Thanks Jeff.
I've just worked out what I did wrong...I kept creating
QueryDefs when I only wanted just to set the values...and
I did omit to Set the Database variable as well!!! Doh!

Regards,

Lee