select distinctrow

[Guest]

I am trying to make a form, where I have 4 lookup boxes. I also have another
table that it is looking at, I wish to make each of these four look at only a
specific set of items in the table. They are all in the same column but are
preceded by letters a b c etc.. that I can differentiate. I have tried
select distinctrow but I can only make one of them look where I want because
it makes a sql query and every time I go to make another it overwrites the
first. Is there some way I can make these queries take a name that it does
not try to calculate? or what should I do?

 

[John Vinson]

I am trying to make a form, where I have 4 lookup boxes. I also have another
table that it is looking at, I wish to make each of these four look at only a
specific set of items in the table. They are all in the same column but are
preceded by letters a b c etc.. that I can differentiate. I have tried
select distinctrow but I can only make one of them look where I want because
it makes a sql query and every time I go to make another it overwrites the
first. Is there some way I can make these queries take a name that it does
not try to calculate? or what should I do?

I'm sorry, I cannot figure out WHAT you're doing.

You should be able to set each combo's RowSource to a (static) query.
I have no idea what you mean by "preceded by the letters a b c.."
What's preceded? What's in the table (example perhaps?) and what do
you want the combo to return (again, an example)? What do you mean by
"it makes a sql query"?

John W. Vinson[MVP]

 

[Guest]

Sorry I was having trouble posting. I am trying to limit to list the lookups
in certain fields. I have equipment preceded by an A, B,C,D I want the
first lookup in the field to only look at the list of A equipment and the
second field to only lookup the B equipment. I did get around it but not in
a nice way. I was trying to set up for separate distinctrow queries, but
everytime I went from the first field to the second it just changed my
original to the B instead of making two queries. Hope this makes sense.

 

[John Vinson]

Sorry I was having trouble posting. I am trying to limit to list the lookups
in certain fields. I have equipment preceded by an A, B,C,D I want the
first lookup in the field to only look at the list of A equipment and the
second field to only lookup the B equipment. I did get around it but not in
a nice way. I was trying to set up for separate distinctrow queries, but
everytime I went from the first field to the second it just changed my
original to the B instead of making two queries. Hope this makes sense.

I'm sorry, but no, it doesn't.

If the first combo box were based on a query, qryA, with a criterion

LIKE "A*"

on this equipment field; and the second combo box based on another
query, qryB, with a criterion

LIKE "B*"

then "going from the first field to the second" won't change anything.

What is the Recordsource of the form? What are these four combo boxes
bound to, if anything? The same field, or four different fields? What
are you in fact using as the Row Source of the combos?

John W. Vinson[MVP]

 

[Guest]

Sorry I will try again. They are all looking at the same field, in another
table. Its a combo box pointing to the column with the A,B,C,D equipment in
it. Each field in the form is separate but looking at the same field in
another table. In the first field I go select distinctrow and make the
criteria A* it then saves the query as a distinctrow query. I go to the next
field in the form and go select distinctrow and set the criteria, in what
looks like a new query, as B*, and save etc... When I have finished and go
back to the first field the criteria now says D* and looking in queries there
is only one select distinctrow query saved. I am using Access 2003. It will
does not seem to want to save more than one select distinctrow query. I want
to be able to save a select distinctrow query for each of the fields in the
form. I am basically limiting the data entry on each field of the form to a
specific set of items. Field one says pick Equipment and lists all the A*
equipment, field two says Pick Equipment and should list all the B* equipment
but does not. Is this clearer? I really don't know how else to explain.
Except there is a table with one column with all the A,B,C and D equipment
that is not related to the form other than for lookup purposes. Thanks

 

[John Vinson]

Sorry I will try again. They are all looking at the same field, in another
table. Its a combo box pointing to the column with the A,B,C,D equipment in
it. Each field in the form is separate but looking at the same field in
another table. In the first field I go select distinctrow and make the
criteria A* it then saves the query as a distinctrow query. I go to the next
field in the form and go select distinctrow and set the criteria, in what
looks like a new query, as B*, and save etc... When I have finished and go
back to the first field the criteria now says D* and looking in queries there
is only one select distinctrow query saved. I am using Access 2003. It will
does not seem to want to save more than one select distinctrow query. I want
to be able to save a select distinctrow query for each of the fields in the
form. I am basically limiting the data entry on each field of the form to a
specific set of items. Field one says pick Equipment and lists all the A*
equipment, field two says Pick Equipment and should list all the B* equipment
but does not. Is this clearer? I really don't know how else to explain.
Except there is a table with one column with all the A,B,C and D equipment
that is not related to the form other than for lookup purposes. Thanks

Again:

What is the Recordsource of the form? What are these four combo boxes
bound to, if anything? The same field, or four different fields? What
are you in fact using as the Row Source of the combos?

Please open the Form in design view; view its Properties; and post the
Recordsource. If it's a query post the SQL view of the query.

Open one or two of the combo boxes in design view. View its Rowsource.
If it is a query, again, please post the SQL.

That will help me understand better than your user-oriented narrative
explanation.

I'm GUESSING that you're using the same query for all four combo
boxes. Obviously, if you have only one query and change it, all four
combo boxes will reflect the change. You must - no option - use four
different queries for the four different controls.

John W. Vinson[MVP]