Appending records to two tables with a one to many relationship (1 Viewer)

[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!

 

[Pat Hartman]

two problems:
1. You need to specify how the tables are joined. You have an inner join specified on one relationship in the second query but the join type needs to be specified for all relationships.
2. Because the joins are indeterminate, I can't tell what field is the foreign key in the second insert that will link those records to their parent. I'm going to guess that it is CredEventsID. Normally, for the second query you would actually specify the FK value since there is usually no way to get it by joining to the parent table.

Because of the problem of obtaining the newly generated Autonumber, I would not run an insert query for the parent record. I would do the insert with DAO (or ADO if you prefer) which will allow me to obtain the autonumber when the record is inserted. I can then pass it to the second query which I would do with an append query since it is appending multiple rows.

To get the autonumber:
rs.AddNew
rs!fld1 = something
rs!fld2 = somethingelse
...
SaveNewKey = rs!yourprimarykey
rs.Update

Then in the second query, pass in the value of SAVENewKey as the foreign key.

 

[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.

 

[Pat Hartman]

There are no joins in the queries except the one I mentioned. Open the queries in QBE and draw the join lines to connect all the tables on their relationship fields. Access will normally do this for you by default if you actually created relationships using the relationship window.

The following is code from one of my applications to use as a model. The only change I made to it was to add SaveAutonumber so you'd know where that code goes.

You'll need to change all the column names and then run your second append query after the rs.Close statement. Your procedure name will also be different. You might want to put this in the click event of a button. My code actually runs an unrelated append query first. I left it because it shows how you might pass in parameters to your second append query.

Private Sub Form_AfterInsert()

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim td As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim SaveAutonumber as Long
    
   On Error GoTo Proc_Err
    
'copy default settings
    Set db = CurrentDb()
    Set qd = db.QueryDefs!qCopyAllLists
        qd.Parameters![EnterToAuditParmsID] = Me.AuditParmsID
        qd.Parameters![EnterFromAuditParmsID] = 1
    qd.Execute dbSeeChanges
    
    Set td = db.TableDefs("tblAuditLog")
    Set rs = td.OpenRecordset
        rs.AddNew
        rs!AuditParmsID = Me.AuditParmsID
        rs!AuditStartDate = Date
        rs!AuditName = Me.AuditName
        rs!CoName = Me.CoName
        rs!CoAbbr = Me.CoAbbr
        rs!UpdatedBy = Forms!frmLogin!txtUserID

        SaveAutonumber = rs!yourpkname

        rs.Update        
    rs.Close 

Proc_Exit:
   Exit Sub

FK = foreign key. A foreign key is used in one table to point to the primary key of another table. For example - CustID is the PK of the customer table and will be used in the Order table as a FK to connect the order to a customer. What makes a column a FK is creating a relationship with it in the relationships dialog on the Database tools Ribbon/Tab/menu

this example is from A2010 but it doesn't use any new feature. The only thing "odd" is the dbSeeChanges argument on the .Execute method that runs the query. This is necessary because my tables are actually SQL Server rather than Jet/ACE. You can leave it or remove it. It works with Jet/ACE although it is not required. SQL Server needs it whenever you are appending rows in a table that has an autonumber primary key to remind it to tell Access what the autonumbers are.

This is code in a form event and all the "Me." references are to controls on the form that contains this code.