Different validation rules for different product entries?

[Kozbot]

Hello everyone.

I am (kind of) new to Microsoft Access and have a question about making validation rules more complex.

I am making a quality control database with 3 tables. One containing the product and their unique codes, the 2nd with their unique lot numbers for each different production run, and the 3rd with all the required quality control data for each product.

Each different product has different required specifications. For example, Product AB-544 needs a value between 11-20 while product RY-233 needs a value between 21-30.

My question is: Is there a method of defining different validation rules for different product codes? I want the QC tech entering the data to only be able to enter in quality data that is within the required specification defined by the product code. If the current product is AB-544 he should only be able to enter in 11-20 but if he is entering in data for RY-233 he should only be able to enter 21-30.

If this is possible without knowing visual basic (which I am learning) that would be awesome. Any help is greatly appreciated!

 

[jdraw]

Have you designed your tables and relationships yet?
Seems you have at least these tables.

Products
Lots
QualitySpec

then there's the Product meets QualitySpec or not?
Product was created in Lot X

????

 

[Kozbot]

Have you designed your tables and relationships yet?
Seems you have at least these tables.

Products
Lots
QualitySpec

then there's the Product meets QualitySpec or not?
Product was created in Lot X

????

yes I have. I have those 3 tables. Products has a one to many relationship with both lots and QualitySpec. While Lots has a one to many relationship with QualitySpec.

 

[Pat Hartman]

