Adding a row from a select query with inner join...

[kriswork]

Hi,

I have a query:

SELECT Change_Details.*, Change_Resources.* FROM Change_Resources INNER JOIN Change_Details ON Change_Resources.Change_Number = Change_Details.Change_Number

In the relationships for the db there is a 1(Change_Details) to many (Change_Resources) relationship

If I switch to the datasheet view of this query, I can add the 1st record OK, making sure I fill in the Change_Details.Change_Number only and not the Change_Resources.Change_Number which otherwise would give me an error.

When I go to add a 2nd record with the same number I need to do it the other way round (type the number in the Change_Resources.Change_Number field) otherwise it says I'm violating the primary key. I understand the error as Change_Details.Change_Number has to be unique, but as it is a one to many relationship I thought Access would work it out.

The crux of the problem is the query is used in code and has a "where" on the end which I use to check to see if this change_details/change_resources combo already exist - If not then add the record. The problem is that if the change_details record already exists, but the change_resources doesn't then when updating the record it fails as it is a duplicate.

SQL_Command = "SELECT Change_Resources.*, Change_Details.* " & _
"FROM Change_Resources INNER JOIN Change_Details ON Change_Resources.Change_Number = Change_Details.Change_Number " & _
"WHERE Change_Details.Change_Type = ""RFC"" and Change_Resources.Change_Number = """ & longChange_Number & _
""" and Change_Resources.Sheet_Name = """ & strSheetName & """ and Change_Resources.Role_Type_ID = " & bytRole_Type_ID
Set rstChange_Resources = CurrentDb.OpenRecordset(SQL_Command)
With rstChange_Resources
    If .EOF Then
        .AddNew
        !Change_Type = strChange_Type
        ![Change_Details.Change_Number] = longChange_Number
        !Change_Description = strChange_Folder
        !Sheet_Name = strSheetName
        !Role_Type_ID = bytRole_Type_ID
    Else
        .Edit
    End If
 
    !Man_Days = singMan_Days
    !Resource_Date = dateFul_Date
    .Update
    .Close
End With

I can get round this by running another query 1st to check if the parent exists and then decide whether to update Change_Details.Change_Number or instead Change_Resources.Change_Number, but this seems like a fudge and would slow things down.

Hopefully I've explained the problem OK.

Thanks for any help.

Kristian

 

[kriswork]

I've found a solution to the problem which is still a fudge, but I'm kind of happy with it as its a nice fudge with a shiny wrapper:

SQL_Command = "SELECT Change_Resources.*, Change_Details.* " & _
"FROM Change_Resources INNER JOIN Change_Details ON Change_Resources.Change_Number = Change_Details.Change_Number " & _
"WHERE Change_Details.Change_Type = ""RFC"" and Change_Resources.Change_Number = """ & longChange_Number & """"
Set rstChange_Resources = CurrentDb.OpenRecordset(SQL_Command)
With rstChange_Resources
    If .EOF Then
        .AddNew
        !Change_Type = strChange_Type
        ![Change_Details.Change_Number] = longChange_Number
        !Change_Description = strChange_Folder
        !Sheet_Name = strSheetName
        !Role_Type_ID = bytRole_Type_ID
    Else
        .FindFirst "Change_Resources.Sheet_Name = """ & strSheetName & """ And Change_Resources.Role_Type_ID = " & bytRole_Type_ID & ""
        If .NoMatch Then
            .AddNew
            ![Change_Resources.Change_Number] = longChange_Number
            !Sheet_Name = strSheetName
            !Role_Type_ID = bytRole_Type_ID
        Else
            .Edit
        End If
    End If
    
    !Man_Days = singMan_Days
    !Resource_Date = dateFul_Date
    .Update
    .Close
End With

What I've done is loosen the 1st query to see if the Change_Details record exists - if it doesn't then go ahead and add the record as normal. If it does then do a findfirst for the Change_Resources record - If that exists then do an edit. Now for the scenario that was causing the problem where the Change_Details record exists, but the Change_Resources record doesn't - I now update the Change_Resources.Change_Number field and all works fine.
Interestingly my idea failed the 1st time in this problem scenario because I updated the Change_Type field when I didn't need to and as soon as you modify a field from the Change_Details table it creates a new record which will then stop you updating Change_Resources.Change_Number field.

I worked this out by trying to add a record to the datasheet view of the same query until I worked out what was happening.

If anyone can come up with a fudge free solution I'd be very interested to see it.

Thanks

Kristian

 

[Pat Hartman]

Although Access lets you create a query that includes both the one side and the many side and lets you add to both tables at once, it is a little tricky.
Let me start with - normally, you would never do this. You would add the record to the 1-side table first. Then add a record to the many-side table and if the 1-side table has an autonumber PK, you can't really get around this because you need the value of the autonumber to use as the FK and you don't know it until the parent record has been added. You can get around this in a query that you are typing into because you can see the autonumber as it populates and so you can type it into the FK field on the child record. You can't do this with a query you are running in VBA.

So - change the code so that it adds the parent record if necessary and then adds the child record with the correct FK value.

FYI - SQL strings in code contribute to database bloat and take more time to execute. It is far better to create querydefs and execute those.

 

[kriswork]

Hi Pat,

Thanks for the info.
What I found was if I needed to add records to both the parent and child table, then I'd only ever need to put in the PK in the parent and MS Access put in the FK in the child (as long as I was doing it through a record set created using a join). When I get some spare time I really need to read up on joins.

Kris