Open record from listbox selection

[magster06]

Hello all,

I have a form (Case Search Form) with a listbox. The listbox populates from a query (QSearchAll).
I also have a textbox that I am using as a search field.

The listbox populates as it should and the textbox works as it should by searching the listbox.

What I want to do is that when a user double clicks a selection in the listbox, then I would like a form to open and the record for the "Case Number" be pulled from its table.

The code I am using and some of the form designed is another user:

I have incluede the pic of the form with the listbox:

This is the code for the search box:

Private Sub SearchFor_Change()
'Create a string (text) variable
    Dim strSearchString As String
'Populate the string variable with the text entered in the Text Box SearchFor
    strSearchString = SearchFor.Text
'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QSearchAll
    SrchText.Value = strSearchString
'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
    Me.SearchResults.Requery

'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        'Set the focus on the first item in the list box
            Me.SearchResults = Me.SearchResults.ItemData(1)
            Me.SearchResults.SetFocus
        'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
            DoCmd.Requery
        'Returns the cursor to the the end of the text in Text Box SearchFor,
        'and restores trailing space lost when focus is shifted to the list box
            Me.SearchFor = strSearchString
            Me.SearchFor.SetFocus
            Me.SearchFor.SelStart = Me.SearchFor.SelLength
            
        Exit Sub
    End If
'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus
'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
    DoCmd.Requery
'Returns the cursor to the the end of the text in Text Box SearchFor
    Me.SearchFor.SetFocus
    If Not IsNull(Len(Me.SearchFor)) Then
        Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If
End Sub

Here is the code for the double click event:

Private Sub SearchResults_DblClick(Cancel As Integer)
    Dim strDocName As String
    Dim strLinkCriteria As String
    strDocName = "PSU"
    strLinkCriteria = "[Case Number]=" & Me![SearchResults]
    DoCmd.OpenForm strDocName, , , strLinkCriteria
    
End Sub

When I double click the selection, then of course a parameter box opens up. I know that the [Case Number] =" & Me![SearchResults] is wrong, because it is not returning [Case Number] = [Case Number], but I dont not know how to fix this.

 

[magster06]

I found another thread that had the answer. I am not sure how I missed it, but I did.