Variable Search to Return All Like Matches on a form

[molsen123]

Hi All

What a great site!

I am not new to Access, but I am, so far, only a UI developer - not much VBA experience. Using Access 2003.

I have created a simple database for a user to input job orders and produce a 'job sheet' and an invoice, as well as various other reports. It all works fine. There are only two tables, the tbljobs and the tblservices. There could be more than one service, hence the services having its own table.

The user has asked if she can search the 'locations' field in a table, and just return those records where any part of that location matches what she types in. Its a memo field, as there can be many thousands of them and its not something I can build a fixed list into.

I want to build a form where she can type in any word and return all records where there is a possible match (as a continuous form). I know it sounds simple but I just cant get it!

Can anyone assist?

Many thanks

Mark:banghead:

 

[Brianwarnock]

The criteria will be
Like "*" & Forms!formname!controlname & "*"

If there are spaces or special characters, which there shouldn't be, in the object names place them in[]

Brian

 

[John Big Booty]

Welcome to the forum.

You may find the sample here of some interest.

 

[molsen123]

Thanks Brian & John. The very demanding user is UA testing at the moment so I'll give it a go!

Rgds

 

[molsen123]

Hi Again Kind People

I have tried your method via the link in your response, but I get the following "compile error - method or data not found" when typing in the search data. I have added the code to the On Change event property of the SearchFor box:

Private Sub SearchFor_Change()
'Create a string (text) variable
Dim vSearchString As String
'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = 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 QRY_SearchAll
SrchText.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.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", 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.SearchFor.SetFocus
If Not IsNull(Len(Me.SearchFor)) Then
Me.SearchFor.SelStart = Len(Me.SearchFor)
End If
End Sub

I've tried setting this up as per the instructions 3 times now, but still the same error occurs. I would be grateful for any guidance.

Tks

Mark

 

[pr2-eugin]

Make sure you have followed Step 10 and 11 mentioned in the Steps.

 

[molsen123]

Fabulous - Works a treat. Thanks a bunch!

 

[molsen123]

Hi All

I followed the instructions above to the letter (eventually), and it works a treat. However due to renewed inspiration after finding this website and your gold-plated advice, I wanted the user to be able to select a record in the list box and open a form showing all the information within that selected record. In the dbl-click event of the list box, I entered the code below. I first created a form (with a subform) named frmLocationSearchResult as a pop-up. The Location is the field on which the original search is based, and is a memo field. The SearchResults is the unbound list box itself. I keep getting a Compile error: Expected: =.

I have a feeling I'm being a total f***-wit, but I would appreciate your help.


End Sub
Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmLocationSearchResult", , , "[Location]= " &
Me![SearchResults]
End Sub

 

[pr2-eugin]

Well Line breaks are very useful when you have a very long sentence however it is not really needed here.. Also you have to use the Item selected property (I think !!)

Private Sub SearchResults_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmLocationSearchResult", , , "[Location]= " & Me.[SearchResults].ItemsSelected
End Sub

If you do need to use Line breaks, use a Space and Underscore after the &..

Private Sub SearchResults_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmLocationSearchResult", , , "[Location]= " & _
            Me.[SearchResults].ItemsSelected
End Sub

 

[molsen123]

Thanks again - However this time I have a compile error with the .ItemsSelected highlighted as the problem. I'm wondering if I have the wrong field names selected in the code

 

[pr2-eugin]

Yes.. thought that would be a case.. try..

Private Sub SearchResults_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmLocationSearchResult", , , "[Location]= " & _
            Me.[SearchResults].Column(0,Me.[SearchResults].ItemsSelected)
End Sub

 

[molsen123]

Hi All

Apologies - been away for a while.

Still cant get this working. When I click on an address in the list box, I get the following:

Run-time error '3075':

Syntax Error (missing operator) in query expression '[Location]' = Sherwell Rise South, Torquay'.

Cant figure out how I've mixed up.

 

[pr2-eugin]

It is because it is a String.. Change it to..

Private Sub SearchResults_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmLocationSearchResult", , , "[Location]= '" & Me.[SearchResults].Column(0,Me.[SearchResults].ItemsSelected) & "'"
End Sub

 

[molsen123]

Many, Many thanks!

 

[pr2-eugin]

You are welcome.. Glad to Help.. Good day..