Need help with error message

[vkumar]

I have a data entry form and when I enter a value for a foreign key in the form I want the error to show immediately (you can't add or change a record because a related record is required in table X -(Foreign key value must exist in table X as Primary key value)) not when saving the record after entering all the fields. Thank you for your help.

[June7]

You would have to use VBA code and build your own message. One way is in the BeforeUpdate event of the control, check for the value in table X.

A combobox will restrict users to existing data and then can use the NotInList event to handle new value.

[vkumar]

Thank you for your reply. I am not familiar with VB code. How do I compare the fields in VB. When a enter a job number in the forms (data entety form), the Job table should have the Job number exist already, otherwise error message should display. The code is comparing the job number entered in the form to the Job number in the job table.

[June7]

The easiest method might be a combobox. Then the NotInList event could handle an entry not in the table. Many examples for the NotInList event. Google it. Here is one http://www.blueclaw-db.com/access_no...ed_example.htm

[vkumar]

Again thank you for your reply. I can't use a combo box. Job table has job numbers and it can change(dynamic), in the invoice form when the user enters invoice, user must enter the job number and it should be already in the job table, if not error message should come up. I am getting the error message at the end when I save the form (FK -PK referential integrity), but I want the error message as soon as the user enters the job number. There are more fields and it is a waste of time for the user to enter the rest of the fields and then getting the message.

[June7]

I still don't understand why could not use combobox.

Alternative is the BeforeUpdate event of textbox with code that searches table for entered value. A DLookup function could do that, something like:

If IsNull(DLookup("JobNumber", "Jobs", "JobNumber='" & Me.textboxname & "'")) Then
MsgBox "Job Number not in table."
Cancel = True
End If

Review
http://office.microsoft.com/en-us/ac...010341717.aspx
http://www.blueclaw-db.com/access_ev...foreupdate.htm

[vkumar]

Hi,
I tried and it gave me a syntax error. Here is what I have:
(My table name is Job and the textbox name that I enter the value is named as Job Number, in the Job table the field it is comparing is Job Number.)
Private Sub Job_Number_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("Job Number", "Jobs", "Job Number='" & Me.Job Number & "'")) Then
MsgBox "Job Number not in table."
Cancel = True
End If
End Sub
Thank you for your help.

[June7]

Whenever spaces or special characters or punctuation (underscore is exception) are in names or a reserved word, must enclose in []. That's why it is a good idea to avoid those name structures.

If IsNull(DLookup("[Job Number]", "Jobs", "[Job Number]='" & Me.[Job Number] & "'"))

If JobNumber is number datatype then remove the apostrophe delimiters. Date datatype uses # delimiter.

[vkumar]

Thank you, I should have known.
But I got another runtime error, it says "data type mismatch in criteria expression" when I enter the job number.

[June7]

Did you read the last line of my previous post?

[vkumar]

It works. Sorry I didn't read your last line. As soon as I saw the square brackets I got excited and hurried to change it. You saved me today. THANK YOU again for all your help and patience.