Cascading combo boxes that will be autonomous record to record

[Dawnit]

I need major help with my form that has triple cascading combo boxes.

Combo box 1 is bound, combo box 2 and 3 are unbound. They cascade perfectly, EXCEPT when it comes to using them in a form where I need to have my selections of each combo box save to the underlying table AND work independently from record to record.

The way it works now is whatever combo box selection I make in one record gets automatically applied to all records.

I would greatly appreciate any and all help you can give. Thanks!!

 

[jbehrne]

Hi,

Post your code and I'll have a look.

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Dawnit]

Thank you for your time! Here is my code:

Private Sub Combo1_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

cboCombo2 = Null
cboCombo3 = Null

strSQL = "SELECT DISTINCT tblDemo.Combo2 FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.Combo1 = '" & Combo1 & "'"
strSQL = strSQL & " ORDER BY tblDemo.Combo2;"

cCombo2.Combo2 = strSQL

strSQLSF = "SELECT * FROM tblDemo "
strSQLSF = strSQLSF & " WHERE tblDemo.Combo2 = '" & cboCombo2 & "'"

End Sub

Private Sub cboCombo2_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

cboCombo3 = Null

strSQL = "SELECT DISTINCT tblDemo.Combo3 FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.Description = '" & cboCombo2 & "'"

cboCombo3.Combo2 = strSQL

strSQLSF = "SELECT * FROM tblDemo "
strSQLSF = strSQLSF & " WHERE tblDemo.Combo2 = '" & cboCombo2 & "'"

End Sub

Private Sub cboCombo3_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

strSQL = "SELECT DISTINCT tblDemo.Combo3 FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.Combo2 = '" & cboCombo2 & "'"

strSQLSF = "SELECT * FROM tblDemo "
strSQLSF = strSQLSF & " WHERE tblDemo.Combo2 = '" & cboCombo2 & "'"

End Sub


I patiently yet anxiously await your reply. Feel free to let me know if you have any other questions.

THANKS!!

 

[jbehrne]

Hmmm... I don't see were you are saving any information back to your table. In the after update event, at the end of your current code, of combox put this in (change names to match your fields):

- Combobox 1
Me!Field1Name = Me!Combo1
- Combobox 2
Me!Field2Name = Me!Combo2
- Combobox 3
Me!Field3Name = Me!Combo3

- That should save the information to your underlying table.
- See if that works for saving your info and if if clears up the problem of not working independtly from record to record.

jbehrne



If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Dawnit]

Thanks for the quick reply, I will give it a try and let you know!!

 

[Dawnit]

Okay, I gave your suggestion a try, but I'm not an expert on code and I may have interpreted your instructions incorrectly.

Further, I don't mean to make it harder for you to help me, but I have changed the names in the code below to match my fields:

My Form:
ComboBox1 = cboFunc
Combobox2 = cboDescription
Combobox3 = cboSubdescrip

The Underlying Table:
Field1 = Function
Field2 = Description
Field3 = SubDescrip

The cboFunc is working independently and my selections are saving to the Function field, BUT when I make a selection in the Description combobox, it changes in all records, but saves only to that record in the underlying table. Same problem with the third combobox. I hope you can help me! I just need to resolve this problem and my year's work on this database will not be a waste. Crossing my fingers...

Following is the code as I have updated it with your suggestion:

Private Sub cboFunc_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

cboDescription = Null
cboSubdescrip = Null

strSQL = "SELECT DISTINCT tblDemo.Description FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.Function = '" & cboFunc & "'"
strSQL = strSQL & " ORDER BY tblDemo.Description;"

cboDescription.RowSource = strSQL

strSQLSF = "SELECT * FROM tblDemo "
strSQLSF = strSQLSF & " WHERE tblDemo.Description = '" & cboDescription & "'"

Me!Function = Me!cboFunc

End Sub

Private Sub cboDescription_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

cboSubdescrip = Null

strSQL = "SELECT DISTINCT tblDemo.SubDescrip FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.Description = '" & cboDescription & "'"

cboSubdescrip.RowSource = strSQL

strSQLSF = "SELECT * FROM tblDemo "
strSQLSF = strSQLSF & " WHERE tblDemo.Description = '" & cboDescription & "'"

Me!Description = Me!cboDescription

End Sub

Private Sub cboSubdescrip_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

strSQL = "SELECT DISTINCT tblDemo.SubDescrip FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.Description = '" & cboDescription & "'"

strSQLSF = "SELECT * FROM tblDemo "
strSQLSF = strSQLSF & " WHERE tblDemo.Description = '" & cboDescription & "'"

Me!SubDescrip = Me!cboSubdescrip

End Sub

THANKS!!!!

 

[jbehrne]

Hmmm... Can you send me a zipped copy of the db? I'll be glad to look at it and find out what is going on (easier than debugging through the posts...) You can send it to: jbehrne@hotmail.com
(Include any instructions, etc. about what is going on).

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Dawnit]

Thank you! My zipped file is on its way