Select Records For Report Not Working

[ler1992]

Can anyone tell me why this isn't working?

From frmMenu the user clicks button Generate Report. In code I want to read the value of field vendor_id from table tblVendorID and put it into variable strID. I want to use this variable to open report rptApprovals. This report's record source is tblApprovals. When I run through my code with debug I can see that the value of strID is getting set correctly. Does something in my code need to be changed or is it something in my report?

Dim rstVendorID As DAO.Recordset
Dim strID As String

Set rstVendorID = CurrentDb.OpenRecordset("tblVendorID")
rstVendorID.MoveFirst
strID = rstVendorID.Fields("vendor_id").Value

DoCmd.OpenReport "rptApprovals", acPreview, , strID

Is this even possible? I've tried everything I can think of. Any help you can give is appreciated.
Lisa R.

 

[wh00t]

Dim rstVendorID As DAO.Recordset
Dim strID As String

Set rstVendorID = CurrentDb.OpenRecordset("tblVendorID", DB_OPEN_DYNASET)
rstVendorID.MoveFirst
strID = rstVendorID.Fields("vendor_id").Value

DoCmd.OpenReport "rptApprovals", acPreview, , strID

 

[ler1992]

I added DB_OPEN_DYNASET to the Set rstVendorID = CurrentDb.OpenRecordset("tblVendorID") line but got the same results.

Other ideas?

 

[ler1992]

Pat,

With the syntax

strID = "vendor_id = " & rstVendorID.Fields("vendor_id")

my strID variable is set to "vendor_id = 1181" (1181=the vendor id in the table) this, generates a "Data type mismatch" error

With the code I have, in debug I can see that the value of strID is being set correctly (1181). However, when the report opens it opens with all records in tblApprovals not just the records matching the value in strID. Sorry I left that part out of my org. post.

As for the usefulness of this code, once I get this working I will add a Loop to loop through and generate a snapshot report for each vendor_id in the tblVendorID table. I use tblVendorID table to set the strID variable to locate the records I want from tblApprovals.

My VBA knowledge is VERY limited so if there is a better way to do what I'm trying to do, someone please tell me.