Using cascading Combo Boxes to filter record

[Sgt Bilkp]

Another picky question that has me flummoxed. I can get my head around cascading combo boxes, but this one has got me.

In the header of my form frmStaff i would like to place two combo boxes to filter the records shown. Records for this form come from a query Q_StaffNOTExcluded

The first combo box needs to select records for a certain location. This data is the Location field, again through the query. The second combo box lists the staff names StaffName.

I would like to be able to select a location and the records be filtered to that location. I would then like to be able to select a name from the second combo box (which only shows staff from that location). If it matters, the PK for each of these tables (where staff details are stored) and Locations are StaffID and LocationID and the tables are tblStaff and tblLocation. Both have correctly linked relationships.

 

[wazz]

I would like to be able to select a location and the records be filtered to that location.

since you're on frmStaff i'm assuming you want staff for a certain location.

I would then like to be able to select a name from the second combo box (which only shows staff from that location).

i'm again assuming you've just filtered your form to get all the staff for a location. what's the purpose of the second combobox?

we've got the two tables and the query. how are the two tables related and what tables and fields are in the query?

 

[ShaneMan]

Another picky question that has me flummoxed. I can get my head around cascading combo boxes, but this one has got me.

In the header of my form frmStaff i would like to place two combo boxes to filter the records shown. Records for this form come from a query Q_StaffNOTExcluded

The first combo box needs to select records for a certain location. This data is the Location field, again through the query. The second combo box lists the staff names StaffName.

I would like to be able to select a location and the records be filtered to that location. I would then like to be able to select a name from the second combo box (which only shows staff from that location). If it matters, the PK for each of these tables (where staff details are stored) and Locations are StaffID and LocationID and the tables are tblStaff and tblLocation. Both have correctly linked relationships.

Hey Sgt,

You don't say what you are filtering. A subform? A ListBox? The form itself?
I can tell you what has worked for me in similar situations. You can build an SQL statement and use the WHERE clause part of the SQL to filter your records. If this isn't enough to get you rolling, then you could post back with a little more information on how you have your form set up and where your filter records are being displayed

HTH,
Shane

 

[wazz]

hmm, i think maybe i see. you can't get the data for the second combo until something is selected from the first combo. you'll have to get the criteria for the query of the second combo from the first combo.

query for 2ndCombo (add these fields from tblStaff to the query grid):
StaffID, Name, LocationID (criteria: Forms!frmStaff!cbo1)

cbo1_afterupdate (make sure locationid is in column 1)
me.cbo2.requery

 

[Sgt Bilkp]

Sorry, every time i try to explain, i think i post enough information to cover it.

Wazz - Correct, i want staff from a certain location to be displayed. The second combo box is to further filter the form frmStaff to only show that one person. This form is for people who don't know how to scroll through records using the record selector buttons.

Bit more back ground.

tblStaff contains a list of staff (obviously). StaffName, Email, StaffID (PK), StaffGrade, StaffExclude etc. Also contains a PFK of LocationID. Now StaffExclude is a Y/N field which i will come onto later.

tblLocation contains a simple list of locations. Location ID (PK) and Location.These are linked together through reletionships. One member of staff can only have one location, whereas a location can have many members of staff.

Now these two tables are linked together throgh a query, Q_StaffNOTExclude. This has a criteria under StaffExclude to only show False result, ie: those staff with the box unticked. (Those with the box ticked do not work for us anymore, but the records are still valid for accountancy purposes.

frmStaff uses the query Q_StaffNOTExclude to show "live" staff only, from all locations (6 location options).

Does that help in any way?

 

[ShaneMan]

Sorry, every time i try to explain, i think i post enough information to cover it.

Wazz - Correct, i want staff from a certain location to be displayed. The second combo box is to further filter the form frmStaff to only show that one person. This form is for people who don't know how to scroll through records using the record selector buttons.

Bit more back ground.

tblStaff contains a list of staff (obviously). StaffName, Email, StaffID (PK), StaffGrade, StaffExclude etc. Also contains a PFK of LocationID. Now StaffExclude is a Y/N field which i will come onto later.

tblLocation contains a simple list of locations. Location ID (PK) and Location.These are linked together through reletionships. One member of staff can only have one location, whereas a location can have many members of staff.

Now these two tables are linked together throgh a query, Q_StaffNOTExclude. This has a criteria under StaffExclude to only show False result, ie: those staff with the box unticked. (Those with the box ticked do not work for us anymore, but the records are still valid for accountancy purposes.

frmStaff uses the query Q_StaffNOTExclude to show "live" staff only, from all locations (6 location options).

Does that help in any way?

I hope I'm not stepping on Wazz post. We must have been posting at the same time. If I am I will back off.

Is frmStaff a continuous form?

 

[wazz]

hi shaneman. go for it. but i'm watching.

 

[ShaneMan]

hi shaneman. go for it. but i'm watching.

Ok, now I'm nervous. Kind of like a coach watching you do a drill. You do it great when they aren't watching and you screw up when they are.

 

[Sgt Bilkp]

No, single form

 

[ShaneMan]

No, single form

Ok Sgt, I'll admit that I'm a little confused. In your first post you said "records" (plural) but then you also say that this is a single form. A single form is only going to show a record (single record), could you help me out with a little more details, sorry. After you use your two cboBoxes is there always only going to be one Location that matches and only one Staff that matches?

 

[wazz]

i think i had it in post #4. (just altered slightly).

then you just have to add an after_update event to cbo2:
me.recordset.findfirst "StaffID = " & me.cbo2