Create a Search using Surnames, Firstnames and Invoice Numbers

[Mrx]

Hi All!
I know that this will probably be a simple question for all of you experts
but I've been pulling my hair out while trying to get this search to work!!
Going around in circles is driving me nuts!!!
This is what I want to be able to do if you follow this link
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=15211&d=1161158846

But I can't see to make it work with my database I'm not to sure what values
I have to change in the expressions etc.
To make this question short I want to be able to create a search in my Form
which searchs for Surnames, Firstnames and Invoice Numbers to bring up all of
the fields (for instance I also have some fields called: Notes, Location ID
and Contact ID) from that record to the form.
I have tried so many ways trying to make it work! From lists, combo boxes and
form filtering, but if I can get this sample database to work on mine it
would be great! I'm just trying to make it as User friendly as I can for
staff.
I have copied the Query1 in the sample to my Query (Named: SearchRecords)
with the relevent Fields selected for example Surnames, Firstnames and
Invoice Numbers have in the Criteria: Like "*" & [Forms]![Search]![Search2] &
"*"

Thank you for your help in advance! Please tell me if I haven't worded my
question right or if you need more infomation!!

 

[Anthos]

The way this query is working is that in the first returned field they
have combined all the fields that they want to search into one, then
they have used a like expression on the criteria.

(note sql code not written with 100% correct syntax, hopefully written
in a way that makes it easy to understand thogh)

eg. if you want to search on firstname and lastname they have done the
following.
select firstname & " " & lastname as search, firstname, lastname from
contacts where search like "*" & textfieldonform & "*"

If you wanted to include invoicenumbers then you would change it as
such
select firstname & " " & lastname & " " & invoicenumber as search,
firstname, lastname, invoicenumber from contacts where search like "*"
& textfieldonform & "*"

The more fields you add to the first feild, the more it will search,
you do not need to add the field to the rest of the query if you don't
want it shown either.
(i.e. if you didn't want the invoice to be shown on the above query,
but still wanted to be able to search it, then you would type select
firstname & " " & lastname & " " & invoicenumber as search, firstname,
lastname from contacts where search like "*" & textfieldonform & "*")

That explains the query side of things.
Now on to how the form works.

What happens on the form is that when you change the field value for
search, it runs VBA code that updates a hidden label on the form.
Because the field's value doesn't actually change until it loses focus,
this is the best method of doing "realtime" searching of a string.

The feild that I mentioned as textfieldonform in the query would relate
to the caption mentioned above.

Regards
Anthony

Hi All!
I know that this will probably be a simple question for all of you experts
but I've been pulling my hair out while trying to get this search to work!!
Going around in circles is driving me nuts!!!
This is what I want to be able to do if you follow this link
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=15211&d=1161158846

But I can't see to make it work with my database I'm not to sure what values
I have to change in the expressions etc.
To make this question short I want to be able to create a search in my Form
which searchs for Surnames, Firstnames and Invoice Numbers to bring up all of
the fields (for instance I also have some fields called: Notes, Location ID
and Contact ID) from that record to the form.
I have tried so many ways trying to make it work! From lists, combo boxes and
form filtering, but if I can get this sample database to work on mine it
would be great! I'm just trying to make it as User friendly as I can for
staff.
I have copied the Query1 in the sample to my Query (Named: SearchRecords)
with the relevent Fields selected for example Surnames, Firstnames and
Invoice Numbers have in the Criteria: Like "*" & [Forms]![Search]![Search2] &
"*"

Thank you for your help in advance! Please tell me if I haven't worded my
question right or if you need more infomation!!

 

[Mrx via AccessMonster.com]

Thank you very much for your help Anthony! I feel that I might be able to
finally get this done with your help!! I do have just a quick question though
(I might add that I haven't used your help yet but I will play around soon!)

with your example search:
if you want to search on firstname and lastname they have done the
following.
select firstname & " " & lastname as search, firstname, lastname from
contacts where search like "*" & textfieldonform & "*"

I type: select firstname & " " & lastname as search, firstname, lastname from
contacts where search like "*" & textfieldonform & "*" on the criteria
section of the query or in a textbox on the form?

I'm getting a error box (In the Query) saying: Check the subquery's syntax
and enclose the subquery in parentheses

I'm going to play around with it anyways, but you have done very well in
explain this!!

I will post again once I've had a fiddle!

Hoo Roo!

 

[Mrx via AccessMonster.com]

Ok with a bit of playing around I have came closer I think... (I hope!!!)

I've now got a Enter Parameter Value box coming up when I try to type in the
search textbox on the form.
With the "Enter Parameter Value" box (Below the Enter Parameter Value heading
has: Forms!Search!Search2) I can type in a Value and it will find some
records when I click ok but it's not quite working like the example. (Typing
in the search textbox to do a search)

I'm thinking that somehow I have to get that "Enter Parameter Value" box
intergrated to the form by using the search textbox form; instead of the
Enter Parameter Box just popping up? Would that then enable Phrase Prediction
to work? I'm just taking a stab in the dark so if anyone can help us out
that'd be great!

 

[Anthos]

I am glad things are getting there for you.
Because I didn't know what your form was called, not the feilds that
you are using, I used a generic example, so firstname lastname etc
didn't exist in the tables, and the table probably didn't exist in your
database.

On your search for you need at least 2 objects
1- An unbound text feild (For the sake of arguement call it
searchentry)
2- A Label also on the form (call it Search2 so that it works with your
example)

Now your form that does the seach, has to be called Search as well.

On the On Change event of the unbound text feild, you need to have the
following code.

Dim strSearchvalue as String
Me.Search2.Caption = Me.SearchEntry.text

Now your Query should work how you want it to.

The phrase prediction is done because of the like * statemet.

Feel free to let me know if I am confusing the issue.

 

[Mrx via AccessMonster.com]

Hi Anthony!
It works, It WORKS! And all I did was change my Form name from 'Search
Records' to 'Search' and it works! Although It's still not 100% like the
sample (It's not doing the pharse prediction such as guessing the word I want
to type in the search textbox) but it's doing enough for what I want so I'm
happy with that! Thank you once again for your help it really is muchly
appreciated! Hope you have a great day!
Hoo Roo!
Luke