how to run a query from a listbox??

[Muzicmn]

how can i design a query to search for a value that is selected from a listbox???

i would like to design the table so when the user clicks on a button they get a choice of data, (from the listbox) once they click on the one they want the query inserts that data into the query and displays the results.

i have seen this in a few tables but was never able to figure out how to do it

thanks alot

Ricky

 

[Tim L]

Hi Ricky,

I would advise that you should change the list box to a combo box, if as you suggest,

once they click on the one they want

the user only needs to select one item.

This can be done easily by right clicking on the listbox control and selecting Change to>Combo Box. However, I think that this solution should work for single selections in list boxes (I don't know about multiple selections, sorry).

Assuming that your list/combo box is called UsersChoice all that you need to do in the query is to put the line

=[formname].[UsersChoice]

in the appropriate criteria field, then, when the query is run the query will look for the value of the UsersChoice box and use that as the criteria.

You can do this using the built-in 'build' function in Access, this will make the process of constructing the reference easier and less likely to be wrong! Make sure that once you have selected the right form and control that you click on paste before clicking done/ok.

Note that you do not have to bind the list/combo box to a table/query you just need to set the Row Source to a suitable table/query etc.

If you want the query to run as soon as the user has made their choice you could put the action under UserChoice On Change event (or perhaps the On Click event of a listbox). Alternatively, if the user needs to set other criteria, use a button to activate the query.

HTH

 

[dcx693]

Tim L, there's no reason the user needs to change the control from a listbox to a combo box if he wants to accommodate one user selection. There is a reason why listboxes can be set to single-select (the default) and multiselect.

In fact, listboxes and combo boxes are very similar, one really big difference between the single-select varieties being that listboxes "stay open" and you can resize them to display more or fewer choices whereas combo boxes "close" and "open" if you click the drop-down arrow. (The other big difference being that you can add new entries to a combo box.)

 

[Tim L]

dcx693,

Thanks for clarifying that.

I had to speak from my area of experience and I have not really used list boxes much, but was fairly certain that the method suggested would work for a combo box, even if it wouldn't for a list box.

I trust that the rest of my suggestion was in order?

Tim

 

[dcx693]

I trust that the rest of my suggestion was in order?

Actually, now that I look at it...

This line here:
=[formname].[UsersChoice]

In a query Criteria line, when referring to a field in a form, you must use "bang" notation (using the "!" symbol) and use the "forms" qualifier like this:
=[forms]![formname]![UsersChoice]
or more simply, if your object names do not include any spaces or special characters:
=forms!formname!UsersChoice

 

[Tim L]

Good job I suggested using the Build function then, eh !

Tim

 

[dcx693]

The expression builder is probably good for some things, but I never use it.

 

[Muzicmn]

works great...

thanks for all the help guys

Ricky