Combo box issue..........need help!!!

[Chris1977]

Here is the situation folks, hopefully one of you will be able to help me out.

I have two combo boxes, cboIssueClass1 and cboIssueClass2. I have a list of issues in the first box based on a table called IssueClass1. That table has 2 columns, the ID field and the issue class field. In the issue class field are hardware names such as motherboard, hard drive, memory etc. In the table IssueClass2 which the 2nd combo box is based on contains multiple columns which correspond to the previous combo box and table. so for example in table 2 motherboard is broken down into issues such as defective onboard video, USB, defective sound etc.

What i want to happen is when they choose a value in the first combo box the associated column for that value will appear in the 2nd.

So if User A chooses motherboard in the first combo the only list which will be visible in the 2nd combo box is the one for motherboard. Then they can choose their issue in combo box 2. Is there a way to do this through coding?

Thanks in advance.
Chris

 

[godfrey62]

I think I have a solution, or at least a start for you as I have done something similar. I had two combo boxes and subsequently, two fields to populate: tBCSvcReq (Broad Service Category or whatever type of name you need for your IssueClass1.) and (not seen in code below) tSCSvcReq (Specific Category or in your case IssueClass2).

I would also suggest that you have two text fields on the form showing the values selected in your combo boxes as the combo boxes are unbound (at least I had them unbound). But that’s up to you.

Private Sub cboIssueClass2_GotFocus()

Dim stLinkCriteria As String
Dim stDocName As String

If Not IsNull(tBCSvcReq) Then

If cboIssueClass1 = "Motherboard" Then
‘Enable the second combo box.
cboIssueClass2.Enabled = True
‘The following puts the combo box value into the field in the table.
tBCSvcReq = "Motherboard"
‘The following is what sets the second combo box source to the desired table.
With cboIssueClass2
.RowSource = "tblMotherboard"
End With

ElseIf cboIssueClass1 = "Video" Then
cboIssueClass2.Enabled = True
tOtherSvcReq.Enabled = True
tBCSvcReq = "Video"
With cboIssueClass2
.RowSource = "tblVideo"
End With

ElseIf cboIssueClass1 = "Hard Drive" Then
cboIssueClass2.Enabled = True
tOtherSvcReq.Enabled = True
tBCSvcReq = "Hard Drive"
With cboIssueClass2
.RowSource = "tblHardDrive"
End With

ElseIf cboIssueClass1 = "Monitor" Then
cboIssueClass2.Enabled = True
tOtherSvcReq.Enabled = True
tBCSvcReq = "Monitor"
With cboIssueClass2
.RowSource = "tblMonitor"
End With


ElseIf cboIssueClass1 = "Miscellaneous" Then
tBCSvcReq = "Miscellaneous"
cboIssueClass2.Enabled = True
tOtherSvcReq.Enabled = True
With cboIssueClass2
.RowSource = "tblMiscellaneous"
End With


ElseIf cboIssueClass1 = "Sound Card" Then
cboIssueClass2.Enabled = True
tOtherSvcReq.Enabled = True
tBCSvcReq = "SoundCard"
With cboIssueClass2
.RowSource = "tblSoundCard"
End With

‘If you have some that don’t have a second box, then let user know no other selection needed with a message box.
ElseIf cboIssueClass1 = "Floppy Drive" Then
MsgBox "This category does not have any more " _
& "specific sub-categories. Use the Comments " _
& "section for any additional info."

tBCSvcReq = "Floppy Drive"
cboIssueClass2.Enabled = False
tOtherSvcReq.Enabled = False

ElseIf cboIssueClass1 = "RAM" Then
MsgBox "This category does not have any more " _
& "specific sub-categories. Use the Comments " _
& "section for any additional info."

tBCSvcReq = "RAM"
cboIssueClass2.Enabled = False
tOtherSvcReq.Enabled = False

ElseIf cboIssueClass1 = "BIOS" Then
MsgBox "This category does not have any more " _
& "specific sub-categories. Use the Comments " _
& "section for any additional info."

tBCSvcReq = "BIOS"
cboIssueClass2.Enabled = False
tOtherSvcReq.Enabled = False

End If

End If


End Sub



I hope this helps a little. If I’ve confused you let me know.

Heidi S.
specktc@aol.com

 

[Chris1977]

First off thanks for the reply.

The only part that i was unsure on what where you refrenced the row source as tblVideo etc. I have the video, motherboard etc logging issues all in one table. I was trying to reference the column in the coding like this:

If Me!cboIssueClass1.Column(2, 4) Then
Me!cboIssueClass2.Column (4)
End If

that unfortunately was an experiment that didn't work. So in order to log both combo boxes and have them write back to a table for reporting purposes i'll need to create a seperate table for each issue i.e. hard drive, memory etc?

thanks

 

[godfrey62]

Chris,
Yes, unfortunately separate tables is the only way I know how to handle the different data. If someone else "out there" knows how to identify certain rows, Chris and I would love to hear it!!

My thoughts, though, Chris, are if your data ever needs updating, you would have to go back in to each line of code and change the rows identified (if that is a solution someone else submits) whereas if you have a separate table for each IssueClass2, then you can update each table independently and not have to worry about affecting other code. I have one table for IssueClass1 and then different ones for IssueClass2 based on IssueClass1 selected. Sorry I didn't really say that in my first posting.

Heidi

 

[Chris1977]

Heidi,

Again thanks for the help. I'm going to go ahead and create the different tables then let you know how it goes.

Later,
Chris

 

[mickeync]

Hello all,
Thanks to Heidi's code... I copied your coding and modified to work on my problem. It worked great !!! However when I click on 2nd combo box (On Chris's issue that would be IssueClass2) I got an error message "can't find macro ..."
I tried Lost Focus event but it didn't work !!! Logically I think I need some more even to leaving the 2nd combo box without having any error (I mean updating before leaving this field )right ??? any idea Heidi ??? Thanks so much ...

Mickey