Units Received Cant Be more than Units Ordered (1 Viewer)

[Rob_Jones101]

Hi

I have two fields, Units Ordered and Units Received. Units Received cannot be more than Units Ordered. I have code for it but it dosent work properly. I have tried the greater than sign both ways but neither work.

Private Sub Units_Recived_AfterUpdate()

If Me.Units_Recived <= Me.Units_Ordered Then
MsgBox "Units Received Cannot Be More Than Units Ordered", vbOKOnly
End If


End Sub

I have tried using validation <=[Units_Ordered] but this is inconsistent.

Any help would be appreciated.

 

[PeterF]

In the AfterUpdate event the record is already saved, it should be in the BeforeUpdate event and you should cancel the update.

 

[Rob_Jones101]

Hi Peter

Is the code correct as it doesn't seem to do anything. I've moved it to the Before Update event and it still doesn't do anything.

I've also moved the greater than sign around and it doesn't allow me put anything in the field.

Private Sub Units_Recived_BeforeUpdate(Cancel As Integer)

If Me.Units_Recived >= Me.Units_Ordered Then
MsgBox "Units Received Cannot Be More Than Units Ordered", vbOKOnly
End If

End Sub

 

[PeterF]

I made some changes to your code

Private Sub Units_Recived_BeforeUpdate(Cancel As Integer)
        If NZ(Me.Units_Recived , 0) > NZ(Me.Units_Ordered, 0) Then 'only greater, Received and ordered can be the same
            MsgBox "Units Received Cannot Be More Than Units Ordered", vbOKOnly
            Cancel = True 'Cancel the update otherwise the changes are saved 
            Me.Undo    'clear all changes not needed if you want to stay in edit mode
        End If
End Sub

 

[missinglinq]

I tend to do this kind of thing, when two or more Controls are involved, in the Form_BeforeUpdate event...doing it in a Control's BeforeUpdate event is dependent on the user entering data in a certain order (i.e. Units_Ordered entered before Units_Recived, in this example) and in my experience, end users have a bad habit of not entering data in the same order the developer expects! Using the Form_BeforeUpdate event eliminates this problem.

You might also want to correct your spelling of the Control/Field you've named Units_Recived, as you're apt to spell it correctly, elsewhere in you app, as Units_Received, and get unexpected results.

Linq ;0)>

 

[Rob_Jones101]

Now I am not able to enter any number without getting the msgbox. Not sure why?

 

[Micron]

Now I am not able to enter any number without getting the msgbox. Not sure why?

Post the new code.... kinda hard to see it from here

 

[missinglinq]

Micron is right...we have to see the exact code (Sub headers included) that you're using!

I was also wondering, given your aim with this code, why you wanted to pop a warning if the Units_Recived were = Units_Ordered?

Another late thought...are the Fields connected to Units_Recived and Units_Ordered defined as Numbers...or, perhaps, as Text?

Linq ;0)>

 

[jdraw]

Rob,

Please copy your code directly from Access and post it. or provide a copy of the database with only a few records to highlight the issue and instructions for showing the issue.
Good luck.

 

[Rob_Jones101]

Hi. Sorry. I have been out of the office for a few days. Here is the code that I have.

Private Sub Units_Recived_BeforeUpdate(Cancel As Integer)

If Nz(Me.Units_Recived, 0) > Nz(Me.Units_Ordered, 0) Then
MsgBox "Units Received Cannot Be More Than Units Ordered", vbOKOnly
Cancel = True
Me.Undo
End If

End Sub

It is exactly the same but still wont function. It wont allow me to enter any value into it now.

 

[Minty]

What does it do when you enter a value? Ping up the message box?
Try the following just to make sure we are dealing with the correct things;

   Debug.Print "Rcvd:" &  Me.Units_Recived
   Debig.Print "Order:" &  Me.Units_Ordered
  
   If Nz(Me.Units_Recived, 0) > Nz(Me.Units_Ordered, 0) Then
        MsgBox "Units Received Cannot Be More Than Units Ordered", vbOKOnly
        Cancel = True
        Me.Undo
    End If

Then open the immediate window in the code editor (ctrl + g) to see what values it thinks you have.

 

[Rob_Jones101]

Units ordered is 10 and i tried to enter 9 and got the error message.

In the code editor these are the values.

Rcvd:9
Order:10
Rcvd:9
Order:10
Rcvd:9
Order:10

 

[Minty]

Units ordered is 10 and I tried to enter 9 and got the error message.

Error message or the message box?
Can you screenshot it, I wonder if you left an incorrect validation rule on the control?

 

[Rob_Jones101]

There is no validation on any of the text boxes.

 

[Minty]

Ok, can you clarify the error, Is it an error or the message box?

 

[Rob_Jones101]

It is the message box.

 

[Micron]

Units ordered is 10 and i tried to enter 9 and got the error message

You are trying to edit records using bound controls and BeforeUpdate event on controls? Then the value being used in the comparison isn't what you see, it is the Old Value, yes?
Or is this happening with new records?

You said a field contained 10 and you tried to enter 9, but where?

Why are you not doing this in form before update as suggested?

 

[Minty]

I'm with Micron, I suspect the value hasn't been saved in the order qty so it's maybe not comparing the correct values, although the debug suggests otherwise.

If it's not that I think we'll need a stripped-down copy of the DB to try and fathom it out directly.

 

[Rob_Jones101]

It is happening with old and new records. The units received is only one text box. I don't have a goods in form it is just on one form. I have tried it with an unbound text box and a bound text box and I still get the same error message. The error message is the message box.

 

[jdraw]

Rob,

Can you post a copy of your database (C&R, then zip) with only enough records to show the issue and instructions of how to repeat the issue?