open Query from Code : Getting Error

[Max Harvey]

Hi,

I have looked at the example called "Open Parameter queries from code"
from the site http://www.mvps.org/access/queries/qry0003.htm

I made up a test which I though looked pretty close (which I will
paste below)

I have put it on the BeforeUpdate event of a form I am using, but
whenever it is called, I get "Run-time error '13':, Type mismatch
Could anybody give me a suggestion of what I did wrong? Probably a
typo, but I really can't spot it :(
Cheers

Max.

Private Sub Form_BeforeUpdate(Cancel As Integer)
' This is a function which will NOT ALLOW data to be entered if it has
conflicting values
' We have a query which is designed to see if to date/times and a room
passed to it would
' conflict with any of the current bookings. If it does, then it has
to NOT allow this
' entry to be entered into the database. A nice message with as much
information as possible
' should be returned... it "This entry for Room ###xx would conflict
with the booking of XXX"
Dim dbs As Database ' The Current Database
Dim rs As Recordset ' Where the query results will be held
Dim qdQry As QueryDef ' The query which will return the conflicts

Set dbs = CurrentDb()
Set qdQry = dbs.QueryDefs("QryTemp")
' MsgBox ("aborts after this statement")
Set rs = qdQry.OpenRecordset() ' Have rs recieve the query
results

MsgBox ("Before Update test message")
End Sub

[TC]

The example is for parameter queries. You have not set any parameters! So if
QryTemp is a parameter query, "pretty close" will not be close enough.

Post the SQL text of your query, and clarify exactly what line of code the
error occurred on. Forget all the comments & stuff, they won't help us find
the problem :-)

HTH,
TC
"Max Harvey" <go****@DELTAL.org> wrote in message
news:7a**************************@posting.google.c om...

Hi,

I have looked at the example called "Open Parameter queries from code"
from the site http://www.mvps.org/access/queries/qry0003.htm

I made up a test which I though looked pretty close (which I will
paste below)

I have put it on the BeforeUpdate event of a form I am using, but
whenever it is called, I get "Run-time error '13':, Type mismatch
Could anybody give me a suggestion of what I did wrong? Probably a
typo, but I really can't spot it :(
Cheers

Max.

Private Sub Form_BeforeUpdate(Cancel As Integer)
' This is a function which will NOT ALLOW data to be entered if it has
conflicting values
' We have a query which is designed to see if to date/times and a room
passed to it would
' conflict with any of the current bookings. If it does, then it has
to NOT allow this
' entry to be entered into the database. A nice message with as much
information as possible
' should be returned... it "This entry for Room ###xx would conflict
with the booking of XXX"
Dim dbs As Database ' The Current Database
Dim rs As Recordset ' Where the query results will be held
Dim qdQry As QueryDef ' The query which will return the conflicts

Set dbs = CurrentDb()
Set qdQry = dbs.QueryDefs("QryTemp")
' MsgBox ("aborts after this statement")
Set rs = qdQry.OpenRecordset() ' Have rs recieve the query
results

MsgBox ("Before Update test message")
End Sub

[Max Harvey]

"TC" <a@b.c.d> wrote in message news:<1069646795.823362@teuthos>...

The example is for parameter queries. You have not set any parameters! So if
QryTemp is a parameter query, "pretty close" will not be close enough.

Post the SQL text of your query, and clarify exactly what line of code the
error occurred on. Forget all the comments & stuff, they won't help us find
the problem :-)

OK, I have ditched my attempt, and tried the one pretty much straight
from the web page..
Now I have a query called QryTemp,
its code is as follows

-----

SELECT tblBooking.BookingID, tblBooking.RoomID, tblBooking.ClientID
FROM tblBooking
WHERE (((tblBooking.BookingID)=[Please Enter Code:]));

-----

As for the code I am using (comments removed)

-----

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim qdfParmQry As QueryDef 'the actual query object
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("QryTemp")
qdfParmQry("Please Enter Code:") = 3
Set rs = qdfParmQry.OpenRecordset()

End Sub

-----

And I even checked to ensure that BookingID = 3 is a valid record, and
displays a result when the query is run by itself :)

