can link one drop-down list to another?

[kmnotes04]

Is it possible to link one drop-down box to another? For example, if a
name is chosen from a drop-down list, can another drop-down list then
automatically display the person's office as a result of the choice
made in the first drop-down list? If so, how can that be done? I am
working with 'Teach Yourself Microsoft Access' and I didn't see it
discussed in that book (if it's even possible to do). Or would I just
have to create linked tables in the database layout instead?

Also, is there a way to create a 'last updated' field automatically
populated by the date at which a record was last updated?

Finally, how do I specify in which database field a value for a
checkbox should be stored?

Thanks in advance for any help on these issues!

[fredg]

On 19 May 2005 13:12:32 -0700, km*******@hotmail.com wrote:

Is it possible to link one drop-down box to another? For example, if a
name is chosen from a drop-down list, can another drop-down list then
automatically display the person's office as a result of the choice
made in the first drop-down list? If so, how can that be done? I am
working with 'Teach Yourself Microsoft Access' and I didn't see it
discussed in that book (if it's even possible to do). Or would I just
have to create linked tables in the database layout instead?

Also, is there a way to create a 'last updated' field automatically
populated by the date at which a record was last updated?

Finally, how do I specify in which database field a value for a
checkbox should be stored?

Thanks in advance for any help on these issues!

1) The person can have more than 1 office?
Leave the Rowsource of the second combo box blank.
Code the first Combo Box AfterUpdate event:
ComboTwo.Rowsource = "Select [FieldName] From TableName Where
[NameField] = '" & Me!ComboName "';"

Each person has only one office?
Instead of the second combo box, use an unbound text control.
Include the office as a 2nd column in the first combo box.
then code the combo AfterUpdate event:
TextControlName = Me!ComboName.Column(1)
where the column number is the number of the column that displays the
office. The Column number is zero based, so Column(1) is the 2nd
column.

2) Add a DateTime datatype field to your table
Name it [LastUpdated].

If the table is the form's recordsource, add that field to the form
from the Field List tool button.
If a query is the form's record source, add the field to the query
first, then add it to the form from the form's Field List tool button.

Code the Form's BeforeUpdate event:
If Me.Dirty = True then
Me![LastUpdated] = Now
End If

3) Add a Check box field to the table.
If the table is the form's recordsource, add the Check Box to the Form
from the form's Field List tool button.
If a query is the form's record source, add the field to the query
first, then add it to the form from the form's Field List tool button.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Tim Marshall]

km*******@hotmail.com wrote:

Is it possible to link one drop-down box to another? For example, if a
name is chosen from a drop-down list, can another drop-down list then
automatically display the person's office as a result of the choice
made in the first drop-down list? If so, how can that be done? I am
working with 'Teach Yourself Microsoft Access' and I didn't see it
discussed in that book (if it's even possible to do). Or would I just
have to create linked tables in the database layout instead?

A simple example.

Given two tables:

TBL_PERSONNEL
PER_PK PER_FIRST_NAME PER_LAST_NAME
1 Joe Blow
2 Sally Silly
3 DP Gumby

TBL_OFFICE_ASSIGNMENT
OFF_PK OFF_PER_FK OFF_NUMBER
1 3 A-1000
2 3 A-2201
3 3 S-3456
4 1 A-2013
5 2 J-2341
6 2 B-1204A

In reality, you'd probably have a many to many relationship here with
three tables with office details listed once per office with one office
per record in a table of offices. The TBL_OFFFICE_ASSIGNMENT would have
a PK reference instead of the office number.

Anyway, make your first combo box based on the tbl_personnel table. Use
the builder to construct a row source (combo box properties window,
beside the "row source" property) that brings in the PER_PK and maybe
the combined name, PER_LAST_NAME & ", " & PER_FIRST_NAME. Do this by
constructing query when in the builder (it looks like a query design
window anyway) and bring down PER_PK as the first field and then make a
calculated field like this in the field cell:

Worker Name: PER_LAST_NAME & ", " & PER_FIRST_NAME

Close the query builder and say yes you want to make this the row source.

In the combo box properties window, go to format and indicate:

Column Count: 2 (this will be for the per_pk and worker name field)

Column Widths: 0; 2"

The 0 makes the first column (PER_PK) hidden - the user only sees the
work name.

So, you have PER_PK (which is hidden) and then the name.

Go to the "data" tab and find the "Bound column" property. Make sure it
is 1 (by default). It is the bound column that represents the value of
this combo box when a selection is made. In this case it will be
whatever the value of PER_PK is. For instance, if you choose Silly,
Sally, the value of the combo box will be 2.

Go to the name property of the properties window (click on the "Other"
tab) and give this combo box a name, say, cboName.

Now you're done the first combo box.

Next, create a second combo box. Use the builder to create a row source
(as described above). Use the table TBL_OFFICE_ASSIGNMENT.

Bring down OFF_NUMBER into the query design. Next bring down
OFF_PER_FK. this latter field is the "look up number" for the personnel
occupying the office.

Now, in the "show" check box beneath the OFF_PER_FK field, turn the
check mark off - you don't want anyone seeing this field, you just want
to show the office numbers the person selected in the first combo box.

Next, go to the criteria field immediately below the OFF_PER_FK field
and type in:

[Forms]![FrmYourFormName]![cboName]

This tells the row source of this second combo box to only show values
for the name selected in the combo box, cboName.

Name the second combo box cboOffices

You might think you're done, but there is one thing. Each time you make
a selection in the name combo box, cboName, you have to tell Access to
reevaluate what is showing in the second combo box. Some people do this
in the after-Update event of the first combo box, I prefer to do it in
the On Got Focus event of the second combo box.

In the properties window of the second combo box, cboOffices, click on
the "Event" tab. Find the "On Got Focus" event and click the builder
and choose code builder.

In the VBA screen, under "Private Sub cboOffices_GotFocus()" (Access
writes this for you), type in:

me.cboOffices.requery

This will tell the cboOffices combo box to reevaluate itself each time
you click on it.

Now you're done.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me