Dynamically search multiple fields (John Big Booty Super)

[Roger555]

I've managed to incorporate this wonderful dynamic search engine into my database (Link below). However I would like to be able to be able to click on the search results to open a form that contains detailed specs for the item. I already have the form I want to open created I just want to be able to open the item from the dynamic search results.

FYI: The Dynamic search "PKGMaterialQuery" results are populated off a list "PKGMaterial" and I want to double click to open a bound form "PKGMaterialForm" which would be populated off the same list "PKGMaterial"

I will try and upload a sample database but i need to clean up some data first.


Here is the link to the code I'm using (Wont let me post links so filll in www dot below)

access-programmers.co.uk/forums/showthread.php?t=188663


My Exact Code
"Private Sub SearchFor1_Change()
'Create a string (text) variable
Dim vSearchString As String

'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = SearchFor1.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 QRY_SearchAll
SrchText1.Value = vSearchString

'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.SrchText1) <> 0 And InStr(Len(SrchText1), SrchText1, " ", vbTextCompare) Then
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.SearchFor1.SetFocus

If Not IsNull(Len(Me.SearchFor1)) Then
Me.SearchFor1.SelStart = Len(Me.SearchFor1)
End If
End Sub"

Any help would be greatly appreciated!!!!

 

[Roger555]

Anyone able to help me with this? I still have not had a chance to get an upload because I have to remove a lot of information (this is for work). It is exactly like the example posted by john I just want to be able to click on the list box results to open that item in a separate form I created.

 

[AUGuy]

You should look at the On DoubleClick event in the properties for your list box.

In the VBA for that event, you can open the form you're wanting to go to, set the current bookmark to the original listbox value (me.listboxNameHere is one way to get it) and now have it displayed on teh second form.

 

[Roger555]

Thanks I eventually figured it out and have it working using the code below.

Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "ProductDataMasterForm", , , "BPCS='" & Me.SearchResults & "'"
End Sub

 

[eduardo]

Thanks I eventually figured it out and have it working using the code below.

Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "ProductDataMasterForm", , , "BPCS='" & Me.SearchResults & "'"
End Sub

but it crashes if you accidentaly click in a blank space inside the list box right?

 

[John Big Booty]

but it crashes if you accidentaly click in a blank space inside the list box right?

No, unless you have records that have null values in their linking field, or the way you have set your form up is allowing focus to be lost from the list. I have not encountered the problem you describe. This portion of the code;

[COLOR="SeaGreen"]'Set the focus on the first item in the list box[/COLOR]
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus

ensures that the focus is set on the first item in the list and clicking below the last item in the list simply opens the related record that currently has focus.

Check the sample out.

 

[eduardo]

No, unless you have records that have null values in their linking field, or the way you have set your form up is allowing focus to be lost from the list. I have not encountered the problem you describe. This portion of the code;

[COLOR=SeaGreen]'Set the focus on the first item in the list box[/COLOR]
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus

ensures that the focus is set on the first item in the list and clicking below the last item in the list simply opens the related record that currently has focus.

Check theout.

Yeah you're right, I'm checking and it crashes when there are no results, or the focus is lost. Sometimes it loses focus even if there are matches, I don't know why.

Addition: In the form I made for my office (sampe attached) somehow it doesnt focus on the first item, but the second, so if there is only ONE result, the focus gets lost, hence, doubleclicking in a blank space will make it crash.

 

[John Big Booty]

Change the Double Click event to the following;

Private Sub SearchResults_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Provisorias edicion"
    
    [COLOR="DarkOrange"]If IsNull(Me.SearchResults) Then
        MsgBox "please ensure an item is selected from the list"
        Exit Sub
    End If[/COLOR]
    
    stLinkCriteria = "[ID]=" & Me![SearchResults] & ""
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Simply insert the Orange higlighted portion into your current event.

 

[eduardo]

Change the Double Click event to the following;

Private Sub SearchResults_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Provisorias edicion"
    
    [COLOR=DarkOrange]If IsNull(Me.SearchResults) Then
        MsgBox "please ensure an item is selected from the list"
        Exit Sub
    End If[/COLOR]
    
    stLinkCriteria = "[ID]=" & Me![SearchResults] & ""
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Simply insert the Orange higlighted portion into your current event.

Wow, you make it look so easy. Thanks!

 

[John Big Booty]

Another option instead of stopping the event would be to use the Isnull() test to automatically set focus to the first item in the list, thusly;

Private Sub SearchResults_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Provisorias edicion"
    
    If IsNull(Me.SearchResults) Then
        [COLOR="Green"]'Set the focus on the first item in the list box[/COLOR]
        Me.SearchResults = Me.SearchResults.ItemData(0)   [COLOR="Green"]'If you have a header row use one (1) rather than zero (0)[/COLOR]
        Me.SearchResults.SetFocus
    End If
    
    stLinkCriteria = "[ID]=" & Me![SearchResults] & ""
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[eduardo]

Another option instead of stopping the event would be to use the Isnull() test to automatically set focus to the first item in the list, thusly;

Private Sub SearchResults_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Provisorias edicion"
    
    If IsNull(Me.SearchResults) Then
        [COLOR=Green]'Set the focus on the first item in the list box[/COLOR]
        Me.SearchResults = Me.SearchResults.ItemData(0)   [COLOR=Green]'If you have a header row use one (1) rather than zero (0)[/COLOR]
        Me.SearchResults.SetFocus
    End If
    
    stLinkCriteria = "[ID]=" & Me![SearchResults] & ""
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

That's even better man, I'll try it out.

And it'd be handy a on-key-press event besides the on-double-click one. For, example, pressing INTRO takes you to the selected record. I tried but it only worked for the first result, kinda weird.

 

[eduardo]

Another option instead of stopping the event would be to use the Isnull() test to automatically set focus to the first item in the list, thusly;

Private Sub SearchResults_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Provisorias edicion"
    
    If IsNull(Me.SearchResults) Then
        [COLOR=Green]'Set the focus on the first item in the list box[/COLOR]
        Me.SearchResults = Me.SearchResults.ItemData(0)   [COLOR=Green]'If you have a header row use one (1) rather than zero (0)[/COLOR]
        Me.SearchResults.SetFocus
    End If
    
    stLinkCriteria = "[ID]=" & Me![SearchResults] & ""
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Hello again, I used both solutions. The message box pops up when you double click and there are NO results at all (instead of crashing, a message box is perfect)
And the latest solution you posted, works perfectly to set focus on an item that lost it. So both solutions are useful included in the same code.

 

[janeyg]

Hi, I too would like to select the record and open another form to that record. I have modified the code above to my form info but it get an error message 3075 missing syntax. I can't see where?


My code is (which I tried in click and dbl click event)


Private Sub SearchResults_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmResourcesbyStepOneRecord"

If IsNull(Me.SearchResults) Then
'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1) 'If you have a header row use one (1) rather than zero (0)
Me.SearchResults.SetFocus
End If

stLinkCriteria = "[Title]=" & Me![SearchResults] & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub



Any help would be much appreciated.
thanks
Janeyg

 

[John Big Booty]

Which line is that get highlighted when you debug the code?

 

[janeyg]

Wow, it worked using just this code

Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmResourcesbyStepOneRecord", , , "Title='" & Me.SearchResults & "'"
End Sub

thanks!

 

[janeyg]

This one

DoCmd.OpenForm stDocName, , , stLinkCriteria