Changing QueryDef with VBA in 2000 (1 Viewer)

anthonyk

New member
Local time
Today, 19:58
Joined
Jan 24, 2000
Messages
5
I have an SQL Pass Through Query that I need to be able to changing the search parameter dynamically (this is Access 2000). I can not use the Access 97 code (does not work ) and the examples from Access 2000 also result in the same error (user-defined type not defined) Here is the SQL Passthrough code for the query named qry_ZONE:

SELECT tbl_SUBNETS.SUBNET
FROM tbl_SUBNETS
WHERE (((tbl_SUBNETS.ZONE)='ZONE 19'));

I want to change the WHERE clause to say ZONE 18, or ZONE 17. I know the code for this should be short. Can anyone tell me what the code would be, including variables for Access 2000. Again, for Access 97 I could have used some like Dim db As Database. Dim qdef As QueryDef. db = Currentdb and so on and son on. This doesn't work. ANy ideas. Thank you in advance.

Anthony
 

anthonyk

New member
Local time
Today, 19:58
Joined
Jan 24, 2000
Messages
5
I don't seem to be getting any replies on this, so let me see if I can help my self out a bit. Below is the exact Access 97 VBA code to make this work. Does anyone know the 2000 equivalent?

Dim dbTest As Database
Dim qdf As QueryDef
Set dbTest = CurrentDb


For Each qdf In dbTest.QueryDefs
If qdf.Name = "query1" Then
qdf.SQL = "SELECT Table1.ID, Table1.ONE, Table1.TWO, Table1.THREE " & _
"FROM Table1 " & _
"WHERE (((Table1.THREE)='" + Me![Text1] + "'));"
DoCmd.OpenQuery qdf.Name
End If
Next qdf
Exit Sub
 

Users who are viewing this thread

Top Bottom