How do I link a lookup query to an ADO table?

[Guest]

I have a process which will read through a database, assign report variables
and print a report. Below is a sample.

01 Sub Print_Mailing()
02 Set rstl = New ADODB.Recordset
03 With rstl
04 .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
05 "Data Source=C:\mailing\mailing.mdb;"
06 .Open "MailingSample", , , , adCmdTable
07 End With
08
09 'LOOP THROUGH TABLE
10 Do Until rstl.EOF
11 If IsNull(rstl.Fields(11)) = True Then 'set Name
12 strName = Trim(rstl.Fields(11))
13 'OPEN SETTINGS QUERY AND TRANSFER VALUES TO REPORTS TABLE
14 DoCmd.SetWarnings False
15 DoCmd.OpenQuery "qmakReportMailing" '<==This is what I am having
a problem with.
16 rstl.MoveNext
17 Loop
18 rstl.Close
19 Set rstl = Nothing
20 End Sub

I would like Line 15 to run a qmake query based upon the value of the ID of
the current record in the ADO table.

Below is a listing of this query as I used it to tie to an Input Form:

qmakReportMailing: (when connected to Input Form--this works)
SELECT tblInput.insInputID
FROM tblInput
WHERE (((tblInput.insInputID)=[Forms]![frmInput]![insInputID])

How do I or what is the syntax to point directly to the database current
record without using a form?
The database id field is rstl.Fields(1) named "ID" in the database definition.

qmakReportMailing: (when connected to Mailingsample database)
SELECT MailingSample.ID
FROM MailingSample
WHERE (((MailingSample.ID)=?????)

Thanks for your help!!

 

[Alex Dybenko]

Hi,
i think the simple solution is to have a hidden form loaded,
then you can use a textbox on it to filter your query, and set this textbox
to id of current record in your code:
Foms("MyForm").txtID=rstl("ID")

 

[Guest]

Thanks Alex,

I did a little twist to your recommendation. I thought I would respond to
your answer so others could find the solution.

I was not able to directly set the value of the ID field in the text box on
the hidden input form. What I did was set up a record lookup instead. It
looks like this:

DoCmd.GoToRecord acDataForm, "frmMailing", acGoTo, rstl("ID")

Thanks again for your assistance!!

Hi,
i think the simple solution is to have a hidden form loaded,
then you can use a textbox on it to filter your query, and set this textbox
to id of current record in your code:
Foms("MyForm").txtID=rstl("ID")

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

I have a process which will read through a database, assign report
variables
and print a report. Below is a sample.

01 Sub Print_Mailing()
02 Set rstl = New ADODB.Recordset
03 With rstl
04 .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
05 "Data Source=C:\mailing\mailing.mdb;"
06 .Open "MailingSample", , , , adCmdTable
07 End With
08
09 'LOOP THROUGH TABLE
10 Do Until rstl.EOF
11 If IsNull(rstl.Fields(11)) = True Then 'set Name
12 strName = Trim(rstl.Fields(11))
13 'OPEN SETTINGS QUERY AND TRANSFER VALUES TO REPORTS TABLE
14 DoCmd.SetWarnings False
15 DoCmd.OpenQuery "qmakReportMailing" '<==This is what I am
having
a problem with.
16 rstl.MoveNext
17 Loop
18 rstl.Close
19 Set rstl = Nothing
20 End Sub

I would like Line 15 to run a qmake query based upon the value of the ID
of
the current record in the ADO table.

Below is a listing of this query as I used it to tie to an Input Form:

qmakReportMailing: (when connected to Input Form--this works)
SELECT tblInput.insInputID
FROM tblInput
WHERE (((tblInput.insInputID)=[Forms]![frmInput]![insInputID])

How do I or what is the syntax to point directly to the database current
record without using a form?
The database id field is rstl.Fields(1) named "ID" in the database
definition.

qmakReportMailing: (when connected to Mailingsample database)
SELECT MailingSample.ID
FROM MailingSample
WHERE (((MailingSample.ID)=?????)

Thanks for your help!!