Small example of what I'm trying to do. Let's say I just have a table with 2 fields, ShopName & OldName. What I'm trying to get, is when a user enters a name into the txtShopName control on a form, it will search both fields for that name. We have body shops that sometimes change names, but the old name still shows up on estimates for a few weeks until everything gets completely changed over. Is there anyway to get 1 control to search in 2 fields? I've tried just adding the OldName field into my query, but seeing as the name isn't the same in both fields, I get nothing returned. I've tried to send what's in the control to a hidden text box, but have something wrong with the code because it doesn't go over until I click the button to run the query, and now I'm thinking even if I got that to work, I'd still have the same issue because the name isn't in both fields. HELP PLEASE!!!!

As always, thanks ion advance....

Hi Shawn


you can use a UNION query in a combo to find records

SELECT ShopID, ShopName as ShName FROM Shops WHERE ShopName Is Not Null
UNION
SELECT ShopID, OldName as ShName FROM Shops WHERE OldName Is Not Null
ORDER BY ShName

WHERE ShopID is the autonumber ID field for the Shop

here are some general instructions for creating one or more combos to search for records:

Make one or more unbound combos on your form. Let the first column be invisible and be the primary key ID of the recordsource of your form and then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:



where
- SomeID is the Name of the primary key field, which is in the RecordSource of the form -- assuming your primary key is a Long Integer data type (autonumbers are long integers)

Remember that the Rowsource for a combo can come from anywhere -- it can pull from multpile tables or only use one ... just make sure that the first column is the primary key ID of the table you want to search and that is the same table the form is based on.

My head hurts just from looking at that. If no one else can offer an easier idea, looks like the way to go though. Thanks for the help...

Hi Shawn,

The FindRecord code is worth taking time to understand --- you will be able to use it all over the place, it is a generic function. All you have to do to customize it is change ONE thing ... SomeID needs to be the name of your autonumber ID field and that field must be in the RecordSource for the form.

any statement beginning with ' is a comment! Comments make the code easier to understand

The Union query is actually not so bad -- but when you have the same information in 2 places, it gives a nice way to get just one list.

here is something you can read that will help you understand the SQL a bit better if that is a problem:

Access Basics
http://www.utteraccess.com/forums/showflat...;Number=1220772
30-page Word document on Access Basics (for Programming) -- it doesn't cover VBA, but prepares you for it because it covers essentials in Access

I do not think you are going to find an easier solution ... so it would be best to start trying to understand this one. We are here to help you with any questions you have