Full Version: Populating Combo Box List
UtterAccess Forums > Microsoft® Access > Access Forms
lorenambrose
This has no doubt been asked, but the search comes up empty.
I have a DB with numerous tables and forms. Nonconforming product and Supplier control. My issue is that there is a field in every table for the supplier name. One of the tables is the "APPROVED SUPPLIER LIST" and for each form that has a control to enter the Supplier, I would like to get the drop down list info from the
Approved Supplier List table and the Supplier's Name column. This way when a supplier is added, ALL the combo boxes will be up to date.
ALL tables are unrelated.
I do NOT want to create a record in every table but just the table bound to the form I am currently in.
Please Help
lorenambrose
This is what I have:
quot;Approved Supplier List" table
table has a column for "Suppliers Name"
I have a "PNCR" table with a Column for "Supplier" there is a form with a ComboBox control called "txtSupplier" for updating this table
There is a "Received" table with a column for "Received Supplier" there is a form with a ComboBox control called "Received Supplier" for updating this table
I need the GREEN ComboBox controls the get their value list/list contents from the BLUE field above, and populate their own respectively bound tables and forms only.
Alan_G
Hi
Not 100% I understand your description of your tables correctly, but generally speaking your set up would be along the lines of one (lookup) table for the approved suppliers -
tblSuppliers
SupplierID-->Autonumber and primary key
SupplierName-->Text (stores the name of the supplier)
NotCurrent-->Yes/No (if true then the supplier is no longer current and can be excluded from queries/rowsources etc)
That's the only table you'd need that stores a suppliers name. In any other table you need to store info. about a supplier, you just store the SupplierID as a foriegn key (numeric, Long datatype)
HAs and when you need to add suppliers, you can do it with a dedicated form bound to tblSuppliers, or via the Not In List event procedure of any forms that have a combobox for selecting a supplier and storing the ID to a different table
Hope that makes sense for you
lorenambrose
The following table is where I need to get the data for all comboBox lists:
quot;Approved Supplier List" table
table has a column for "Suppliers Name"
When I add a supplier to this table, I want to be able to use all the suppliers in the table as a list fo the other comboBoxes in the database. This way only approved suppliers can be selected and all data will be consistantly entered.
lorenambrose
That does make sense, but unfortunately I do not know how to do any of that execpt build the table. Can you help.
Alan_G
Hi
From your reply, it looks like you're doing it right <
To get the info from the suppliers table in any comboboxes (or listboxes) you just need to set the rowsource (using the names I used in my reply above) to
SELECT SupplierID, SupplierName FROM tblSuppliers WHERE NotCurrent = False ORDER BY SupplierName
Then set the following properties of the combo
Column Count - 2
Bound Column - 1
Column Widths - 0,3 (second figure whatever suits. Setting the first column width to 0 will hide the column)
Now on any forms you need the supplier, bind the combo to the appropriate table field (numeric, Long datatype) which will store the SupplierID, not the supplier name
lorenambrose
I am a little confused about the RED items, especially the last one. Am I adding a hidden textbox to the forms that I need to select a supplier? Very confused.
Alan_G
Hi
ope, you don't need hidden textboxes or hidden anything - well, except for the first column of the combo <
Select the combo on your form (in design view), then look in the properties window in the Format tab where you'll find and can set the Column Count and Column Widths properties, then look in the Data tab where you'll see the Bound Column property
lorenambrose
I have it working somewhat, but now I find that when I run reports and such the "Supplier's Name" does not apear. Instead the ID of the supplier is displayed. How do I fix this?
Alan_G
Hi
here's several ways of doing that, depending on where/how you need the suppliers name. On a report for example, typically the record source would be a query so you just add the suppliers table to the query then drag the SupplierID field (from the suppliers table) onto the field in the other table where you're storing the ID foreign key. Access will draw a line between the fields and you can add the suppliers name to the query grid and you have it available for your report.
If it's on a form, you can use a combo/listbox and hide the ID column by setting the column width to 0 (as per above), or you could use the DLookup() function. You can use a query as form's record source as well, but it may or not be updateable.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.