Synchronized Combo Boxes

[LesleaOH]

I have a database set up for time sheets and need help creating some combo boxes that are synchronized based on the information in the first box, then the second box which populates the third box. The joint table is called Timesheet. The first combo box (cboStorm) would pull StormID from Storm Names table. The second box (cboPW) would pull PWNumberID from PW Codes table. (There is a column in PW Codes called StormID to tie them together) The third combo box (cboCref) would pull CPSBRefID from CPSBRef table. (There is a column in CPSBRef called PWNumberID to tie them together)



Can someone please help me out? I've been able to get the first two to synchronize but I kept getting a parameter error. I tried to use the VBA method off of the Microsoft Access site with the following code as an After Update Event for cboStorm.

Option Compare Database

Private Sub cboStorm_AfterUpdate()
Me.cboPW.RowSource = "SELECT PWNumberID FROM" & _
" PW Codes WHERE StormID = " & Me.cboStorm & _
" ORDER BY PWNumberID"
Me.cboPW = Me.cboPW.ItemData(0)

End Sub

I can upload a test database also to my website if someone needs to look at it/can help. Thanks!

[RuralGuy]

Maybe this link will help.

[LesleaOH]

I did find that one when I searched earlier but it only has one table where I am pulling from different tables. I did try it but couldn't figure it out.

[RuralGuy]

You can zip the file and post it here if you like. Scroll down to Manage Attachments when posting.

[LesleaOH]

Here is the file. I was attempting the combo boxes on Form1

[RuralGuy]

Go ahead and edit the post and upload the file this time.

[LesleaOH]

Oh hush :-P I was in the process of doing that once I realized it wasn't there :-)

[RuralGuy]

See if this one works for you. Strings need to be enclosed with single or double quotes and names with embedded spaces need to be enclosed with brackets.

[LesleaOH]

Oh wow, thanks a bunch!! I'll play with it but it looks like exactly what I needed.

[RuralGuy]

Glad I could help. Are you ready to use the Solved thread tool yet?

[LesleaOH]

As soon as I get it worked into my timesheet form with no problems :-) I have a wee bug to fix first

[LesleaOH]

OK, it all works so I can do the Solved button now. Thank you so much for your help

[RuralGuy]

You're very welcome.

[LesleaOH]

I have another question about the form. I have it integrated and it works just fine entering the information. The only problem is after I enter the information, then go to the next record to enter new info, the choice from the first box, cbostorm is defaulting to the same value from the previous record. Any ideas on how I can make it go back to showing blank?

[RuralGuy]

You can clear it in the Current Event of the form is Me.NewRecord is true.