Subform Filter

[sullyman]

Hi folks,

I have multiple combos working together correctly as follows:

Year
Location
Department
Employee

The combos bring back the correct records for that individual employee contained on a subform when the last combobox (employee) is selected.


I'd like to break this down further so when i select each individual Combo in sequence as below:

1. Year: it will bring me back all records for that year,
2. Location: it will bring back all records for locations that match the year selected in the previous combo
3. Department: it will bring back all records for departments that match the year and location selected in the previous combos
4. Employee: it will bring back all records for that employee that match the year, location, course selected in the previous combos (This is currently working with the code above)

What code can i place within each combo afterupdate step to break down the records further and update the subform on each combo update so it would make easier for people to either edit records by Year, Location, Department or Employee in the subform below.

Can you tell me if the above is possible and point me in the right direction

Thanks,
Brian

 

[sullyman]

I have tried the following on the Afterupdate of the Department Combo but have no luck so far. Is the syntax correct?

If CboDept = "<All>" Then
Set FrmCHistoryEdit.RecordSource = "SELECT CHistory.*, CHistory.DepartmentID FROM CHistory"
Else
Set FrmCHistoryEdit.RecordSource = "SELECT CHistory.*, CHistory.DepartmentID FROM CHistory WHERE CHistory.DepartmentID = " & CboDept
End If

 

[pbaldy]

More like:

Me.FrmCHistoryEdit.Form.RecordSource = "SELECT..."

Presuming it's a subform of the form containing this code.

 

[sullyman]

Many thanks Paul, will try same. Thanks for your time. It is much appreciated

 

[sullyman]

Paul - Many thanks. The following got it working thanks to you


Me.FrmCHistoryEdit.Form.RecordSource = "SELECT DISTINCT ID, CHistory.YearID, CHistory.EmployeeID, CHistory.LocationID FROM CHistory " _
& "WHERE CHistory.[YearID] = " & Me.CboYears _
& " AND CHistory.[LocationID] = " & Me.CboLocations _
& " ORDER BY CHistory.LocationID"


I need to take a break now as its 10.30p.m. here in Ireland and i have been at this since 9a.m. My head is hurting

 

[pbaldy]

Have a beer at the local pub and get some sleep! Just visited my daughter in England, and enjoyed several pubs there.

 

[sullyman]

Hi Paul. Many thanks. I intend to have several large pints this weekend