Using a list box in a query?

[Gooter]

Greetings,

Here is what I want to do. I would like to create a query where the user can select from a list or combo box the values that are in a particular field in the database. For example: I have a field called "Names" and in that field there are 5 records, Joe, Steve, Mary, Pat, and Lisa. I want to create a query where the user can see a list of all the available names and pick the one they want to search for so they can then see the other fields associated with that person.

So far, I can prompt the user to enter a name to search for; however, the user has to type the name exactly as it appears in the record else the query won't pick it up. Is there a way to use a wildcard? (e.g., J* would display all the names starting with J).

Obviously, I have simplified my example to make it easier to explain what I'm trying to do. In my database, a name can appear numerous times that's why I would like to give the user a list of names they can choose from so they don't have to type the name verbatim.

Thanks.

 

[SgtJarrow]

Sounds to me like I would create a select query with the field you want, set the query to display only unique records, and then link a combobox to this query. Use an AfterUpdate event on this combobox to run the search and display your result. If the combobox AutoCorrect property is set to yes, it will automatically fill in the name as you begin to type it. If the users already have to click a button to run this search, just replace the button with the combobox or have the buttong open a small pop-up window containing just this combo box and then close it when do.

I could send you an example if you need some help on this.

Good Luck! %-) There is no I in team.

Robert L. Johnson III, A+, Network+
robert.l.johnson.iii@citi.com

 

[Gooter]

Thanks for the reply.

I think I'm going to need an example. Would you mind posting one?

FYI, this is how I set up the query. I picked all the fields I want to be displayed. For the searchable field, in this case "Names", I typed this in the criteria section in the design view, "[Enter Name:]". When the user starts this query, a box is displayed where they can enter the name and it will then display the results.

Sad to say, this is the only way I know how to set up a query. I definitely a newbie when it comes to this sort of thing.

Thanks
Marc

 

[SgtJarrow]

Not a problem.....Here is what I would do.....

Create a query based on the table with the names, suing only the name field. In the design of the query, right click on the first blank field and select properties. Look for the UniqueRecords property and make it yes. Then check your query to make sure it shows only unique names.

Next on a form or somewhere where the users can make a selection, create a combo box. Set this box to get a list from the query you just created. On the form where this box is, right click the combo box and select properties. Look for the AfterUpdate property. Set this to event and click the elipse to the right. Use the following in the code window that pops up....

Dim strRunQuery as String
strRunQuery = "name of your query"
DoCmd.RunQuery strRunQuery

This should work for you......If you would like to send me a copy of your database (if it is not too big), I would be happy to insert this for you and return it to you directly. I might be able to see what you are trying to do faster anyway.....My email is below but here it is for sure...... robert.l.johnson.iii@citi.com

There is no I in team.

Robert L. Johnson III, A+, Network+
robert.l.johnson.iii@citi.com

 

[Gresford]

Sorry to butt in. For a similar name search I've used the same sort of parameter query as you did with your [Enter Name:] criteria. But if you use the 'Like' function along with * wildcards, you get loads of flexibility.
If you stick Like "*" & [EnterName:] & "*" in the criteria of your query, you will get the same EnterName prompt, but Access will then retrieve any names that contain the string you enter. If you only want 'names beginning with..', then drop the first "*".
Cheers.

 

[Gooter]

Gresford,
I like your idea about using the LIKE function; however, I can't seem to get it to work and I think I know why. I actually have two tables: Table 1 - contains the names, address, phone numbers, etc. Table 2 - contains what I want to keep track of. I set a primary key in each table, NameID for the Names table and OrderID for the second table. I created a relationship between the two tables linking the NameID to both so that if I changed the address or name of one firm it would automatically update the address/name in the second table for any historical records. The problem I see using the LIKE function in the second table is that I can't search by name since it is keying off of the NameID (I have to search by ID #). Is there any way around this using the LIKE function?

Thanks.

 

[Gresford]

If I understand you correctly..
Create a single query with linking Name and Order tables together. Just take the Name field(s) from the Name table (not the address stuff) and then include all the Order fields you want. You can then stick the Like parameter (as above) into the Name field.
(Are you familiar with creating queries? Use the Query wizard - pick your first table, and select the fields you want from it; then pick the second table, and select the fields you want from that. Take the 'Detail' (not 'Summary' option). When the wizard has created the query, switch to Design View and add your Like parameter to the criteria of the relevant Name field.)