Search Query not working.

[kacey8]

Hi all, Me Again!!!!...


So I had a search query I was using for a previous DB which worked perfectly. I am trying to use it for a new DB but the Macro for the search box doesn't seem to work.

I can't remember where I got the search code from originally (might have been here) but it worked so well.

Anyway.. here is what I have.

3 x unbound text boxes called
SearchFor
SearchResults
SrchText (This one is hidden from view)

I have a query (called QRY_SearchName) set up with the following added for the Field I am searching [Address 2] in the criteria Like "*" & [forms]![SearchName]![SrchText] & "*"

And then I have the Macro on "Change" for the SearchFor box.

Private Sub SearchFor_Change()
'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_SearchName
    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.[B][COLOR=red]ItemData(1)[/COLOR][/B]
    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

The area in bold and red is where Access is highlighting the error. now I did change the names of the queries used in the search but I thought I found all the problems.

Help would be very much appreciated.

 

[BlueIshDan]

If SearchResults is a text box you'll need to say SearchResults.Value to get its value.

ItemData(#) if to listboxes and combo boxes.

 

[kacey8]

Thats my mistake... SearchResults is suppose to be a list box not a text box.

New issue now. It isn't displaying any results in the listbox :/

 

[BlueIshDan]

Ok so now you need to decide what you want to see in this list box.
What field, from what table?

 

[kacey8]

I had thought I had done that. It should be a field called "Address 2" it searches through and then display the matches so the user can select the record they want which then loads into the form bellow.

It works perfectly on my other DB when searching for a name, I guess I am changing it without doing it correctly.

 

[BlueIshDan]

I just need to know the table and field. and on which event you wish to populate this list.

 

[kacey8]

Table is called dunmow and field is [Address 2]

 

[BlueIshDan]

So what is your list boxes row source?

 

[kacey8]

Row source:

SELECT [QRY_SearchName].[Address 2] FROM [QRY_SearchName] ORDER BY [Address 2];

Basically an edited version of my old one I used which was

SELECT [QRY_SearchAll].[Name] FROM [QRY_SearchAll] ORDER BY [Name];

(the bottom one worked perfectly

 

[kacey8]

This whas the guide I was using

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

 

[BlueIshDan]

Table is called dunmow and field is [Address 2]

Row source:

SELECT [QRY_SearchName].[Address 2] FROM [QRY_SearchName] ORDER BY [Address 2];

I think you have answered your own question here.

Replace [Qry_SearchAll] which is the old database's table name with the current databases table name [dunmow]

SELECT [dunmow].[Address 2] FROM [dunmow] ORDER BY [Address 2];

 

[kacey8]

EDIT:

Sorry, this was my mistake, my row source

SELECT [QRY_SearchName].[Address 2] FROM QRY_SearchNAme ORDER BY [Address 2];

Works perfectly (except I accidently put it in Row Source Type.

QRY_SearchName is the query used for the search (not a table)

All works.. Thank you very much for your assistance.

 

[BlueIshDan]

You told me that your table name is dunmow lol

 

[kacey8]

You told me that your table name is dunmow lol

Hi,

Sorry yes my table is called Dunmow but the search box doesn't search a table, it searches through a query (as the query will order it)

I just followed the guide posted in the link
http://www.access-programmers.co.uk/forums/showthread.php?t=188663

This uses a listbox which source is a Query, not a table.

number 10 of the guide doesn't use a table as the source, but a query.

1. Save and close your query and go back to our form FRM_SearchMulti, now put a list box on the form use your Query QRY_SearchAll as the source for the List box when promoted by the wizard, and follow the bouncing ball from there, call this List Box SearchResults.

Obviously I have replaced the FRM (form) and QRY (Query) names.

 

[kacey8]

Okay...

So now I have a list of the results, I have no idea how to make the result I click load on the form listed bellow.