validation rule via vba

[Ratib Hussaini]

I have inventory query with following fields

Product ID , Purchased, Sold,On Order, Available, LocationA, LocationB, LocationC

where locationA, B ,C represent the quantity available in each location.
Available represents the total quantity available in 3 locations

In order details subform, i have the following fields
Product ID, Location ID, QTY, Price, Total, status

Now the problem is , I dont want the user to give a qty more than the qty available in the location selected.
I tried to set validation rule via VBA in after update property of Location ID,,, but it shows an error that i have used visual basic code.

Can you give your idea please, what should i use here, thanks in advanced.

 

[JHB]

Place validation rule in the BeforeUpdate event for both Location ID and QTY, then here you can cancel the update if the value is higher as allowed.

 

[Ratib Hussaini]

As you said, I placed validation rule in the before update event for both location id and qty. code is below.

Private Sub Location_ID_BeforeUpdate(Cancel As Integer)
    Dim LocationID As Long
    Dim ProductID As Long
    Dim qty As Long
    
    ProductID = Me.Product_ID
    LocationID = Me.Location_ID
    If LocationID = 1 Then
        qty = DLookup("QTY_Avail_A", "Inventory", "[ID]=" & [ProductID])
        Me.Quantity.ValidationRule = "<qty"
      
    End If
    
    If LocationID = 2 Then
        qty = DLookup("QTY_Avail_B", "Inventory", "[ID]=" & [ProductID])
        Me.Quantity.ValidationRule = "<qty"
      
    End If
    
    If LocationID = 3 Then
        qty = DLookup("QTY_Avail_C", "Inventory", "[ID]=" & [ProductID])
        Me.Quantity.ValidationRule = "<qty"
      
    End If
End Sub

the same code in the before update event of the QTY. after running the form and given higher value than the availabe qty to the QTY field I got the following error.

The object doesn't contain the Automation object 'qty.'

You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations.

check the component's documentation for information on the properties and methods it makes available for Automation operations.

 

[JHB]

Try:

Private Sub Location_Id_BeforeUpdate(Cancel As Integer)
  Dim LocationId As Long
  Dim ProductID As Long
  Dim QTY As Long
  
  ProductID = Me.Product_ID
  LocationId = Me.Location_Id
  If LocationId = 1 Then
    QTY = DLookup("QTY_Avail_A", "Inventory", "[ID]=" & [ProductID])
  ElseIf LocationId = 2 Then
    QTY = DLookup("QTY_Avail_B", "Inventory", "[ID]=" & [ProductID])
  ElseIf LocationId = 3 Then
    QTY = DLookup("QTY_Avail_C", "Inventory", "[ID]=" & [ProductID])
  End If
  If Me.Quantity > QTY Then
    MsgBox ("Quantity to high")
    Cancel = True
  End If
End Sub

 

[Ratib Hussaini]

Thanks Mr. JHB, and sorry for late reply, as I was not well,,, Your code works fantastic !!!!!!!!!!!!!!!!!!!!!

 

[JHB]

You're welcome.

 

[Pat Hartman]

Since two fields are involved, the validation code needs to go in the Form's BeforeUpdate event rather than the individual field events.

 

[Ratib Hussaini]

Sorry, Mr. Hartman I put the code in forms before updates event but it wont work, by doing this after completing each record it shows the message, "qty high", in either way weather it is low or high.

And by putting code in before update event of location and qty.....
Mr. JHB and Mr. Hartman's, one thing I realized now that, after updating qty, if the qty is high as per our code, it show the msg that qty is high but after clicking OK button of the msg, it show another msg that property not found with a ok button. but it works fine,, I dont know whats wrong there. Any idea?

 

[Pat Hartman]

Please post the actual code.

 

[Ratib Hussaini]

Thanks Mr. Hartman for the reply. I made little bit changes to the code according to my need, but it shows the same msg "property not found". here is the code

Dim LocationID As Long
Dim productid As Long
Dim qtyA As Long
Dim qtyB As Long
Dim qtyC As Long
Dim qtyAvailable As Long
Dim response As Integer

productid = Me.Product_ID
LocationID = Me.Location_ID
qtyA = Inventory.GetQtyAvailableA(productid)
qtyB = Inventory.GetQtyAvailableB(productid)
qtyC = Inventory.GetQtyAvailableC(productid)
qtyAvailable = Inventory.GetQtyAvailable(productid)

If LocationID = 2 Then

    If Me.Quantity > qtyB Then
        If qtyA <> 0 Or qtyC <> 0 Then
            MsgBox ("Please use other locations product")
            Cancel = True
        End If
    End If
ElseIf LocationID = 3 Then

    If Me.Quantity > qtyC Then
        If qtyA <> 0 Or qtyB <> 0 Then
            MsgBox ("Please use other locations product")
            Cancel = True
        End If
    End If
ElseIf LocationID = 1 Then

    If Me.Quantity > qtyA Then
        If qtyB <> 0 Or qtyC <> 0 Then
            MsgBox ("Please use other locations product")
            Cancel = True
        End If
    End If

    End If

As per my code I just wanted to be sure that the user shouldn't be allowed to use a qty more than the qty available at the selected location if and only if there is qty available in other locations.
I used this code in before update event of location and qty, it works fine but it shows that "property not found" message.
And as you said i removed the code from both the events and used it in before update event of the form. But it wont work according to my need, e.g for a product xyz I have 10 units at each location means totally 30 units at my company. If I use any location with qty lesser than 10, it still shows "Please use other locations product", but yes it wont show that error "property not found".
And one more thing if you allow me to ask you another question as well, in my company we have customers that most of them dont do whole the payment, they pay some amount at the time of purchasing and pay rest of the amount at another day, there is no limit when they must pay and no interest as well. what is your idea, should I add payment details at my order details form, or I should use totally different form for that. thanks

 

[Pat Hartman]

What line of code is generating the error?

Keep a separate table for payments.

 

[Ratib Hussaini]

thanks. I dont know, whenever i put the higher value for each location, it shows my msg, but jst after that it says property not found jst need to press ok and it works fine. According to my code it works fine, just as an information it says property not found.

But in before update event of the form that error wasn't there, for higher and lower both it was showing the message "please use another location" but not that error.

 

[Pat Hartman]

Step through the code line by line. Look at the variable values as you go.

 

[isladogs]

Hi noodle

You've also cross posted this here
https://www.access-programmers.co.uk/forums/showthread.php?t=173800
Doing that wastes everyone's time as we & you may not see answers in the other thread.
If you're not sure which forum to use, put a link like that above in both posts

I've already done a brief response in the other post

As yours is a new question it would be better as a new thread.
Please do so, then delete both posts.
After you do that I'll delete mine & put an edited response in the new thread!