Help a newbie: Populating dropdown lists on a form using data from anothr dropdown list

[DAVID]

Hi, hope someone here can help me. I've been asked to do an absence booking
system for work. It's an office of around 120 people split into 7 teams and
the manager of each team would be updating this system.

I'm having a few problems however. The first main one being I want the form
to record absences when opened to have a dropdown list of the Team Numbers
so the manager can select their team number.

Then I need for another dropdown menu underneath to show the member of staff
in that team selected above.

Once the member of staff is chosen I want that name to be copied into a a
name field on the new record to enter an absence for that person.

Then when they click the save button it clears everything but the team
number. Is this possible? And if so, can anyone help me implement it?

So the form would look something like this

Team Number:
Staff Member:
Team Member / Reason for Absence / Date

Save

 

[Dan Artuso]

Hi,
I'll try to get you going on this.
I'm going to have to make some assumptions about your tables, but if you do not have this structure,
you probably should.
tblEmp; a list of employees and the usual data including the TeamId
tblTeams: A list of Teams with TeamId (this can be your TeamNumber??) being the primary key

I'm going to assume here that an employee can only belong to one group at a time so the TeamId in tblEmp
will identify which group they belong to.

Your first combo will be populated by a query something like this:
Select TeamId From tblTeams

Okay, then your StaffMember combo will use this:
Select <relevant fields> From tblEmp Where TeamId = Me.cboTeamId

In the After Update event of your first combo you would have this line of code:
Me.cboStaffMember.Requery

See if you can ge that far.