onClick event

[tkip]

Hi Guys!

It's been a while I haven't asked questions here mostly because things are working fairly well and I have been able to fix things up on my own.

But this seemingly simply task confused the hell out of me and I know there is a solution for that.

What I have is a form with 4 combo boxes and I just want to change the rowsource property of the coombo boxes based on what I have selected on other 2 comboxes.

Here is the overview of the current status:

Classification1 : a combo box with rowsource set to
Me.cboBMclass1. RowSource = "Select Distinct [tblMetal].[Class] From [tblMetal]"


Classification2 : a combo box with rowsource set to the same rowsource:
Me.cboBMclass1. RowSource = "Select Distinct [tblMetal].[Class] From [tblMetal]"

Metal1 and Metal2 - the other two combo boxes which I would like to modify the rowsource on certiconditions .

Metal1 and Metal2 can't be selected and dropdown list won't show anything until both of the classifications are selected. Once the classifications are selected, populate apprppriate combo box based on Classification1 and Classification2 .
(i.e Classification1 populates Metal1, Classification2 populates Metal2)

So I put this code on "OnClick" event of Metal1 combo box:

If (IsNull(Me.cboBMclass1) Or IsNull(Me.cboBaseMetal2)) Then

    MsgBox "Please complete STEP ONE by selecting both Classification 1 and Classification 2.", vbInformation, "Step One Failed!"
    
    'Move cursor to empty classification field
    If IsNull(Me.cboBMclass1) Then
        Me.cboBMclass1.SetFocus
        Me.cboBaseMetal1.RowSource = ""
    ElseIf IsNull(Me.cboBMclass2) Then
        Me.cboBMclass2.SetFocus
        Me.cboBaseMetal2.RowSource = ""
     End If
   
Else
    Me.cboBaseMetal1.RowSource = "Select Distinct [tblMetal].[Metal] " & _
                                "From [tblMetal] " & _
                                "Where [tblMetal].[Class] = '" & [cboBMclass1].Value & "'"
                                                               
End If

I was hoping that this code will execute when I click on Metal1 combo box. But it didn't do anything (no prompt). Even when I selected both classifications , the rowsource did not change and the dropdown list shows nothing.

Any idea where I am doing wrong?

I did try placing the code on "Current" event. But since the current loads up as soon as the form loads, it runs before I have any change to select stuff from combo boxes.

I'd appreciate any help I can get on this.

Thanks
eric

[ChipR]

You want to use the AfterUpdate event of the combo box. Also, instead of checking with IsNull, use

If cboBMclass1.ListIndex < 0 or cboBMclass2.ListIndex < 0 Then
  Exit sub
End If

I do not think a message box is appropriate here. What happens when the user makes a selection from the first combo box? They get an error message, even though they haven't had a chance to select anything from the second combo box.

[tkip]

AfterUpdate event won't run until the control's field has been updated. So I didn't place it under AfterUpdate event. But I did change the first line of my code to what you have suggested (cboBMclass1.li stindex < 0 etc...) and placed it on "MouseDown" event to check if cboBMclass1 and cboBMclass2 fields are empty.

I then change the rowsource of combo box on "Form_Curre nt" event. Basically, I just split the code into two parts and place it on different event of the form.

It works just the way I want it now. Thanks for pointing me out another way to check combo box.

Thanks

[ChipR]

Glad you got it working!