Forms and Validating Data

[ebbo]

Hello, I am just exploring the world of access (completely new never done it before EVER!), vb, forms and queries and i am totally in over my head. What i want to do is actually pretty simple in my head I just cant figure out how to do it in access, i hope some1 can help.

I have two tables:

TABLE: COMPANIES
ID
Account_no
Company
1Address Type
1Address1
1Address2
1Address3
1Town
1County
1Postcode
Company_Notes

TABLE: CANCELLATIONS
Cancellation_ID
DX_NO
Company_Name
Cancellation_Date
Product
Diarised
Date_Diarised
Cancellation_Complete
Date_Complete

I have created a tabular form based on the cancellations table, basically what I would like to do is on the DX_NO field if somebody enters a DX Number I want to check that the DX_NO is valid by checking it exists in the COMPANIES TABLE. DX_NO = ACCOUNT_NO on the Companies Table.

I have looked at the validation rule property for the DX_NO field on my form, but have no idea how to even begin using the expression builder.

All i want to do is display a message "Company does not exist" if the value does not exist in the Companies Table.

I am hoping some1 can step me through!

CheerZ

P.S using access 2003

 

[statsman]

The easiest way I can think of to do this would be to enter DX_NO by way of a combo or list box which is called from Tables:Companies.

If the company doesn't exist in Tables:Companies, it can't be entered.

 

[ebbo]

you are quite correct my friend, this is exactly what I have done (took me a while to figure out about the relationship view!)

Have not looked into it further yet, but 1 thing i noticed its not very "user friendly" I would like a custom error message as soon as I exit the DX_NO text box field.


At the moment I only get a generic ms access error message when I exit the the entire row and attempt to click into another row.

Can any1 help with this?