Kozbot
02-26-2013, 07:33 AM
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!
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!