Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Checking Table for already created record

Checking Table for already created record

Checking Table for already created record

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.


RE: Checking Table for already created record

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?

Hook'D on Access
MS Access MVP

RE: Checking Table for already created record

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?

RE: Checking Table for already created record

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?

Hook'D on Access
MS Access MVP

RE: Checking Table for already created record

Listen to Duane!

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


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Checking Table for already created record

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

RE: Checking Table for already created record

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


RE: Checking Table for already created record

Go to this page and search for "Training".

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

Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close