Combo box in table definition

[RSunday]

I have a table where the values in a field can be picked from a list - but
this list depends on the value in another field in the same table.

Example: I have a field called "kids". I can select a kid-value from a list
of kids - but I only want to select among the kids in the "family" - i.e. I
have a field specifying the family. This selection should limit my list of
kids to only the reæevant ones.

Can I set up a combo box where this relation is implemented?

Alternatively I create a form with this logic - but at this stage it would
be easier to do without.

 

[Jeff Boyce]

Lookup FIELDS, bad ... lookup TABLES, good (to paraphrase a colleague...)

It sounds like you are working directly in the table(s). This is almost a
necessity in Excel, but Access is a relational database. Access tables
store data, Access forms (and reports) display it.

Do the "lookup" work in your forms, using a combo box THERE.

The major problem most folks encounter with trying to use lookup fields in
tables is that what is stored does not match what is displayed ... this
causes considerable confusion.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[RSunday]

You are right about the confusion when one value is shown an another is
actually displayed. But right now I am just prototyping a data structure -
and once I have it right I'll make all the forms for data entry and display
etc. - but right now I just want something that works.

But I found out that I can just add data in the table views - and use the
relations defined between the tables - i.e. open a child table by clicking
the "+" to expand the hiearchy. When I add records in a child-table, the
relations to the parent is automatically made.

Rsunday

 

[Jeff Boyce]

I'll try once more ... do your work with the data in forms, not in tables.
The subdatasheet ("+") feature, like the lookup field, encourages 'sheet
thinking, and will only make it harder to make the transition over to Access
as a not-spreadsheet tool.

I'm not saying not to spend time in tables, but make it "quality time" <g>!
Use your table time to define structure.

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have a table where the values in a field can be picked from a list - but
this list depends on the value in another field in the same table.

Example: I have a field called "kids". I can select a kid-value from a list
of kids - but I only want to select among the kids in the "family" - i.e. I
have a field specifying the family. This selection should limit my list of
kids to only the reæevant ones.

Can I set up a combo box where this relation is implemented?

Alternatively I create a form with this logic - but at this stage it would
be easier to do without.

I'm with Jeff - DON'T use table datasheets for data interaction.

For one thing, it's very easy to have a dependent combo box on a Form; I do
not believe that it is even *possible* in a table. So if you want this
functionality you must use a Form (which you should be doing anyway!)

How to set this up on a form depends on your table structure. You can set the
RowSource of a combo box to a Query selecting only records from the desired
family, by using

=Forms!NameOfForm!NameOfControl

as a criterion where NameOfControl is the name of a control on the form
containing the FamilyID.

John W. Vinson [MVP]