List box row source queries (1 Viewer)

rikklaney1 · Mar 27, 2015

If I have a form with multiple list boxes, lets say named 10, 20 and 30.
and multiple text fields called 15,25,and 35. Now I want to get a value in the list box based on the text box value so I make row source queries that looks like this.

SELECT

.field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![15]));

SELECT

.field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![25]));

SELECT

.field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![35]));

So now my question... Is it possible to make one query that picks up the 15,25 or 35 depending on wether it is being queried from 10, 20, or 35. The difference in name is always the same except for the first number so could it be made to reference where it is asked from?

Minty · Mar 27, 2015

This may sound daft, but what exactly are you trying to look up in from what, in plain English.
A list box called 10, 20 or 30 might sound like a good idea, but from the outside is very difficult to picture what you are trying to achieve.

Is suspect there is a way to achieve what you want, if we can grasp your needs?

Mile-O · Mar 27, 2015

rikklaney1 said:
multiple list boxes, lets say named 10, 20 and 30...multiple text fields called 15,25,and 35.

I actually find it hard to follow your example when you don't just use your real world examples.

rikklaney1 · Mar 27, 2015

It's not really a "real world" thing. Just a curiousity of whether it would be necessary to write a query for every instance like that or if a single query could be made that could be used in every instance ang get the correct result. If the query was used as the row source for list box 10 it would get the variable from text box 15. If it was used for list box 20 it would get the vaiable from text box 25, if it was used for list box 30 it would get the variable from text box 35.

Mile-O · Mar 27, 2015

It would be. But when you obfuscate your problem behind unhelpful object names, it's hard to grasp a) what you're doing overall; and b) if the reason you have to do it is down to a fundamental design flaw that you perhaps don't realise you've done. (i.e. Item1, Item2, Item3, etc.)

rikklaney1 · Mar 27, 2015

the row source for LISTBOX10 would be SELECT

.field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![textbox15]));

the row source for LISTBOX20 would be SELECT

.field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![textbox25]));

the row source for LISTBOX30 would be SELECT

.field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![textbox35]));

So the question is can you make ONE query that when placed in listbox10, listbox20 or list box 30 would look for textbox X 5 where X is the first digit in listbox name.

CJ_London · Mar 27, 2015

if LISTBOX10 and textbox15 are both controls on the same form then you can make the rowsource for LISTBOX10 to be

Code:

SELECT [table].field1,[table1].field2 FROM [table1] WHERE [table1].field2=[Textbox15]

(note this is not code to put in a query to then be used on the rowsource)

if the value of textbox15 changes, you need to requery LISTBOX10 to refresh the list

List box row source queries (1 Viewer)

rikklaney1

Registered User.

Minty

AWF VIP

Mile-O

Back once again...

rikklaney1

Registered User.

Mile-O

Back once again...

rikklaney1

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread