Can I create a drop down inside a table for the ID of another tabl

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a database with a products table and a category table. they're
related with the categoryID field. Problem is, there's nothing in my
categories table as I'm having to create this myself.

When I open up my products table to begin filling in the categoryID field
it's pretty hard to remember what ID is what category. Is there a way I can
make this a drop down menu with which I can just select the correct category
and it will populate the related field accordingly?

Any info would be greatly appreciated. Thanks!
 
I think I see what your trying to do.

Open your products table in design view and select the field that will hold
the category ID. Next, click the "Lookup" table in the field properties. Now
select 'Combo Box' from the 'Display Control' options. Next selct the Row
Source field followed by the "..." button that appears. In the query
designer, add your Categories table the include the CategoryID first followed
by your Category Name. (You might want to sort your output by Category Name).
Now close the designer select the colum count and set the value in it to 2.
Next, enter "0;8" into then Column widths field. Save the table and open in
datasheet view.

You should now be able to select the category from a list by name in the
category ID colum of the products table.

good lock
 
I've got a database with a products table and a category table. they're
related with the categoryID field. Problem is, there's nothing in my
categories table as I'm having to create this myself.

When I open up my products table to begin filling in the categoryID field
it's pretty hard to remember what ID is what category. Is there a way I can
make this a drop down menu with which I can just select the correct category
and it will populate the related field accordingly?

Any info would be greatly appreciated. Thanks!
Click to expand...

Mr. Smith suggested a Lookup Field, which will solve your immediate
problem... BUT. Lookup fields get you from Point A to Point B, but can
in fact make it much more difficult and confusing to get any further!
See http://www.mvps.org/access/lookupfields.htm for a critique.

I would suggest NEVER using table datasheets for any purpose other
than debugging. Instead, create Forms as tools to interface with the
data in your tables. You can easily (with or without the Lookup Field)
put a *combo box* - a drop-down list of categories which will store
the CategoryID while it displays the category name - into your table.
Forms are a MUCH more flexible and effective way to enter data than
entering directly into tables; they're well worth the additional work
setting them up!

John W. Vinson[MVP]
 
How do I create the drop down for the categoryID field in my form? I created
a basic form showing all the fields I need from my Products table using a
wizard but it just wants me to type in the data into that field.
 
It seems as though I have to create the Lookup as Mr. Smith explained to make
it work in a form too. Am I missing something here?

When I first tried creating a form with the wizard it just put a text box
for the categoryID field and inserted a 1, or 2 or whatever the ID is. I
went into design mode and switched this to a combo box...but it still just
listed the numbers. I then deleted this form I had created.

I followed Mr. Smith's steps and successfully created a Lookup for the
categoryID field in the Products table. Now when I create a form with the
wizard it automatically creates a combo box for the categoryID field and does
indeed show me the names instead of the IDs.

I'm happy with this at the moment...but I'm confused as to what you're
saying about the problems with Lookups. How else do you do this?

Thanks!
 
It seems as though I have to create the Lookup as Mr. Smith explained to make
it work in a form too. Am I missing something here?
Click to expand...

Yes, you are.

You can create a Combo Box on a form using the Toolbox wizard.
When I first tried creating a form with the wizard it just put a text box
for the categoryID field and inserted a 1, or 2 or whatever the ID is. I
went into design mode and switched this to a combo box...but it still just
listed the numbers. I then deleted this form I had created.
Click to expand...

If you were to delete the combo box, and create a new one using the
toolbox wizard, it would walk you through the steps of creating a
combo box which stores the ID while displaying the text. You can also
do this manually by working with the RowSource property of the combo
(the query or table whence the combo gets its data), the Bound Column
(which field gets stored), the Control Source (where it gets stored),
and the ColumnWidths property (you can set the width of a column to
zero to hide it from view.)
I followed Mr. Smith's steps and successfully created a Lookup for the
categoryID field in the Products table. Now when I create a form with the
wizard it automatically creates a combo box for the categoryID field and does
indeed show me the names instead of the IDs.

I'm happy with this at the moment...but I'm confused as to what you're
saying about the problems with Lookups. How else do you do this?
Click to expand...

Ok, I'll concede that using the lookup field makes adding a combo box
to the form a few mouseclicks easier, a one-time advantage.

The price for these few mouseclicks - in my experience helping folks
over the past several years on these newsgroups - is that the Lookup
Field *IN THE TABLE* causes many, many problems both technical and
(worse) conceptual.


John W. Vinson[MVP]
 
Back
Top