How do you make a combo box update a field no matter which order you enter it?

[DanicaDear]

I have found a little trip-up in my otherwise terrific database. When you go on a form to enter order details, you first choose a CLASS and then you choose a SIZE. When doing this in that order, a third combo box automatically populates with the item number (COMMODITY_NUM). If you make an error and change the SIZE, the COMMODITY_NUM will update itself. However, if you made an error to the CLASS and go back to change it after you have already entered a size, the COMMODITY_NUM will not update, and it ends up storing the wrong information. Can someone tell me what to do to my code to fix it?
Thanks in advance!

Option Compare Database
 
Private Sub cboClass_AfterUpdate()
If Not IsNull(Me![CBOclass]) Then
  Me![cboSIZE].RowSource = "SELECT [SIZE] FROM tblCOMMODITY WHERE [CLASS] = '" & Me![CBOclass] & "' ORDER BY CInt(IIf(IsNumeric(Right([SIZE],1)),[SIZE],Left([SIZE],Len([SIZE])-1)));"
End If
End Sub
 
Private Sub cboSize_AfterUpdate()
If Not IsNull(Me![CBOclass]) And Not IsNull(Me![cboSIZE]) Then
  Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY", "[CLASS] = '" & Me![CBOclass] & _
                       "' And [SIZE] = '" & Me![cboSIZE] & "'")
End If
End Sub

[NeoPa]

The approach to take when dealing with multiple controls that effect filtering, or any other response really, is to design a function to do the work generally. This function is then called when any of the determining controls is changed. Clearly the function would be built to handle all the data it needs (not just the most recently changed control) so it should work correctly whichever order the controls are updated.

[DanicaDear]

Ok NeoPa, I think I get what you are saying. I need to remove the DLOOKUP function and build some altogether new code, right? Or could I just add another DLOOKUP to the first sub, perhaps using an IF statement to say IF a size has been chosen, then DLOOKUP my COMMODITY_NUM?

[NeoPa]

Danica:
Ok NeoPa, I think I get what you are saying. I need to remove the DLOOKUP function and build some altogether new code, right?

Not really. The code should be the same for both. The code should handle both fully. The format of your code should be something like the following :

Option Compare Database
Option Explicit
 
Private Sub cboClass_AfterUpdate()
    Call ChangeRowSource()
End Sub
 
Private Sub cboSize_AfterUpdate()
    Call ChangeRowSource()
End Sub
 
Private Sub ChangeRowSource()
    With Me
        If Not IsNull(.CBOclass) And Not IsNull(.cboSIZE) Then
            .txtCommodityNum = DLookup("[COMMODITY_NUM]", _
                                       "tblCOMMODITY", _
                                       "[CLASS] = '" & .cboClass & "' And " & _
                                       "[SIZE] = '" & .cboSize & "'")
        End If
    End With
End Sub

This code is mainly guesswork, but the format is important. Notice how both event procedures call the common procedure where all the actual work is done. Notice also the Option Explicit line (#2). This is important (See Require Variable Declaration).

PS. The name of the procedure may be a bit wrong for the code, but I couldn't tell what was what when I set it up.

[DanicaDear]

Yes, that did it. I guess you can tell I have launched my DB and am now finding just really small things that I didn't notice before. Everyone has been really pleased with my project so far, though. (especially me!!)

Thanks so much for ALL your help. I could have never been successful without Bytes and it's experts!

[NeoPa]

I'm really pleased to hear it Danica. You deserve some approbation for your approach to it. You've always tried to get it as right as possible and never shied away from some complicated new approaches. Good for you :-)

[DanicaDear]

Last year I got a $500 bonus for my efforts! :-) It was very flattering! (Then I thought to myself, I should send a portion of this to NeoPa.) LOL.

[NeoPa]

I'll send you my details :-D

Good for you though. I'm really pleased for you. That's always nice. When you're appreciated.

PS. Next time you're in London you can buy me a pint.