Form cascading combo boxes

[Jackson1942]

Have researched cascading combo boxes and am unable to find out if they can be based on a query instead of tables?

I have a query that pulls information from four different tables named Query1.

On the Form is it possible for the combo boxes RowSource to be Query1?

 

[RainLover]

Your Combo can get it's information from anywhere. Table or Query.

Are you happy with what you are trying to do or do you need further help.

 

[Jackson1942]

I'm going to need some help. Give me some time to document what I have and have done befor I reply again.

Thanks

 

[RainLover]

No worries.

 

[Mile-O]

Very quick example of a cascading combobox situation. Only has five items in total. Two tables, two queries, and one form. (And one line of code in that form.)

Maybe stripping the situation to the bare bones, like this, can help you understand how cascading comboboxes work.

 

[Jackson1942]

Rain,

I found my problem with the cascading combo boxes; I left out the “Dim strSource As String” statement in the After Update Event Procedure. Event Procedure follows:

Private Sub cboGroup_AfterUpdate()
Dim strSource As String

strSource = "SELECT DISTINCT Employee " & _
"FROM Query1 " & _
"WHERE [ITS Group] = '" & Me.cboGroup & "' ORDER BY Employee"
Me.cboEmployee.RowSource = strSource
Me.cboEmployee = vbNullString
End Sub



Now I am trying to run a report based on an employee selected from the cboEmployee combo box.

In Query1 I added Like [Forms]![Form1]![cboEmployee] & “*” to the Employee field.
I added a button to Form1 to run Report1.
I selected an employee from the cboEmployee combo box and selected the Run Report button. The report returned every entry in Query1.

I have set up combo boxes before saving the selection for later use to run reports.

How do I save a selection from the cascaded cboEmployee combo box so that it will be used to run Report1?

 

[RainLover]

Your Combo should be getting its value from the Employee Table not the Query.

Your Employee Table shoud be designed like this.

tblEmployees (Name of Table)

EmployeePK as Autonumber (A unique identifer)
FirstName as Text
LastName as Text
Other stuff like Phone, DOB, NextOfKin etc

Then your Combo box's RecordSource should include EmployeePK, FirstName & " " & LastName)

It should show a Concatenated View of the Name with the PK hidden. All searches should be based upon the PK not the actual name.

Private Sub cboGroup_AfterUpdate()
Dim strSource As String

strSource = "SELECT EmployeeID, FirstName & " " & LastName as EmpName " & _
" FROM tblEmployees " & _
" WHERE [ITS Group] = '" & Me.cboGroup & "' ORDER BY LastName"
Me.cboEmployee.RowSource = strSource

End Sub

Code Not Tested.

 

[Jackson1942]

Rain,

My tables were already created, I created a query pulling information from four tables. Created a Form based on the query and aded the cascading cbo boxed. When I selected the first cbo box it populated the second cbo box correctly. When I made a selection from the second cbo box and selected a button to run a report for the selection I received everything in the query instead of just the item selected from the second cbo box.

I have curcumvented the problem; created a cbo that pulls the employee directly to run the report.

Thanks for your help.

 

[RainLover]

I have curcumvented the problem; created a cbo that pulls the employee directly to run the report.

Sounds like you Solved the problem, not curcumvented it.

That is if I understand you properly.