Pass-through query with reference to a form

  • Thread starter Thread starter aydin
  • Start date Start date
A

aydin

I am trying to build a pass-through query which is
supposed to pull criteria that the user enters in a form.
As far as I know, a pass-through query cannot interact
with Jet and therefore it cannot refer to a field in a
form. To overcome this problem, I wrote the following code
using QueryDef, but I was not able to make this code work.
It does not give an error, but it does not bring up any
records either. Can you tell me what I am missing or where
I am making a mistake?
......

Private Sub Command70_Click()

Dim MyDb As DAO.Database, MyQry As QueryDef, MyRS As
DAO.Recordset
Set MyDb = CurrentDb()
Set MyQry = MyDb.CreateQueryDef("")

MyQry.SQL = "select flight_id, flight_date from
flight_table where flight_number = " & [Forms]![Retrieve
Forecasts]![Enter Flight Number]

MyQry.ReturnsRecords = True
Set MyRS = MyQry.OpenRecordset()
MyRS.MoveFirst

Debug.Print MyRS!flight_id, MyRS!flight_date

MyQry.Close
MyRS.Close
MyDb.Close

End Sub
 
aydin said:
I am trying to build a pass-through query which is
supposed to pull criteria that the user enters in a form.
As far as I know, a pass-through query cannot interact
with Jet and therefore it cannot refer to a field in a
form. To overcome this problem, I wrote the following code
using QueryDef, but I was not able to make this code work.
It does not give an error, but it does not bring up any
records either. Can you tell me what I am missing or where
I am making a mistake?
.....

Private Sub Command70_Click()

Dim MyDb As DAO.Database, MyQry As QueryDef, MyRS As
DAO.Recordset
Set MyDb = CurrentDb()
Set MyQry = MyDb.CreateQueryDef("")

MyQry.SQL = "select flight_id, flight_date from
flight_table where flight_number = " & [Forms]![Retrieve
Forecasts]![Enter Flight Number]

MyQry.ReturnsRecords = True
Set MyRS = MyQry.OpenRecordset()
MyRS.MoveFirst

Debug.Print MyRS!flight_id, MyRS!flight_date

MyQry.Close
MyRS.Close
MyDb.Close

End Sub
Click to expand...

What data type is the [flight_number] field?
 
aydin said:
Flight_number is a 4-digit integer. There is also a date
field that I am trying to refer to.
Click to expand...

The date field doesn't seem relevant to the SQL you showed me, but
please check the table design itself and report the Data Type and Field
Size of flight_number. It may be that this 4-digit number is actually a
text field, since it's not being used to calculate with, and the number
you're entering on the form may need to be formatted to match it.

I note in your code that you're setting the querydef's ReturnsRecords
property, but not (as shown) first setting is Connect property.
Although I haven't built a pass-through query in code, I see in the help
file that the Connect property must be set to an appropriate connect
string beginning with "ODBC;" before you set the ReturnsRecords
property.

It may be best, though to test out the query as a simple Jet query, just
to make sure you have the SQL right and can get the records you want
that way; then transform it into a pass-through query.
 
Also, a slight correction: you are using a normal SELECT Query, not a
Pass-through Query.
 
Back
Top