Validation Rule

[Christopher]

I am new to Access. I need to validate that a number I input into a form field is less than field in Table A corresponding to a related field in both. And that input field has to be <= a field in Table b according to the same related field, and there could be multiple records in the table B where there is only one corresponding record in table A.
I have tried different things in the validation rule spot but I can't get anything to work.

[maximus]

can explain how data are related with a practical example. name your tables and fields and clearly explain the relationship. that will help in providing a solution.

[Christopher]

I have a form call Transactions based on a table called Transactions. On this form there is a field called Beginning Weight, the value entered must be <= its starting weight. This can be found in a table called Foam by the name Initial Weight. Both tables and form are related by a field called Bar Code. tblFoam-tblTransaction;1-many.

[sesproul]

is the value Initial weight always set prior to the form being opened. Or is there the possibility of this value being set after this textbox has value.

I often use a function that check values on the exit of a textbox or in your example the BeforeUpdate event.

There may be a simpler solution, but this may work.


ALSO as a general rule NEVER put spaces in column names.

'---Require ADODB reference.
Private Sub BeginningWeight_BeforeUpdate()
    IF NOT CheckValue ([BeginningWeight], [FOAMID_For_The_Foam_Type]) then
      msgbox "Invalid Beginning Weight"
      Cancel=True      ' caused the changes to not be saved
   END IF
End Sub


Public Function CheckValue (byval v_dWeight as double,  & _
                                      Byval v_lFoamID as long) as Boolean
Dim sSql As String
Dim oRS As ADODB.Recordset

    sSql = "SELECT Initial_Weight FROM FOAM WHERE FoamID = "  & v_lFoamID 
    Set oRS = GetRS(sSql)
    If v_dWeight <= oRS(0) then CheckValue = TRUE


End Function



Public Function GetRS(ByVal sSql As String, Optional v_sParameter As String) As ADODB.Recordset
Dim oconn As New ADODB.Connection
      Set oconn = CurrentProject.Connection
      Set GetRS = oconn.Execute(sSql)
End Function

[maximus]

I have tried to replicate the conditions and here is what i have.

Two tables:
Products
Transactions

Now Each product in the product table has a beginning weight and the weight in the transaction form has to be <= to this weight

so i have used a simple code in the before update even of the weight field of my transaction form.

Private Sub Weight_BeforeUpdate(Cancel As Integer)
Dim intBeginingWeight As Integer
intBeginingWeight = IIf(IsNull(DLookup("[BeginingWeight]", "Products", "[BarCode]=" & Me.barCode)), 0, DLookup("[BeginingWeight]", "Products", "[BarCode]=" & Me.barCode))
If Me.Weight > intBeginingWeight Then
MsgBox "Weight typed is not equal to begining weight.Begining Weight for the Item is = " & intBeginingWeight

Cancel = True
End If

End Sub

To use database select a product and type the wight and see the validation take place.

I am attaching the mdb see if this helps you. sesproul has given a wonderful suggestion and his remarks regarding spaces in column names is absolutely very important to follow

[sesproul]

Maximus,

That definately seems more direct! What is the DLookup command does it directly pull the value in a quick query?


steve

[maximus]

I have used Dlookup to pull up the value of the beginning weight directly from the table Products. After I get that then the work is almost done, a simple if statement does the rest. I have used Cancel to ensure that the unvalidated is not saved into the table when the user closes the form when validation is not met.

[maximus]

please mark the thread solved if the suggestions offered to you solves your problems.

[sesproul]

MAXIMUS, Thanks - I'll definitely use that tip. I'm a former vb6-SQL SERVER programmer, so I'm not too familiar with many of the direct table commands in ACCESS. (hence my reliance on the ADO workarounds)


Steve

[Christopher]

I couldn't get your code to work. I have attached a more accurate example.
In the transactions from I am using this material multiple times so the first use will need the dlookup in Products table and subsequent transactions will need to read the last occurrence.

[maximus]

Your example was very helpful and this is what I have done.....

included this code on the BeforeUpDate Event of Beginweight:

Private Sub BeginWeight_BeforeUpdate(Cancel As Integer)
Dim intWeight1 As Integer
Dim intEndWeight As Integer
Dim intBeginWeight As Integer
Dim intMaterialUsed As Integer
intWeight1 = IIf(IsNull(DLookup("[Weight1]", "Product", "[BarCode]=" & Me.Barcode)), 0, DLookup("[Weight1]", "Product", "[BarCode]=" & Me.Barcode))
intEndWeight = IIf(IsNull(DSum("[EndWeight]", "Transactions", "[Barcode]=" & Me.Barcode & " And ID<" & Me.ID)), 0, DSum("[EndWeight]", "Transactions", "[Barcode]=" & Me.Barcode & " And ID<" & Me.ID))
intBeginWeight = IIf(IsNull(DSum("[BeginWeight]", "Transactions", "[Barcode]=" & Me.Barcode & " And ID<" & Me.ID)), 0, DSum("[BeginWeight]", "Transactions", "[Barcode]=" & Me.Barcode & " And ID<" & Me.ID))
intMaterialUsed = intBeginWeight - intEndWeight
Select Case intMaterialUsed
Case Is = 0
If Me.BeginWeight > intWeight1 Then
MsgBox "Please Check Begining Weight which should be to a maximum of = " & intWeight1
Cancel = True
End If
Case Is > 0
If Me.BeginWeight > intWeight1 - intMaterialUsed Then
MsgBox "Please Check Begining Weight which should be to a maximum of = " & intWeight1 - intMaterialUsed
Cancel = True
End If
End Select
End Sub

Scope of this code is to ensure that the last occurence of weight is maintained.

Example

Trans1: Begin Weight 100 End Weight 75

Then next Trans the begining has to 75 and not more than that.

If this solves your problem mark this thread solved.

refer to attached mdb.

[Christopher]

[IMG]file:///D:/DOCUME%7E1/ccastor/LOCALS%7E1/Temp/moz-screenshot-1.png[/IMG]I am clearly not a very good programmer. I get this error and don't know what it means.

And the numbers (weights) that will be input are not necessarily integers.

[maximus]

Hi,
Chris its time that you ziped your database and upload it. make sure it is in Access 2000 mdb format. let me have a look. Have you seen the mdb that I have attached id it working fine.

[Christopher]

I did run your code, and it worked for the most part. With the material that we are using and the scales we are using, the beginning weight for the first transaction may be less than the initial weight. (b/c of the material boiling pt being very low) And if that occurs then the code you have will work for the first transaction but the subsequent trans. will not account for the lost weight b/c it is tied directly to the initial weight. I mentioned earlier that these entries will not be integers they will be decimals. I zipped the appropriate portions of the DB.

[Christopher]

After I got all the typos out of the code I realized that the code was under a false heading. I went back throughout the database and changed all the table column headings to be w/o spaces. I changed the integers to singles, and altered the equation.

Thanks for you help.