Error on QueryDef on Close

[sara]

Hi -
All is fine if I open my form, do something, then close it.

However, if I just open it, then press the Close button (or go into
design view), I get
"object variable or with Block variable not set" error 91 on
mqdf.Close

The following code appears at the top of the module: (someone wrote it
for me)
Private mdb As DAO.Database
Private mqdf As DAO.QueryDef
Private mstrSQL As String

And when I "use" the form (don't just open and close), I execute:
'SQL to append the new record
mstrSQL = "INSERT INTO trelAdDept ( AdKey, DeptNum ) " _
& "VALUES (" & lngAdKey & ", " & lngDeptKey & ")"

'Create a temporary querydef object based on the above SQL stmt
Set mqdf = mdb.CreateQueryDef("", mstrSQL)

'Execute the querydef
mqdf.Execute dbFailOnError

It seems, though, that if I haven't executed the above code, I get the
error when I just try to close the form.

Thanks for the help! I couldn't find this in any prior postings.
sara

[John Welch]

I notice that you haven't set mdb to point to anything. It's a database
type, but it doesn't point to any particular database until you say
something like:
set mdb= currentdb
I think this will help with your error. But you don't have to bother with
querydefs at all- you can just say:
mdb.execute mstrSQL, dbfailonerror

hope this helps
-John

"sara" <sa*******@yahoo.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...

Hi -
All is fine if I open my form, do something, then close it.

However, if I just open it, then press the Close button (or go into
design view), I get
"object variable or with Block variable not set" error 91 on
mqdf.Close

The following code appears at the top of the module: (someone wrote it
for me)
Private mdb As DAO.Database
Private mqdf As DAO.QueryDef
Private mstrSQL As String

And when I "use" the form (don't just open and close), I execute:
'SQL to append the new record
mstrSQL = "INSERT INTO trelAdDept ( AdKey, DeptNum ) " _
& "VALUES (" & lngAdKey & ", " & lngDeptKey & ")"

'Create a temporary querydef object based on the above SQL stmt
Set mqdf = mdb.CreateQueryDef("", mstrSQL)

'Execute the querydef
mqdf.Execute dbFailOnError

It seems, though, that if I haven't executed the above code, I get the
error when I just try to close the form.

Thanks for the help! I couldn't find this in any prior postings.
sara

[pietlinden]

sounds picky of me, but why not just build the SQL statement and then
use

db.Execute mstrSQL, [dbFailOnError]

no querydef required....

Just wondering, that's all.

[sara]

Private Sub Form_Load()
On Error GoTo Err_Form_Load

Set mdb = CurrentDb

I thought this would execute every time the form opens or loads. But
just in case, I tried it on Form_Open event as well. I still get the
same error.

A guy from my user group gave me this code (the group only meets
monthly, and I was hoping to have this working before then!). I'm new
to all this, but learn well by example and explanation. That's why the
code is as it is.

Any other ideas?
Thanks

[David W. Fenton]

pi********@hotmail.com wrote in
news:11**********************@j33g2000cwa.googlegr oups.com:

sounds picky of me, but why not just build the SQL statement and
then use

db.Execute mstrSQL, [dbFailOnError]

no querydef required....

Just wondering, that's all.

I don't see it as picky at all. I almost never edit querydefs in
DAO. I seldom even open them.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/