Form entry - check to make sure entry is valid

[Phantek]

I am new to Access in general, so my apologies in advance.

I have created a database with table showing details about all of our locations (about 60 in the list). Another table lists individual sales from each location, and there is a relationship between the location number in the two tables.

Now I have created a form to easily enter new sales with a command button to enter the new record. That is all working fine, but what I would like is for the form to first verify that the location number that has been entered is in fact a valid location number before it creates the new record: if not, it gives an error message.

How could I go about doing that? Thanks!

 

[Darren Bartrup]

Hi Phantek,

I take it your LocationTable has a unique Primary Key and your SalesTable has it's own unique Primary Key and a Foreign Key which links the two tables - LocationID is unique in the LocationTable and appears within each record in SalesTable where a Sale is allocated to that Location.

When you make the relationship between these two fields tick the 'Enforce Referential Integrity' box which will stop a record being entered into the SalesTable if the LocationID does not already exist in the LocationTable.
In the Relationships window there will be a join between the two fields with a 1 on the LocationTable end, and the Infinity symbol on the SalesTable end.

Edit: It is called the Infinity symbol isn't it? The 8 on its side.

 

[Phantek]

Nice and simple, thanks!

That stops the information from being recorded, which is great... but I am trying to build a macro that will copy the details into a word form at the same time that it saves the details in the Access database. Enforcing referential integrity prevents the record from being added, but not from continuing with the macro to fill out the Word form.

Any idea how a macro can first check to see if the data is correct before proceeding?

 

[Darren Bartrup]

Create a simple query which checks the table for the record. Execute this query at the start of the macro and if it doesn't return any records then return exit the macro.

 

[boblarson]

Why not make it even easier. Just use a combo box to select the location number which can pull the valid locations from the locations table (I assume you have one) and then set the combo box's LIMIT TO LIST to yes and then they can only select a valid location number.

 

[Phantek]

Both ideas are great - I will be exploring both of them. Thanks!