VBA Code to show query results

[JSalle0826]

Hello all,

I have another issue. I'm still working on my code for over 2 weeks now and I've made great strides, but I have one final hurdle.

I am able to list results of my query in a VBA drafted email, however I cannot put any criteria on it to limit the results. I have a form that is tied to my VBA code and when I click it, it will draft the email and list details on the form. The last item I need to list are items contained in a subform.

Currently I get results, however I get all results for all records and not the current record.

Normally in the query I would input a simple line like: [Forms]![Supply Requests]![Request ID] to return the items only for the specific record. However when I do that on the query, the VBA code crashes.

Any suggestions on how to implement this criteria in the VBA Code?

Piece of the code I have thus far:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SupplyRequests1", dbOpenForwardOnly)
With rst
Do While Not .EOF
MyBodyText = MyBodyText & vbNewLine & ![Quantity] & " - " & ![Product Name] & vbCrLf
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing

I want to set the Request ID in the query results equal to the Request ID in the form so it will only pull the results I need.

Can anyone help me??

Thanks.

Jeff

 

[Beetle]

As long as the form is open, and at the correct record, then something like the following should work;

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

'this assumes [Request ID] is numeric. If text you will need to add delimiters.
strSQL = "Select * From SupplyRequests1 Where [Request ID]=" & Forms![Supply Requests]![Request ID]

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenForwardOnly)
With rst
Do While Not .EOF
MyBodyText = MyBodyText & vbNewLine & ![Quantity] & " - " & ![Product Name] & vbCrLf
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing

 

[JSalle0826]

Awesome!!! It works...thank you, thank you , thank you!!!! You are a life saver!!!

 

[Beetle]

You're welcome