You can't do this with validation rules on a column. You might be able to do it with a validation rule on the table but that would be too complex to read. I would also not hard-code something like this either. I would add the from and to range values to the product table. Then in my form's BeforeUpdate event, I would compare the value entered to the range from the product table. The most efficient method is to include the from and to in the RowSource of the combo used to select the product (don't forget to update the column count or the combo will break). Then to do the validation, you would use code something like:

If IsNull(Me.Quantity) Then
    msgbox "Quantity is required.", vbokOnly
    Cancel = True
    Me.Quantity.Setfocus
    Exit Sub
Else
    If Me.Quantity >= Me.cboProduct.Column(2) and Me.Quantity <= Me.cboProduct.Column(3) Then
    Else
        msgbox "Quantity is out of range.  Valid range is " & me.cboProduct.Column(2) & " to " & Me.cboProduct.Column(3)
        Cancel = True
        Me.Quantity.SetFocus
        Exit Sub
End If

You may need to modify control names or the column indexes to conform with your existing names. Remember the columns of the RowSource are a zerobased array so the first column is referenced as (0) (this is usually the bound column so we never refer to it this way), the second column is (1), the third is (2), etc.

 

[Kozbot]

Thank you

So apparently this isn't easy to do as I thought. The code you posted is visual basic correct?

 

[Pat Hartman]

Aparently not The code is VBA (visual basic for applications) because that is what Access uses. Although I posted air code, it is complete. You just have to modify the control names and make sure the RowSource of the combo includes the new columns so I've done all the heavy lifting for you.

 

[Kozbot]

Aparently not The code is VBA (visual basic for applications) because that is what Access uses. Although I posted air code, it is complete. You just have to modify the control names and make sure the RowSource of the combo includes the new columns so I've done all the heavy lifting for you.

Thank you. This is making more sense to me as I learn more VBA.

When you say "row source" you just mean the columns the code is looking to for the values? "Combo" is defined as...?

 

[jdraw]

There are tutorials and examples of comboboxes at

http://www.techonthenet.com/access/comboboxes/

 

[Kozbot]

You can't do this with validation rules on a column. You might be able to do it with a validation rule on the table but that would be too complex to read. I would also not hard-code something like this either. I would add the from and to range values to the product table. Then in my form's BeforeUpdate event, I would compare the value entered to the range from the product table. The most efficient method is to include the from and to in the RowSource of the combo used to select the product (don't forget to update the column count or the combo will break). Then to do the validation, you would use code something like:

If IsNull(Me.Quantity) Then
    msgbox "Quantity is required.", vbokOnly
    Cancel = True
    Me.Quantity.Setfocus
    Exit Sub
Else
    If Me.Quantity >= Me.cboProduct.Column(2) and Me.Quantity <= Me.cboProduct.Column(3) Then
    Else
        msgbox "Quantity is out of range.  Valid range is " & me.cboProduct.Column(2) & " to " & Me.cboProduct.Column(3)
        Cancel = True
        Me.Quantity.SetFocus
        Exit Sub
End If

You may need to modify control names or the column indexes to conform with your existing names. Remember the columns of the RowSource are a zerobased array so the first column is referenced as (0) (this is usually the bound column so we never refer to it this way), the second column is (1), the third is (2), etc.

I put this project on hold for a bit now i am back to it.

I have added two columns to the product table titled MinCarbonBlack and MaxCarbonBlack.

I have made a combo box that has row source of the 3 fields now in the Product table (Product, MinCarbonBlack, MaxCarbonBlack)

I am trying to compare the quantity entered into CarbonBlack% field in the QC Data table (a different table then the Product Table but has a relationship with the product table) to those two fields in the Product table.

When I try to reference the fields while writing the code after the "else" operator the fire nothing appears in the drop down box. Am I missing something in getting the code to compare the value entered in to the QCData form (which is the form i'm coding in) to the fields in the Product table?

I apologize if my explanation is confusing.

 

[Pat Hartman]

The RowSource for the combo should include all three fields. The first is visible and is the bound column. The second two can be hidden (0 width).

If Me.txtSomeQty >= Me.cboProduct.Column(1) AND Me.txtSomeQty <= Me.cboProduct.Column(2) Then
    'good path
Else
    'bad path
End If

 

[Kozbot]

The RowSource for the combo should include all three fields. The first is visible and is the bound column. The second two can be hidden (0 width).

If Me.txtSomeQty >= Me.cboProduct.Column(1) AND Me.txtSomeQty <= Me.cboProduct.Column(2) Then
    'good path
Else
    'bad path
End If

Okay good I have done exactly this with the combobox for selecting products.

My confusion is arising when go to type the above code. When I type to reference Carbon black QC data, the field shows up selectable up in a drop down menu. When I go to refer to the min and max values contained in the Product table, those fields do not appear in the drop down menu.

Am I missing a step in getting those combo box columns to appear in the VBA editor?

 

[gemma-the-husky]

Thank you

So apparently this isn't easy to do as I thought. The code you posted is visual basic correct?

no - it's easy - it just can't be done by specifiyng the condition as part of the table design.

instead, its something that you need to build into the database as part of the code that you run to validate particular actions.

 

[Pat Hartman]

My confusion is arising when go to type the above code. When I type to reference Carbon black QC data, the field shows up selectable up in a drop down menu. When I go to refer to the min and max values contained in the Product table, those fields do not appear in the drop down menu.

Am I missing a step in getting those combo box columns to appear in the VBA editor?

Notice that the code I posted refers to the name of the combobox qualified by a column number. You can't reference the individual columns of the combo's RowSource by name. They are a zero-based array so you need to use the column property with an index number.

 

[Kozbot]

Notice that the code I posted refers to the name of the combobox qualified by a column number. You can't reference the individual columns of the combo's RowSource by name. They are a zero-based array so you need to use the column property with an index number.

YES!! I got it to work. I am so happy I almost flipped my desk over.

The msgbox opens up with quantity is required if nothing is entered.

However, regardless of the value I put into the field, the "valid range is between 25-30 still appears, even if the value is between those numbers.

What could be causing this?

Also I got a "block if without end if" error message so I put another End If at the end of the code. My code now looks like this

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.CarbonBlack_) Then
    MsgBox "Quantity is required.", vbOKOnly
    Cancel = True
    Me.CarbonBlack_.SetFocus
    Exit Sub
Else
    If Me.CarbonBlack_ >= Me.Product.Column(1) And Me.CarbonBlack_ <= Me.Product.Column(2) Then
    Else
        MsgBox "Quantity is out of range.  Valid range is " & Me.Product.Column(1) & " to " & Me.Product.Column(2)
        Cancel = True
        Me.CarbonBlack_.SetFocus
        Exit Sub
End If
End If
End Sub

If this correct syntax?

 

[gemma-the-husky]

If Me.Quantity >= Me.cboProduct.Column(2) and Me.Quantity <= Me.cboProduct.Column(3) Then

in this line, with combo boxes, you may be getting a comparison with TEXT rather than numbers.

try adding the val function
If Quantity >= val(cboProduct.Column(2)) and Quantity <= val(cboProduct.Column(3)) Then

also, are you sure quantity is a numeric field?

 

[Kozbot]

If Me.Quantity >= Me.cboProduct.Column(2) and Me.Quantity <= Me.cboProduct.Column(3) Then

in this line, with combo boxes, you may be getting a comparison with TEXT rather than numbers.

try adding the val function
If Quantity >= val(cboProduct.Column(2)) and Quantity <= val(cboProduct.Column(3)) Then

also, are you sure quantity is a numeric field?

This is what I first thought. But both fields are defined as numeric in the table they refer too.

edit: the value function works!! omg i'm so happy. thank ya'll

 

[Kozbot]

Now my question is:

I have multiple fields that require validation like this. Do I need to add another beforeupdate event for every field required

when I code for the next field required I get an "Invalid Use of Null "error message. this message appears when I attempt to enter a value into the next field (called MFI)

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.CarbonBlack_) Then
    MsgBox "Quantity is required.", vbOKOnly
    Cancel = True
    Me.CarbonBlack_.SetFocus
    Exit Sub
Else
    If Me.CarbonBlack_ >= Val(Me.Product.Column(1)) And Me.CarbonBlack_ <= Val(Me.Product.Column(2)) Then
    Else
        MsgBox "Quantity is out of range.  Valid range is " & Me.Product.Column(1) & " to " & Me.Product.Column(2)
        Cancel = True
        Me.CarbonBlack_.SetFocus
        Exit Sub
End If
End If
If Me.MFI >= Val(Me.Product.Column(3)) And Me.MFI <= val(Product.Column(4)) Then
Else
    MsgBox "Quantity if out of range. Valid range is " & Me.Product.Column(3) & " to " & Me.Product.Column(4)
    Cancel = True
    Me.MFI.SetFocus
    Exit Sub
End If

 

[Kozbot]

The me.product.column(3) and (4) are returning IsNull values despite me entering in quantities into the field. I also updated the column count in the combo properties to accommodate the next min and max values (which would be in columns 3 and 4). What am I missing that would make me.product.column(3) and (4) return IsNull?

 

[Pat Hartman]

If you added the values after you opened the form and the combo's rowSource was populated, you wouldn't get the values so you need to close and reopen the form to force it to open.

Since the Val() function fixed your problem, you now know that the columns were incorrectly defined so you should fix them.

Whenever you have a text field, you need to be aware that if you didn't set the AllowZLS property to no at the table level for the column, ZLS are valid. Except for very specialized cases, I choose to set that property to no for the vast majority of my text fields. But, when validating form fields you still need to check for ZLS because the user can enter them. To combine the two tests, try this:

If Me.MyField & "" = "" Then   ' field was null or ZLS
    Msgbox "Field must contain a value.", vbokONly
    ....
End If

 

[Kozbot]

Both the fields in the product table and the fields being entered into the QC Info table are defined as numeric but are still require the val function to operate as intended. What else could be causing this?

Other then that I updated the combobox to accept all the columns for all the min and max values of my specifications. This aspect of the code is functioning as I want it.

here is what my code looks like so far

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.CarbonBlack_ >= Val(Me.Product.Column(1)) And Me.CarbonBlack_ <= Val(Me.Product.Column(2)) Then
        MsgBox "CarbonBlack% is out of range.  Valid range is " & Me.Product.Column(1) & " to " & Me.Product.Column(2)
        Cancel = True
        Me.CarbonBlack_.SetFocus
        Exit Sub
End If
If Me.MFI >= Val(Me.Product.Column(3)) And Me.MFI <= Val(Me.Product.Column(4)) Then
    MsgBox "MFI is out of range.  Valid range is " & Me.Product.Column(3) & " to " & Me.Product.Column(4)
        Cancel = True
        Me.MFI.SetFocus
        Exit Sub
End If
If Me.PelletSize >= Val(Me.Product.Column(5)) And Me.PelletSize <= Val(Me.Product.Column(6)) Then
    MsgBox "Pellet Size is out of range. Valid range is " & Me.Product.Column(5) & " to " & Me.Product.Column(6)
    Cancel = True
    Me.PelletSize.SetFocus
    Exit Sub
End If
If Me.BulkDensity >= Val(Me.Product.Column(7)) And Me.BulkDensity <= Val(Me.Product.Column(8)) Then
    MsgBox "Bulk Density is out of range. Valid range is " & Me.Product.Column(7) & " to " & Me.Product.Column(8)
    Cancel = True
    Me.BulkDensity.SetFocus
    Exit Sub
End If
If Me.Moisture >= Val(Me.Product.Column(9)) And Me.Moisture <= Val(Me.Product.Column(10)) Then
    MsgBox "Moisture is out of range. Valid range is " & Me.Product.Column(9) & " to " & Me.Product.Column(10)
    Cancel = True
    Me.Moisture.SetFocus
    Exit Sub
End If
If Me.SG >= Val(Me.Product.Column(11)) And Me.SG <= Val(Me.Product.Column(12)) Then
    MsgBox "Specific Gravity is out of range. Valid range is " & Me.Product.Column(11) & " to " & Me.Product.Column(12)
    Cancel = True
    Me.SG.SetFocus
    Exit Sub
End Sub

This is probably sloppy. How can I clean it up?

The last function I need the code to be able to perform is to allow the out of spec values to be entered into the table is a "Disapprove" Yes/No box is checked "yes"