This is virtually the same problem I was having before..

I want a message box to appear if a quantity entered is lower than a specified value.

I am using.......

If textbox = ????? Then
If textbox2 < 10 Then
MsgBox(????????[,vbOKOnly])
End If
End If

Someone please tell me what I'm doing wrong as it is driving me mad.

Where have you put the code?

I have put it on the Forms Properties on Current!!

OK your code should be fine, make sure you have "" marks

If textbox = "?????" Then
If textbox2 < 10 Then
MsgBox(????????[,vbOKOnly])
End If
End If

It should work but I don't get the message box unless I exit the record and then go back into it!!

So more help please!!

I had a similar problem, but couldn't really find a suitable solution.

I ended up running a macro from the on change event using GoTo, it's a bit messy but you could set it to move one record back and then one record forward.

Trouble with that is what if it's the first record, I'm not sure what would happen.

If your creating a new record when you want to test the condition then you could set it to GoTo First and then GoTo Last.

Very messy I know but it should work, if anyone else has any suggestion I'd love to hear!

The reason the code doesn't work(I confidently think) is because the value has not yet been assigned to the table it is bound to therefore, the actual field does not have a saved value so there is nothing to compare. Capiche?

To solve this put the above code in the BeforeUpdate event of the textbox. When the user exits the field the beforeupdate event is used to validate any data in the field BEFORE the table field is UPDATED. That way you can manage any undesirable values being saved.


I think the code should be:

If Me.textbox = "?????" Then
If Val(Me.textbox2) < 10 Then
MsgBox "Whatever message you like",0,"YourAppName"
End If
End If

Use the Val() function to convert a textbox value that is not set to a number type to a number.

I hope that helps.

Ian

[This message has been edited by Fornatian (edited 11-08-2001).]

Because the textbox is bound to a table, use

Me.Requery

before your first If statement.

This should refresh the data in the table underlying the form and negate the need to exit the current record.

In the after update event of text2 put Form_Current

If the requery method is used will Access be constatly running the procedure?

Will this affect performace at all?

Even put the code in the After Update event for the textbox you are entering data into.

I meant the code, not the form current event call

[This message has been edited by Fizzio (edited 11-09-2001).]

I often wonder why?
You already have the code in the on current event; Form_Current in the after update event of relevant text boxes will Call the procedure, you do not need to requery or use multiple copies of the same code.

Rich, didn't you suggest to put Form_Current in the after update event.

Emma have you got it to work yet?

[This message has been edited by crosmill (edited 11-09-2001).]

The code is in the forms current event. Typing Form_Current in the after update event will trigger the code and display the message box of course if you want to trap the value and prevent the entry you have to use the before update event.
eg.Private Sub YourField_AfterUpdate()
Form_Current
End Sub
Sub Form_Current()
If Me.textbox1 = "?????" Then
If Me.textbox2 < 10 Then
MsgBox "Whatever message you like",0,"YourAppName"
End If
End If

End Sub