Checking Table for already created record

[puforee]

In my data base I track training dates for individuals. We want to keep a history from year to year of the training for each individual. So, I created an archive table...the table has about 15 fields...three of which make the record unique...Training_Year (text field), BEMS (unique number field), and model (text field). When each training item is completed for each individual for that year my code runs an update query to update that training dates in the Archive table.

When I roll the Training_Year to a new year, I want to create new records for each individual containing the new Training_Year.

How do I check the Archive table to see if the Training_Year is already there? It is a possibility the users can change the Training_Year information and I don't want erroneous data to be stored for the incorrect year.

Thanks,

 

[dhookom]

Can you provide more information about your table structures? Why are you archiving anything? Which field identifies "each individual"? Why are you using a text field for Training_Year?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

OK...I have a main table that contains training programs and due dates for each item...for each individual...there are 12 different training programs and each instructor must do all of them each year...they each have unique due dates...etc.

As each training program is complete I store the completed date in the archived table for that year....individualID...and model. Each individual can be related to one or more model. This way I can pull the training for each individual over the years of employment.

Also, after each training program is complete. The due dates, kept in the main table are updated to the next training cycle. So the main table cannot hold the past training. The main table, that contains about 45 fields is NOT related to the Archive table. The Archive table is populated on the fly so to speak.

So, when a new year comes along I want to update the archive table to show all the users and there completion dates along with the key fields. IndividualId's, Model, and year. But, I don't want to overwrite something that may already be there. Users control selecting the new year and the don't want to loose data by mistake.

Year is stored by text so it does not contain month and day. Yes I can format it so it does not SHOW anything but the year....but I want it to only store the year...I suppose I could use a number field.

Finally, on the form, when the user enters a new year...I want to check the archive table to ensure that year, ID, and model are not already there. If they are not, I will populate the Archive table with all the individuals new data...which will be modified as the year progresses and they complete each training program.

So, simply, how can I use code...possibly DLookup to check three fields in the Archive table based on three fields on my form?

 

[dhookom]

Are you suggesting your "12 different training programs" are 12 different fields? If so, are you stuck with this structure or would you consider changing to a more normalized table structure?

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Listen to Duane!

What happens when the scope of training changes and there are MORE or less courses??

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[puforee]

Thanks to both of you...I am open to any suggestions. And will consider most anything.

 

[puforee]

I still need to know how to check a table...with VB...to see if a specific record is there.

Please...

 

[dhookom]

puforee,
Go to this page and search for "Training".

You can use DLookup() or DCount() in code to determine if a record exists.

Duane
Hook'D on Access
MS Access MVP