Combo Boxes

[D. M.]

Hello All,

I have been banging my head against the wall trying to figure this one out.
Here's the problem: I have created a maintenance request database. I have 3
tables: Maintenance Requests, Stores (Linked from another database), and
Vendors (also linked).

I am trying to create a form with 2 combo boxes that will populate other
text fields in the form: The main table is tblMaintenanceRequests.

1) tblStores - combo box for store number to populate store name, phone &
fax fields;
2) tblVendors - Combo box for Vendor name to populate Vendor Phone & Fax
fields

All other fields on the form will be from tblMaintenanceRequests.

I cannot get this to work. I am working with Access 2007, but the database
will be used with Access 2003.

Any help would be appreciated.

 

[Rick Brandt]

Hello All,

I have been banging my head against the wall trying to figure this
one out. Here's the problem: I have created a maintenance request
database. I have 3 tables: Maintenance Requests, Stores (Linked
from another database), and Vendors (also linked).

I am trying to create a form with 2 combo boxes that will populate
other text fields in the form: The main table is
tblMaintenanceRequests.

1) tblStores - combo box for store number to populate store name,
phone & fax fields;
2) tblVendors - Combo box for Vendor name to populate Vendor Phone &
Fax fields

All other fields on the form will be from tblMaintenanceRequests.

I cannot get this to work. I am working with Access 2007, but the
database will be used with Access 2003.

Any help would be appreciated.

You should NOT be storing anything about stores in your request table except for
the store number.

You should NOT be storing anything about vendors in your request table except
for the vendor name.

This is a basic principle of relational databases. You store data attributes of
an entity only in the table that is built for describing that entity. Which
store and which vendor are attributes of a request. Any other data about a
store is already stored in the stores table and should not be redundantly stored
in requests. The same is true for vendors.

Now, if on your form you just want to SEE additional data about the store and
vendor, that is a perfectly legitimate thing to do. Just use unbound TextBoxes
having ControlSource properties similar to...

=StoreNumber.Column(1)
=StoreNumber.Column(2)
=VendorName.Column(1)

That will cause the additional columns from the ComboBoxes to be shown on your
form, but that data is not stored as part of the request record.

 

[D. M.]

Rick, Thanks for your quick response. Maybe my initial question wasn't
clear. I don't want to store information from the stores or vendors tables
(other than StoreID and VendorID). I want to create a work request for a
store (by choosing the store number from a combo box) and select a vendor (by
Vendor Name combo box). Following is the "structure" of the tables:

tblStores: StoreID (PK)
tblVendors: VendorID(PK); MaintReqID(FK)
tblMaintenance: MaintReqID (PK); VendorID (FK); StoreID (FK).

I hope this clears up my question.

Thanks in advance.

 

[Rick Brandt]

Rick, Thanks for your quick response. Maybe my initial question
wasn't clear. I don't want to store information from the stores or
vendors tables (other than StoreID and VendorID). I want to create a
work request for a store (by choosing the store number from a combo
box) and select a vendor (by Vendor Name combo box). Following is
the "structure" of the tables:

tblStores: StoreID (PK)
tblVendors: VendorID(PK); MaintReqID(FK)
tblMaintenance: MaintReqID (PK); VendorID (FK); StoreID (FK).

I hope this clears up my question.

Well then what I suggested (ControlSources that refer to the additional columns
of the ComboBoxes) is what should work for your specific request...

I am trying to create a form with 2 combo boxes that will populate
other text fields in the form:

Is that what you are trying? If not why not? What are you trying and what
results are you getting?

 

[D. M.]

I'm sorry, I'm just not understanding. I believe my confusion is with the
relationships.

The primary table is tblMaintenance. The PK's and FK's are as noted below.

I want to create a form from tblMaintenance with a combo box to choose store
number and Store city, and another combo box for Vendor Name, phone, etc.

I don't know how to create the combo boxes since the main table is
Maintenance.

Thanks again... sorry for the confusion.

 

[Rick Brandt]

I'm sorry, I'm just not understanding. I believe my confusion is
with the relationships.

The primary table is tblMaintenance. The PK's and FK's are as noted
below.

I want to create a form from tblMaintenance with a combo box to
choose store number and Store city, and another combo box for Vendor
Name, phone, etc.

I don't know how to create the combo boxes since the main table is
Maintenance.

Thanks again... sorry for the confusion.

You add two ComboBoxes bound to VendorID and StoreID respectively and then you
give them RowSources that use tblVendors and tblStores to produce the list of ID
choices. You can then add additional columns from those tables to the ComboBox
RowSources (hidden or shown upon your preference) and those additional columns
can be referenced using the .Column(n) syntax that I gave you earlier.

The ComboBox wizard will simplify the creation of the multi-column aspects of
this for you.

 

[John W. Vinson]

I'm sorry, I'm just not understanding. I believe my confusion is with the
relationships.

The primary table is tblMaintenance. The PK's and FK's are as noted below.

I want to create a form from tblMaintenance with a combo box to choose store
number and Store city, and another combo box for Vendor Name, phone, etc.

I don't know how to create the combo boxes since the main table is
Maintenance.

That's irrelevant.

A Combo Box typically *takes* one field's data from one table - its Rowsource
- and *puts* that value into a different table's field, its Control Source.

If you're assuming that a combo box on the Maintenance form must pull its data
from the Maintenance table (as well as putting data into the Maintenance
table), revise your assumption! The combo's rowsource can be based on any
table in the database, or on a table linked from another database.

John W. Vinson [MVP]