How do I add new information but ensure at most 1 record exits

[General Ledger]

Deear All,

I am a self taught (and not very well) user of Access 2007.

I have a table that must contian unique records based on some key fields (item number and purchase order number). The table includes an AutoNumber field that adds a sequential record number. This can tell me the sequence in which new records were added. I can also determine which record is older or newer.

I need to manually enter new records from time to time. At the end, I need to be sure the table only has the late record entered for each combination of item number and purchase order number.

I am thinking there are a few approaches:

1. Create an entry form that somehow checks as I enter new data, whether a record already exits in the table with a match on the item number and purchase order number, deletes the old record, and appends the new record.

2. Create an entry form that somehow checks whether a record already exits in the table with a match on the item number and purchase order number, and updates some fields in the existing record.

3. Create an entry form that appends all the new records I enter. Then run a Find Duplicate Records query that identifies all duplicate records based on item number and purchase order number. Somehow delete all the records except the one with the highest AutoNumber (the last one entered) as grouped by item number and purchase order number.

Any ideas or full blown solutions are gratefully appreciated!!

Please be detailed and explicit as I am at best a rookie.

Thank you,

General Ledger (on the Jersey Shore)

 

[Scuderia]

Hi GL

Create a primary Key field in your table to contain the unique identifier of each record. A concatenation of item number and PO no. This will ensure the table only ever contains unique items

Click on the tablename and choose 'create' and 'form' from the menu. You now have a form in which you can edit existing Purchase order details or add new ones.