0

I have an Access database with two tables and a form.

The form is linked to one of the tables for data entry purposes.

I have:

  • LIVEDB which is a table of live customers' info.
  • LIVEDBINPUT form which inputs information into LIVEDB
  • CANCELLATIONDB which is just a table - when someone cancels, they are simple cut from LIVEDB and pasted into CANCELLATIONDB.

I have all sorts of rules that prevent duplicates in the LIVEDBINPUT form, but I have recently encountered a problem: the LIVEDBINPUT form is not preventing duplicates from the CANCELLATIONDB table, only the LIVEDB table.

I want the form to prevent a telephone number being entered, if the telephone number already appears in either the LIVEDB table OR the CANCELLATIONDB table.

If I could configure a message box to appear that states something like "this telephone number already exists in LIVEDB or CANCELLATIONDB", and then the option to take the user to that record or delete it, that would be ideal.

I have found loads of information about how to achieve this in multiple columns of one table, but I can't work out or find a way to prevent the duplicates from both tables i.e. for the form to prevent creating a duplicate that already exists in either of the two tables.

0

Construct a UNION query which yields all records stored in both your LIVEDB table and your CANCELLATIONDB table, then test for existing records in this query prior to storing new records.

Assuming that your CANCELLATIONDB table has an identical structure to your LIVEDB table (since you are moving records from one to the other), the UNION query could be as simple as:

SELECT * FROM LIVEDB UNION SELECT * FROM CANCELLATIONDB
  • Hi yes thanks but I am trying to remove the step of "testing existing records prior to storing new records" as we enter around 100 new records daily this will take some time to query prior to entry each time – Joey86 Apr 9 '18 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.