Drop-down list in Query

[Kyle]

I have a table with PEOPLE in it, linked to an ENROLLMENT table, linked to a CLASS table. I do a mail merge with some data onto a letter after each class is over

I have a query that runs now that prints people in each class. When I run the query I type the class name in and then it responds with the class list. I'm curious if I would be able to instead of typing in the class name, use a drop down list and have the query read in the class names

Thanks in advance

Kyle

 

[Ken Snell]

Use a form to obtain the desired value and then have the query read the
value from the form.

Replace the parameter in the query with an expression similar to the
following (substitute your real names):

[Forms]![myFormName]![myComboBoxName]

where myFormName is the name of the form and myComboBoxNameis the name of
the combobox that has the desired value.

On your form, put the combo box and a command button. Have the combo box's
Row Source return all the values that you want to select from. Then put code
on the command button's OnClick event to run the query (or to open a report,
if that is what the query will "feed"):

Private Sub CommandButtonName_Click()
DoCmd.OpenQuery "QueryName"
End Sub


or

Private Sub CommandButtonName_Click()
DoCmd.OpenReport "ReportName"
End Sub


--
Ken Snell
<MS ACCESS MVP>

I have a table with PEOPLE in it, linked to an ENROLLMENT table, linked to

a CLASS table. I do a mail merge with some data onto a letter after each
class is over.

I have a query that runs now that prints people in each class. When I run

the query I type the class name in and then it responds with the class list.
I'm curious if I would be able to instead of typing in the class name, use a
drop down list and have the query read in the class names?

 

[Kyle]

Thanks Ken

I will try that, I never thought of doing it that way

Thanks again

Kyle

 

[Kyle]

I tried what you said Ken, I'm having some sort of problem though.

No matter what I select from the list, the query returns nothing. I have a lookup in my ENROLLMENT table, I don't know if this has anything to do with it or not?

Kyle

 

[Ken Snell]

Yep, get rid of the Lookup in the table's field. Remember, what you see in
the Lookup field is *not* what is actually stored in the field! Therefore,
if you're trying to match what you see, it's not what is there!

--
Ken Snell
<MS ACCESS MVP>

I tried what you said Ken, I'm having some sort of problem though.

No matter what I select from the list, the query returns nothing. I have

a lookup in my ENROLLMENT table, I don't know if this has anything to do
with it or not?

 

[John Vinson]

I tried what you said Ken, I'm having some sort of problem though.

No matter what I select from the list, the query returns nothing. I have a lookup in my ENROLLMENT table, I don't know if this has anything to do with it or not?

Kyle

Yes, I suspect it does. Lookup fields CONCEAL the actual contents of
your table. If you're searching for the value you see in the lookup,
you won't find anything because *it is not there*!

See http://www.mvps.org/access/lookupfields.htm for a critique of this
misfeature; and perhaps you could post the SQL view of your query.

 

[Kyle]

Well, thanks for the suggestions guys. I fiddled around with it and I actually got it to work without having to get rid of the lookup

I really didn't want to get rid of the lookup because it's easier for data entry. That way if someone enters a class name, they can never spell it wrong. I thought it would be easier to select the class name from a list. Is there another way to do this

My class table has a class ID field YYYYMMDDSL (S=subject L=location) followed by a ClassName. I made the ClassName unique, but it is not the primary key, the classID field is. On my enrollment table (that connects the many to many relationship between candidates and class) the ClassName is a Lookup where it pulls the ClassID from the class table, but displays the ClassName for data entry

Hope that made sense, if I'm doing something odd, please let me know

Thanks

Kyle

 

[Ken Snell]

Is there another way to do this? Absolutely....rule #1 (well, maybe a bit
lower on the list!) is this: Never use a table for data entry! Never expose
a table to your users! Always use a form to do data entry and to view your
data!

You can put a combo box on the form that will do the same thing as your
"lookup".

--
Ken Snell
<MS ACCESS MVP>

Well, thanks for the suggestions guys. I fiddled around with it and I

actually got it to work without having to get rid of the lookup.

I really didn't want to get rid of the lookup because it's easier for data

entry. That way if someone enters a class name, they can never spell it
wrong. I thought it would be easier to select the class name from a list.
Is there another way to do this?

My class table has a class ID field YYYYMMDDSL (S=subject L=location)

followed by a ClassName. I made the ClassName unique, but it is not the
primary key, the classID field is. On my enrollment table (that connects
the many to many relationship between candidates and class) the ClassName is
a Lookup where it pulls the ClassID from the class table, but displays the
ClassName for data entry.

 

[John Vinson]

Well, thanks for the suggestions guys. I fiddled around with it and I actually got it to work without having to get rid of the lookup.

I really didn't want to get rid of the lookup because it's easier for data entry. That way if someone enters a class name, they can never spell it wrong. I thought it would be easier to select the class name from a list. Is there another way to do this?

Yes, there is: use a Form - WITH A LOOKUP (a Combo Box) - on it to do
the data entry. Table datasheets are NOT designed or appropriate for
that purpose.

Lookups are fine, they are universally used... but they should be used
in their appropriate place, on a Form, not in a table!

My class table has a class ID field YYYYMMDDSL (S=subject L=location) followed by a ClassName. I made the ClassName unique, but it is not the primary key, the classID field is. On my enrollment table (that connects the many to many relationship between candidates and class) the ClassName is a Lookup where it pulls the ClassID from the class table, but displays the ClassName for data entry.

Hope that made sense, if I'm doing something odd, please let me know.

Well... I would certainly not create a single field containing three
disparate types of data. If you're not showing it to your users - and
even if you are! - it would make much more sense to me to use a simple
Autonumber (meaningless numeric) ID. Packing data into an "intelligent
key" like this is rarely either necessary nor beneficial. If you need
to show the concatenation to the users, just store a date, a subject,
and a location in three fields and concatenate them for display
purposes!