As for where it crashes, it crashes on the "Set rs =
qdfParmQry.OpenRecordset()" line (Thats the line highlighted in Yellow
when I go to debug, with "Run-time error '13': Type mismatch
Any assistance would be great.

NB:. This may be of importance... while in the code editor[1], I had
to go to Tools-->References and select "Microsoft DAO 3.6 Object
Library" for it to even reconise "database" as a type. Is it possible
there are more references I need to select which aren't currently
selected[2]?

[1] About reports as... Microsoft Visual Basic 6.3 Version 9108 VBA:
Retail 6.3.8863 Forms3: 2.01
[2] Currently selected referecnes are
- Visual Basic For Application
- Microsoft Access 10.0 Object Library
- Microsoft ActiveX Data Objects 2.1 Library
- OLE Automation
- Microsoft DAO 3.6 Object Library
Thnaks for any help
Max.

[TC]

Ok. Try changing:

Dim db As Database
Dim rs As Recordset
Dim qdfParmQry As QueryDef
to:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdfParmQry As DAO.QueryDef
Also, I'm not sure whether the Parameters collection is the default
collection of a querydef object, so also change:

qdfParmQry("Please Enter Code:") = 3

to:

qdfParmQry.Parameters("Please Enter Code:") = 3

HTH,
TC

"Max Harvey" <go****@DELTAL.org> wrote in message
news:7a**************************@posting.google.c om... "TC" <a@b.c.d> wrote in message news:<1069646795.823362@teuthos>...

The example is for parameter queries. You have not set any parameters! So if QryTemp is a parameter query, "pretty close" will not be close enough.

Post the SQL text of your query, and clarify exactly what line of code the error occurred on. Forget all the comments & stuff, they won't help us find the problem :-)

OK, I have ditched my attempt, and tried the one pretty much straight
from the web page..
Now I have a query called QryTemp,
its code is as follows

-----

SELECT tblBooking.BookingID, tblBooking.RoomID, tblBooking.ClientID
FROM tblBooking
WHERE (((tblBooking.BookingID)=[Please Enter Code:]));

-----

As for the code I am using (comments removed)

-----

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim qdfParmQry As QueryDef 'the actual query object
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("QryTemp")
qdfParmQry("Please Enter Code:") = 3
Set rs = qdfParmQry.OpenRecordset()

End Sub

-----

And I even checked to ensure that BookingID = 3 is a valid record, and
displays a result when the query is run by itself :)

As for where it crashes, it crashes on the "Set rs =
qdfParmQry.OpenRecordset()" line (Thats the line highlighted in Yellow
when I go to debug, with "Run-time error '13': Type mismatch
Any assistance would be great.

NB:. This may be of importance... while in the code editor[1], I had
to go to Tools-->References and select "Microsoft DAO 3.6 Object
Library" for it to even reconise "database" as a type. Is it possible
there are more references I need to select which aren't currently
selected[2]?

[1] About reports as... Microsoft Visual Basic 6.3 Version 9108 VBA:
Retail 6.3.8863 Forms3: 2.01
[2] Currently selected referecnes are
- Visual Basic For Application
- Microsoft Access 10.0 Object Library
- Microsoft ActiveX Data Objects 2.1 Library
- OLE Automation
- Microsoft DAO 3.6 Object Library
Thnaks for any help
Max.

[Max Harvey]

"TC" <a@b.c.d> wrote in message news:<1069818088.506719@teuthos>...

Ok. Try changing:

Dim db As Database
Dim rs As Recordset
Dim qdfParmQry As QueryDef

to:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdfParmQry As DAO.QueryDef

Also, I'm not sure whether the Parameters collection is the default
collection of a querydef object, so also change:

qdfParmQry("Please Enter Code:") = 3

to:

qdfParmQry.Parameters("Please Enter Code:") = 3

HTH,
TC

Thanks TC, everybody,

With the above correctoins I have managed to make it work. :)

I was so glad when I found the web site with the instructions, and
when they didn't work, I thought I was doomed... you guys Rock.

All the best.

Max.