Value list dependent on another list (Access 2000)

[gavin]

In Access 200 is it possible to have a field on a form that is hidden until
a value from another field is chosen? I probably haven't said that very
well - maybe an example would make it clearer

I have a form with a field on it called "car_manufacturer". This is a combo
box with a value list which includes "Ford;Volkswagen;Renault;Volvo" etc.
When a user selects a value from this combo box is it possible to make a
previously hidden field appear? Furthermore, is it possible to make that
field a combo box with values which depend on the choice made in the first
combo box? For example, if the user selects "Ford" then the second combo box
would have the values "Focus;Mondeo;Fiesta" etc.

I have a nasty feeling that if this is possible it could well involve some
nasty programming!


Many thanks for any suggestions,




Gavin

 

[Kevin3NF]

In the after update event of the FIRST combo box, use VBA code to set the
record source of the SECOND combo box.

MS has an article on it, but I don't remember where to look...other than
support.microsoft.com

It's pretty simple really.

 

[Douglas J. Steele]

http://support.microsoft.com/?id=289670 or
http://www.mvps.org/access/forms/frm0028.htm

 

[Rolls]

This seems to be two questions.

1) Controls have a "Visible" property which can be toggled programmatically.
If that's what you want, find the appropriate event procedure, and within a
loop that detects your condition change the control's "visible" property. I
prefer to do this as little as possible because I find "blinking forms" that
appear & disappear to be annoying.

2) Yes you can use one or two combo boxes to "Limit a list"; i.e, refine
progressive subsets of data in a table. It's a very common use for combo
boxes. If your tables are normalized what you're doing is applying filters
to the full data based on combobox choices the user selects, then presenting
the subset as a filtered query result. The skills are being able to set up
the combo boxes correctly, using the value after the first combo box is
selected as a parameter passed to the second combo box, and being able to
reset the lower level combo box value to "Null" once the higher level combo
box is changed. It can be done with a few lines of code in the appropriate
event procedures. You might have additional textboxes which display other
data as the comboboxes change. Use a bound textbox with the column property
for each field from cboMyComboBox that you want to display in a separate
control. "Requery" or "Repaint" code may be needed to keep the screen
synched to data when events change the active data.

This is a generic skill applicable to any parent-child relationship. Ex:
tables for states, counties, cities. I want to first select a state via a
combobox then see a list of counties or cities within that state in the
second combobox. If I selected Ohio then Cincinnati, then Texas I would not
want to see Cincinnati as a city in Texas, so there is a cboCity.Requery in
the cboState AfterUpdate event.

Patience and practice perfects these processes.

 

[gavin]

Your example of states and cities is exactly the kind of thing I want to do.
I have looked at the links in Kevin's post but it is all going a bit over my
head. My database is very simple and there are only a few options in each
combo box. I am sure this is easy peasy to anyone with a small knowledge of
VB. Is there anyone out there who would be prepared to hold my hand through
my particular scenario? I would be very grateful.

Best wishes,



Gavin

 

[Rolls]

See the thread begun by Mike Webb 12/12 2:08 pm. I gave fold tab-A into
slot-A instructions.

We have an Entity-Relationship (ER) database model. Use two types of tables
a) Entities contain 1 ea. records w/unique instances of the entity. b)
Relationships contain 1ea. records that describe telationships between
entities.

You need to know what goes in which type of table. Also how to manage
forms/subforms. A form is for managing Entities. sfrm for managing
relationships.

Every entity has a relationship to at least one other relationship.

Once this epiphany strikes you and you "get it" you can handle almost every
potential complication. Data Normalization is the important subject matter.
Forms, behind-forms VBA is the next most important subject matter.

If you can manage tables and add, change, & delete data in the tables,
that's the "basics" and from there you add bells, whistles, and polish.

 

[Rolls]

Every entity has a relationship to at least one other entity. (Sorry it
gets confusing).

 

[gavin]

I have looked at the thread that you have suggested but unfortunately it is
so far over my head it's not even funny :-( That would tend to suggest
that I am trying to run before I can crawl and yet I have a feeling that
what I am trying to do really isn't that difficult.

One last plea - has anyone out there got a small amount of time (and a lot
of patience!!!) to help me with my small database?

Best wishes,



Gavin

 

[Rolls]

Suggest that your data consists of two entities:

1) tblManufacturer
a) ManufacturerID (PK)
b) Manufacturer

2) tblModel
a) ModelID (PK)
b) Model

and one relationship between the entities:

3) tblManufacturer-Model
a) Manufacturer-ModelID (PK)
b) ManufacturerID (FK)
c) ModelID (FK)

You perhaps want

frmManufacturer (the parent) to contain
sfrmModel (the child)

You'll have 1 record per unique Manufacturer in tblManufacturer, 1 record
per unique Model in tblModel
and 1 unique relationship (i.e., Ferrari <- Daytona) in the relationship
table tblManufacturer-Model.

The combobox on frmManufacturer will display all relevant models on the
embedded sfrmModel each time you update cboManufacturer. The subform wizard
can build these forms for you without having to write VBA code! When you
change the Manufacturer combo box all the models will change, too.

If you're thinking in terms of only one table:

Ford <- Escort
Ford <- Explorer

which is a relationship table without the two entity tables,
then you're not using data normalization and shall remain a tormented soul.

 

[Kevin3NF]

Gavin,

I would be willing to take a look at your db, provided you do not hold me to
a time constraint. No promises that I can do anything for you other than
take a look.

Contact me offline: Kevin AT 3NF-inc DOT com

You will get a SPAMArrest email back with instructions.

I will get you an FTP site to upload to. Do not email me your db.

--
Kevin3NF

Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001