Search code examples
vbams-accessms-reports

MS Access Link Criteria from Two Sources

I have a report that is opened and a filtered to a specific record by linking to piece of data. (Lot #)

Private Sub ServiceRequest_Click()
DoCmd.RunCommand acCmdSaveRecord
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ServiceRequest"

stLinkCriteria = "[Lot_Number]=" & "'" & Me![Lot_Number] & "'"

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
End Sub

What I need is an additional filter on the report to happen where [Trade] field is filtered to the selection on the originating form. [Trade] = "tradeselect.value"

I tried simply adding an additional stlinkCriteria, like this....

stLinkCriteria = "[Trade]=" & "'" & Me![TradeSelect] & "'"

but then nothing works. The form just opens on the first record, instead of being filtered to a particular one.

Any help greatly appreciated.

Solution

  • When combining two criteria, you need to do it just like an SQL statement (Field1 = "1" AND Field2 = "2")

    For your case, you can just add the necessary text when adding the second criterium:

    stLinkCriteria = stLinkCriteria  & " AND [Trade]=" & "'" & Me![TradeSelect] & "'"