Cascade and Filter

[Trevor G]

HI folks, its been a while since I have either posted for a solution or answered other threads.

I have an issue which I hope someone can help me with. I have a table tblstaff1 and a forom frmStaff1, on the form I am showing the records in a continous form with 2 combos, what I am looking to achieve is to select the first drop down which shows Department names and when I select from it, it then fills the Office combo with a filtered list and once I select from there it then filters the the records. Any help would be much appreciated.

Attached is a sample database.

 

[JHB]

I've made the change and return the database.

 

[Hakello]

Hello,

I tried to adapt the above to my own database as I need the same except that my data is in 2 seperate tables, however my second combobox completly emtpies when I select something in the first cbo.

Please note I'm an absolute novice in both Access and SQL

I want users to be able to select a 'vakcode' and have the second cbo only show 'toetscode' where the vakid is the same as the vakid from the selected 'vakcode'

Private Sub cboVaknaam_AfterUpdate()
On Error Resume Next
Me.cboToetscode.Visible = True
Me.cboToetscode.RowSource = "Select toetscode " & _
"FROM Vak INNER JOIN Toets ON Vak.vakid = Toets.vakid " & _
"WHERE vaknaam = '" & cboVaknaam & "' " & _
"ORDER BY toetscode"
Me.cboToetscode = Null
Me.cboToetscode.SetFocus
End Sub

Is there anything obviously wrong?

My database design can be found here
img6.imageshack.us/img6/9408/databaseontwerp.jpg
(sorry not allowed to post it as link or image)

 

[Trevor G]

I've made the change and return the database.

JHB thank you for your wonderful assistance this works really well.

I hope you enjoy your weekend.

 

[Trevor G]

Hello,

I tried to adapt the above to my own database as I need the same except that my data is in 2 seperate tables, however my second combobox completly emtpies when I select something in the first cbo.

Please note I'm an absolute novice in both Access and SQL

I want users to be able to select a 'vakcode' and have the second cbo only show 'toetscode' where the vakid is the same as the vakid from the selected 'vakcode'



Is there anything obviously wrong?

My database design can be found here
img6.imageshack.us/img6/9408/databaseontwerp.jpg
(sorry not allowed to post it as link or image)

Welcome to the forum,

Follow this link it may well help as with your issue it has examples as well which you can download.

http://fontstuff.com/access/acctut10.htm

 

[Hakello]

Thank you Trevor, thats a very usefull site!

The problem however persists, the second dropdown list becomes completly empty when I select something, as if my sql querry is faulty. I made a querry to check but that worked fine. Then I tried what happened if you selected something from the second cbo first (after adding the approperiate VBA ofcourse ) and then my first list becomes empty .

I've removed most of the non-relevant objects and attached the database to this post. (Please note only HSUM-NED has some relevant data atm)

the FRM_Cijfers has 3 combo boxes, the third currently does nothing, the first and second are supposed to be cascading eachother.

-EDIT-
Uh just found this thread again, I've resolved this issue some time ago but I don't remember what I did exactly. Sorry