Beginner seeking help with VBA code.

[Kozbot]

Hello

I am developing a quality control database with 3 tables, the Product table containing all of our unique products with their quality specifications, a production run table, showing the lot numbers for each production run, and the quality control info table, which contains the QC info collected every 30 min or so from each production run.

I am trying to code the QC Info form to compare a value entered into one of the QC Info fields with the min and max values stored in the product table. For example product PS-140 has a carbon black specification between 38 and 42. So when the user selects PS-140 as the product and goes to enter in a lower or higher carbon black number, a msgbox pops up informing him that this entry is out of specification.

The way I set this up is to have a combo box look up the products and specifications. I want the code to compare the value entered into the field between the two product columns that specify the min and max values

This is what I have coded now.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(me.carbonblack_) then
     msgbox "Quantity is required"
     Cancel = True
     me.quantity.setfocus
     Exit Sub
If Me.CarbonBlack_ >= Me.cboProduct.Column(1) And Me.CarbonBlack_ <= Me.cboProduct.Column(2) Then
        MsgBox "CarbonBlack% is out of range.  Valid range is " & Me.cboProduct.Column(1) & " to " & Me.cboProduct.Column(2)
        Cancel = True	
        Me.CarbonBlack_.SetFocus
        Exit Sub
End If

The first aspect of the code works fine now. The 2nd aspect doesn't function despite the combobox displaying the values I want. I had it working fine but changed something and now the code does not reference the cboproduct.column(2)

Thanks for help!

[SoftwareMatt]

Try moving the code to the BeforeUpdate for the Carbon Black field as follows:

[vba]

If Me.CarbonBlack_ >= Me.cboProduct.Column(1) And Me.CarbonBlack_ <= Me.cboProduct.Column(2) Then

MsgBox "CarbonBlack% is out of range. Valid range is " & Me.cboProduct.Column(1) & " to " & Me.cboProduct.Column(2)

Cancel = True

End If

[/vba]

Also, what is product col 1 and col 2 as the if statement doesn't look right?