Validation Rule using data between two different tables

[Melissa]

Help?! I've got a basic invoicing database setup with a basic Products table
and Orders table. When we recieve orders, we are provided a Quantity (which
is a field value in my Orders table). When an order is placed, the Quantity
must be a mutliple of the corresponding products "Pack Size" (which is a
field value in the Products table). In other words, the customer can't order
partial packs. So, I've tried a variation of validation rules with the
emphasis on the following expression: [Quantity]Mod[PackSize]=0. I have
since read Allen Browne's article which states that you can't have validation
rules that incorporate fields from other tables "(well, not as easily,
anyway)". So I'm thinking it's possible. Anyone know how?

I really appreciate it!!!
Thanks.

 

[Ken Sheridan]

You can validate the entries at form level. In the BeforeUpdate event
procedure of the Qty control put code along these lines:

Private Sub qty_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
Dim strMessage As String
Dim strCriteria As String
Dim intPackSize As Integer
Dim intDifference As Integer

Set ctrl = Me.ActiveControl

' first make sure a product has been selected
If IsNull(Me.productID) Then
strMessage = "Please select a product first."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
ctrl.Undo
Else
' get pack size for selected product
strCriteria = "ProductID = " & Me.productID
intPackSize = DLookup("PackSize", "Products", strCriteria)
' if pack size not a multiple of quantity then
' cancel update and undo control
intDifference = ctrl Mod intPackSize
If intDifference <> 0 Then
strMessage = "Pack size for product is " & intPackSize & "." & _
vbNewLine & vbNewLine & _
"Please enter a quantity which is " & _
"an exact multiple of this, e.g. " & _
ctrl - intDifference & " or " & _
(ctrl - intDifference) + intPackSize & "."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
ctrl.Undo
End If
End If

End Sub


Ken Sheridan
Stafford, England

 

[Melissa]

Thanks. I ran into a problem with this code. The following line of code
produces a data type mismatch error. I've checked all my datatypes and they
are correct (I think). Packsize and Quantity are set as Integers and
ItemNumber is set to be String.
intPackSize = DLookup("PackSize"], "Products", strCriteria)

I've tried everything I can think of. I'm not well versed with VBA code, so
I'm sure I'm missing something really obvious here.

 

[Ken Sheridan]

I assume ItemNumber is the key of Products. In which case you need to wrap
the value in quotes:

strCriteria = "ItemNumber = """ & Me. ItemNumber & """"

Also you have an unnecessary closing bracket in:

intPackSize = DLookup("PackSize"], "Products", strCriteria)

It should be:

intPackSize = DLookup("PackSize", "Products", strCriteria)

Ken Sheridan
Stafford, England

 

[Melissa]

Thank you, thank you, thank you so much!!! I've been struggling with this
for quite sometime now!!! Thanks again!!!!
--
Thanks.

I assume ItemNumber is the key of Products. In which case you need to wrap
the value in quotes:

strCriteria = "ItemNumber = """ & Me. ItemNumber & """"

Also you have an unnecessary closing bracket in:

intPackSize = DLookup("PackSize"], "Products", strCriteria)

It should be:

intPackSize = DLookup("PackSize", "Products", strCriteria)

Ken Sheridan
Stafford, England

Thanks. I ran into a problem with this code. The following line of code
produces a data type mismatch error. I've checked all my datatypes and they
are correct (I think). Packsize and Quantity are set as Integers and
ItemNumber is set to be String.
intPackSize = DLookup("PackSize"], "Products", strCriteria)

I've tried everything I can think of. I'm not well versed with VBA code, so
I'm sure I'm missing something really obvious here.