Open specific record - Syntax

[testdba]

I am trying to use the double click event of a listbox to open a form to a specific record. The problem I have is when I double click a record, Access pops up a box asking for PONumber (which is in column 0 of the listbox).

Here is the code that I am using:

Private Sub lstPOInfo_DblClick(Cancel As Integer)

Dim boStatus As Boolean
Dim strDocName As String
Dim strLinkCriteria As String

    strDocName = "Orders"

    ' function checks to see if the form is open or not
    boStatus = fIsLoaded(strDocName) 
    
    If boStatus = True Then
       strLinkCriteria = "[PONumber]=" & Me.lstPOInfo.Column(0)
       
       Forms![Orders].FilterOn = True
       Forms![Orders].Filter = strLinkCriteria
        
    Else
        strLinkCriteria = "[PONumber]=" & Me.lstPOInfo.Column(0)
        DoCmd.OpenForm strDocName, , , strLinkCriteria
    End If
    
End Sub

I'm sure it's a syntax error on the "strLinkCriteria = "[PONumber]=" & Me.lstPOInfo.Column(0)" line.

 

[wazz]

try removing
"[PONumber]=" &

 

[testdba]

try removing
"[PONumber]=" &

I tried it without "[PONumber]=" & and it still opens the box and it no longer links opens the correct record.

 

[Pat Hartman]

Assuming that there is no typo in the field name, try swapping the .Filter and .FilterOn.

 

[testdba]

Eureka!

I found it. I looked back through some old posts and databases that I had and found something that was closely related. I don't understand the syntax, but it works. I changed the line to strLinkCriteria = "[PONumber]= '" & Me.lstPOInfo.Column(0) & "'".

Here is the working code:

Private Sub lstPOInfo_DblClick(Cancel As Integer)

Dim boStatus As Boolean
Dim strDocName As String
Dim strLinkCriteria As String

    strDocName = "Orders"
    boStatus = fIsLoaded(strDocName)
    
    If boStatus = True Then
       strLinkCriteria = "[PONumber]= '" & Me.lstPOInfo.Column(0) & "'"
       
       Forms![Orders].Filter = strLinkCriteria
       Forms![Orders].FilterOn = True
        
    Else
        strLinkCriteria = "[PONumber]= '" & Me.lstPOInfo.Column(0) & "'"
        DoCmd.OpenForm strDocName, , , strLinkCriteria
    End If
    
End Sub

Thanks for your help!

P.S. I did switch the two filter lines around Pat.

 

[Pat Hartman]

Whenever your criteria field is text, you need to surround it with quotes. If the field is a date, you need to surround it with #.