Combo Boxes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

D. M. - 02 Feb 2008 16:37 GMT

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.

Reply to this Message

Rick Brandt - 02 Feb 2008 16:49 GMT

> Hello All,
>
[quoted text clipped - 18 lines]
>
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

D. M. - 02 Feb 2008 17:09 GMT

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.

> > Hello All,
> >
[quoted text clipped - 41 lines]
> 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.

Reply to this Message

Rick Brandt - 02 Feb 2008 17:24 GMT

> Rick, Thanks for your quick response. Maybe my initial question
> wasn't clear. I don't want to store information from the stores or
[quoted text clipped - 8 lines]
>
> 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?

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

D. M. - 02 Feb 2008 18:53 GMT

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, Thanks for your quick response. Maybe my initial question
> > wasn't clear. I don't want to store information from the stores or
[quoted text clipped - 17 lines]
> Is that what you are trying? If not why not? What are you trying and what
> results are you getting?

Reply to this Message

Rick Brandt - 02 Feb 2008 19:13 GMT

> I'm sorry, I'm just not understanding. I believe my confusion is
> with the relationships.
[quoted text clipped - 10 lines]
>
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

John W. Vinson - 02 Feb 2008 20:41 GMT

>I'm sorry, I'm just not understanding. I believe my confusion is with the
>relationships.
[quoted text clipped - 6 lines]
>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]

Reply to this Message