Appending records to two tables with a one to many relationship

[PatientsFirst]

Hi! I'm hoping that you can assist me with this problem.

I've developed two tables: CredEvents and CredEventsSites that have a one to many relationship linked on the field CredEventID. CredEvents has a field that tracks the type of event entered in a field called CredEventType. I've made forms for my users that view a list of CredEvents that have happened or are scheduled to happen in the future. A click on a button gives them details of the event - including what sites (hospitals) are a part of this event.

When a user wishes to add a new Credential Event, they click a button, a form opens where they select a Credential type, target date and the involved sites. Once they've made their selections another button is clicked that runs two queries:

One query appends the new event to the CredEvents table, the second query I hoped would just append the new site records to CredEventsSites. What is happening instead is as follows:

• The new Credential Event is added to CredEvents
• The site records are added to every event for the selected physician with the same CredEventType - creating multiple site records for some Credential Events

I've made several attempts to remedy the problem - and I'm stuck. My current attempt at a solution was to use the Second() function in the hopes that it would only add site records linked to a record in CredEvents that had been created in the last 90 seconds. Unfortunately, this brilliant plan didn't work.

I'm unable to load my DB - most of it is connected to a SQL backend and contains confidential information. However, I can share the SQL of the two queries that are my current headaches:

Append to CredEvents (seems to work fine)
INSERT INTO CredEvents ( Med_Prof_Record_No, CredEventType, CredDate, [Order], [Report Heading], LastUpdated, LastUpdatedBy )
SELECT Med_Prof.Med_Prof_Record_No, CredEventType.CredEventType, Month([Forms].[CredEventsAddRecord].[TargetDate]) & "/15/" & Year([Forms].[CredEventsAddRecord].[TargetDate]) AS CredDate, CredEventType.Order, CredEventType.[Report Heading], Now() AS Expr2, User_Facility_Restrictions.UserAccessID AS Expr3
FROM User_Facility_Restrictions, Med_Prof, CredEventType
WHERE (((Med_Prof.Med_Prof_Record_No)=[Forms]![CredEventsAddRecord]![Med_Prof_Record_No]) AND ((CredEventType.CredEventType)=[Forms]![CredEventsAddRecord]![Combo2]) AND ((User_Facility_Restrictions.UserAccessID)=CurrentUser()));

Append to CredEventsSites (my nemisis)
INSERT INTO CredEventsSites ( CredEventsID, [Primary], FacCode, Department_name, [Section], LastUpdated, LastUpdatedBy )
SELECT DISTINCT CredEvents.CredEventsID, CredEventSiteHold.Primary, CredEventSiteHold.FacCode, CredEventSiteHold.Department, CredEventSiteHold.Section, Date() AS Expr1, User_Facility_Restrictions.UserAccessID AS Expr3
FROM User_Facility_Restrictions, CredEventSiteHold, CredEvents INNER JOIN Med_Prof_Facilities ON CredEvents.Med_Prof_Record_No = Med_Prof_Facilities.Med_Prof_Record_No
WHERE (((User_Facility_Restrictions.UserAccessID)=CurrentUser()) AND ((CredEvents.CredEventType)=[Forms]![CredEventsAddRecord]![Combo2]) AND ((Med_Prof_Facilities.Med_Prof_Record_No)=[Forms]![CredEventsAddRecord]![Med_Prof_Record_no]) AND ((CredEvents.LastUpdated)>Second([Forms]![CredEventsAddRecord]![AddTime])-90));

I'm looking forward to hearing your ideas!

Thank you!

 

[PatientsFirst]

Wow! Thank you for the quick reply Pat!

The two tables have an inner join. Where there are relationships in the two queries they are also inner joins. (Some fields have been dropped from unlinked tables to id the user, etc)

I have never used DAO or ADO (I have heard of them). I have extremely limited VBA skills. I've done things to generate emails, used If, then, else and to print multiple reports with one click.

I'll confess I'm a bit intimidated by the thought of trying this. (But I was intimidated by If then else the first time I tried it too!)

Do you have any guidance on where to learn more about this before I give this a try?

Edited to ask: what do you mean by FK value?
Edited again to mention that I'm working in Access 2000.