Cascading Combobox

[DMerchen]

This seems like a pretty hot topic right now, but I am running into problems with this. I am trying to filter a timesheet using two listboxes. I must be doing something wrong.

I have an timesheet table that pulls information from the employee table and date tablem to create the timesheet. The timesheet table looks up values from the employee table and the date tables.

When I create a form to create the filter for the timesheet I want to be able to choose the employee and then fill another listbox with only the dates of timesheets that can be chosen. I am really stuck about how to accomplish this. It seems like you have to have two tables linked together with keys. I have the TimesheetID, and then EmployeeID, and DateID from separate tables all in the timesheet table. Is there a way to accomplish this? I hope this makes sense.

 

[John Big Booty]

You need to create a query that selects TimeSheetID, using the result of your first Combo as a criteria for EmployeeID ([Forms]![FRM_Nmae]![ComboName])

This query will populate your second Combo/Listbox. You will need to put the following in the OnChange event of your first Combo

Me.ComboName.Requery

 

[DMerchen]

I am still a bit lost. I used the code from a previous link on this forum, but I am not sure how to apply this to my table for my comboboxes. This is what I have.

Private Sub cboEmployee_AfterUpdate()
With Me![cboDate]
If IsNull(Me!cboEmployee) Then
.RowSource = ""
Else
.RowSource = "SELECT [Date]" & _
"FROM tblTimecard" & _
"WHERE[Employee]=" & Me!cboEmployee
End If
Me.cboEmployee.Requery
End With

End Sub

I choose an employee, but the date combobox never shows any dates.

Any help is appreciated.

 

[Singh400]

Try this...

In cboEmployee change the _AfterUpdate() to:

Private Sub cboEmployee_AfterUpdate()
Me.cboDate.Requery
End Sub

Then in cboDate, set the RowSource as:

SELECT DISTINCT [tblTimecard].[Date] FROM [tblTimecard] WHERE [tblTimecard].[Employee] = [cboEmployee]

That should work. I'm new to this stuff (new to the coding side of access anyway) myself.

 

[DMerchen]

That didn't seem to work for me. I choose the employee name, but the second combobox doesn't populate with the dates. It seems like it should work, but I must be missing something. Any other ideas? I am beating my head against the wall at this point.

 

[Singh400]

Could be because you are using Date as a field name. Access doesn't like this at all. It's a reserved word. Using reserved for anything is up there with the evils of lookup fields. Change that field name to something else.

What I post should work, because I'm using the exact same method for my database. I just changed the corresponding values.

 

[DMerchen]

Thanks for the response. It still does not work. I get the employee values and I changed the other combobox value to TimecardDate instead of date. I am looking up the values from two separate tables (Employees and Timecard Dates)to pull into the timecard table where I give the timecard a unique key. How are you doing this? I am wondering if my structure is just set up poorly or incorrect. Can you post a small example? Any help is appreciated.

 

[John Big Booty]

Have a look at the attached example.

The important parts are the Query that uses the selection from the first Combo as a criteria.

The other important part is the piece of code that forces the second Combo to requery it's source when ever the first combo is change.

 

[DMerchen]

Here is a small sample of the database that is not working. I put this together to try some things, but I am still failing to get the second combobox to populate based on the first choice. I believe it could be something pretty simple so I post this in hopes that someone can point out how I am missing this. Perhaps it is my structure, or a simple command, I am just not sure. Any assistance is appreciated.

 

[John Big Booty]

I'm not familiar with the method you where using, and couldn't get it working. One problem I did find was that you where reqerying your initial Combo rather than the Combo your are cascading to.

I've got it working by using a query to feed your second Combo, rather than the SQL you where using.

 

[DMerchen]

Wow, that works slick. I am still trying to figure out all of the details how you accomplished this. Thanks for your help.

 

[John Big Booty]

It's pretty straight forward.

Check the new query I created. The second combo uses that as it's row source. The second Combo is forced to requery any time the first combo is changed.

 

[DMerchen]

Strange this is happening that I didn't realize with this before. The second combobox does update, but I can't select a different date. When I try to choose a different date the combobox updates to the value at the top of the list. Why would this be?

 

[boblarson]

I don't get why you are selecting the date from the list anyway. The date must exist for that employee for you to select.

Anyway to fix your current problem, go into the second combo and in the query underlying the rowsource (not the saved query), remove the employee ID field and change the combo's number of columns to 1 and remove the column sizes.

 

[DMerchen]

Thanks, I did eventually figure it out. It just took longer than expected. Seems I did just like you suggested, so it seems it is good now. Thanks for the response.