Open form using multiple criteria - VBA

[jlgray0127]

Good afternoon!


Can someone please help? I'm getting the Syntax error and can not figure out what I'm missing!
It's probably been 3 years since I've done anything major in MS Access, so any help is greatly appreciated!

Private Sub Product_DblClick(Cancel As Integer)
DoCmd.OpenForm "12 Week Supply Status_Pastdue", , , "[Product] = '" & Me.Product & " AND [LPA] = '" & Me.LPA & "'"
End Sub

[ranman256]

missed a single quote before the AND. I exaggerated it below:

"[Product] = ' " & Me.Product & " ' AND [LPA] = ' " & Me.LPA & " ' "

[jlgray0127]

Awesome! I did miss that! Thank you!
It cleared my error out and opened the form, but it's not returning the results now.... interesting... it opens with no records to display. There are records. I can open the form and filter each field for the criteria. Perplexed, still. Any thoughts on why that is? They're both just text fields, so should work, I'd have thought.

[ssanfu]

Try

Private Sub Product_DblClick(Cancel As Integer)
     DoCmd.OpenForm "12 Week Supply Status_Pastdue", , , "[Product] = '"  & Me.Product & "'"
End Sub

Are records returned?


Next, try

Private Sub Product_DblClick(Cancel As Integer)
     DoCmd.OpenForm "12 Week Supply Status_Pastdue", , , "[LPA] = '" & Me.LPA & "'"
End Sub

Are records returned?


Are you sure that you have records

WHERE "[Product] = '" & Me.Product & "' AND [LPA] = '" & Me.LPA & "'"

Both conditions must be TRUE for records to be returned.



Maybe you want records

WHERE "[Product] = '" & Me.Product & "' OR [LPA] = '" & Me.LPA & "'"

[jlgray0127]

Thank you for the response!
I do get records to return in both of the first instances.
The records are there, but it has to be "AND", rather than "OR". I need the individuals to be able to open the form and only display the records for the parts on the specific production line they are responsible for.
I'm using the form to drill down, or filter down, to the information they are individually responsible for.
I'm definitely perplexed why it is not working.

[ssanfu]

Would you Post the dB??

[jlgray0127]

I did find a workaround. Thank you!