Append to detail table using SQL

[bulrush]

A2003 on WinXP.

I mentioned I'm not to good at SQL, but I perused an SQL summary of commands and was unable to figure this out myself.

I have a Master table and a Detail table. While looping through the Master table where the key field is "GID", I want to update the Detail table, where the key is autonumbered. The fields on the Detail table I want to update are: GID (numeric) and JOB (text field).

The UPDATE command seems to update an existing record and I didn't see an APPEND command.

Thank you.

 

[vbaInet]

Maybe consider using recordset?

By the way, how do you loop through your master table?

 

[bulrush]

I loop through my master table using a DAO ecordset.

I'm doing this but Access is not saving my JobNum to the Detail table.
Right after my Master recordset does an Update, I do an INSERT INTO on the detail recordset to add the new record.

The point is to add a JobNum to the Detail table linked to the current Master record by the GID field (same field name on both Master and Detail tables.)

Myset.Update ' Update Master recordset. We should have GID from Myset!GID at this point.
        
' Now add job number to Detail recordset. 
        If Len(Trim(txtJobNum)) > 0 Then
            crit = "INSERT INTO MyDetail (GID, PSIJobNum) VALUES (" & Myset!GID & ", '"
            crit = crit & txtJobNum & "')"
            crit = crit & ";"
            DoCmd.SetWarnings False
            DoCmd.RunSQL crit
            DoCmd.SetWarnings True
            DoEvents
        End If

 

[vbaInet]

Here's an example of updating your PSIJobNum field for when GID is 1. Autonumber fields cannot be amended.

CurrentDb.Execute "UPDATE [MyDetail] SET [PSIJobNum] = " & txtJobNum.value & "  WHERE [GID] = 1;"

 

[bulrush]

I don't think I was clear. I don't want to update a record in MyDetail table, I want to append a record to MyDetail and set GID equal to the Master table's GID, and set MyDetail!JobNum to txtJobNum.

 

[vbaInet]

Are we talking two tables here? Are they linked? If they are linked, when you perform an INSERT you don't have to do anything with the ID of the child table (if they are properly linked), it will set that value automatically.


CurrentDb.Execute "INSERT INTO [MyDetail] ([PSIJobNum]) VALUES (" & txtJobNum.value & ");"