How to loop an append query

[Adam Tippelt]

Situation:

When a user 'confirms' that they want to save the information they've inputted, I need to append the database records from a temporary table to a different table.

Problem:

The data spans across 3 related tables - scripts, tests and data. One script can have many tests, and one test can have many data entries. When the records are appended, they're also assigned new primary keys, which means the relationships needed to be re-made.

In another instance I've successfully implemented this on a one by one basis, where only one record has been appended per click of the save/confirm button. What I've done is an append query for the main table and an append query for the sub table. Then I've done a DLookup with suitable criteria to find the new primary key of the main record that was just appended, and use an update query on the sub record to fill in the foreign key, thus re-creating the relationship.

However what I don't know is how I can do this when more than one main record is appended at the same time. Obviously an append query does more than one record at a time, but the problem lies in 'capturing' the new primary key for that record so it can be used to update the foreign key for the sub table records.

Does anyone have any ideas of how I could achieve what I'm after? I was wondering if there was a way of creating a loop function that appended one record, sorted it out, and then moved onto the next. All records in the temporary table belonging to the current user will be moved, therefore would it be possible to have a function to do something like:

- A DMin function that finds the first record for that user.
- Append Query to move that record.
- VBA to capture the new primary key (possibly in an invisible textbox)
- Append Query to move the sub-records.
- Update Query to update the relationship between the main record and sub-records.
- Delete Query to delete the record from the temp table.
- Loop back round for any other records present in the temp table.

Is this possible? Or perhaps a better method that doesn't seem like such a hack? Or perhaps am I finally going crazy?

Any suggestions would be much appreciated!

Thanks.

Adam.

[hype261]

Adam,

I have done this before using a loop in VBA. Access has a query that will return you the Primary Key of the last transaction that was completed by the user. The syntax is

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
 

So psuedo code for the loop would be

While some criteria
   Append main record
   get main record ID
   Append Child Records
 
Wend
 

[Adam Tippelt]

Mmm that doesn't really offer any help on what I've already got. It needs to capture the ID number on every iteration of the loop, not just one ID before it.

Now that I've had some sleep and my brain is working again, I've had another go at coding in what I'm after - however I've come up against an error I don't understand:


Run-time error '2465':

Microsoft Office Access can't find the field 'AddedBy' referred to in your expression.


It's erroring on the SQL append query I wrote, yet I can't understand why - the field exists both in the table it's in now, and the table it's being appended to.

Any ideas why it's now producing that error?

[Adam Tippelt]

Mmm figured that one out, onto the next.

I'll post the code if I get it working...and yes...I do realise that I've spent most of this thread talking to myself... :)

[Adam Tippelt]

For anyone that might be interested, the solution I came up with was:

Private Sub Save_Click()
Dim db As dao.Database
Dim rs As dao.Recordset
Dim currentTS As Integer
Dim currentRef As Integer
 
                Set db = CurrentDb
                Set rs = db.OpenRecordset("TestsForScripts_temp")
 
                'Finds the new Script_ID of the latest script that the user appended to the ScriptLibrary table.
                Me.Script_temp = DMax("Script_ID", "ScriptLibrary", "[ScriptName]='" & Me.ScriptName & "'" & " AND " & _
                                                   "[Client]='" & Me.Client & "'" & " AND " & "[CreatedBy]='" & Me.CreatedBy & "'" & _
                                                   " AND " & "[LastEditedBy]='" & Me.LastEditedBy & "'")
                'Loops through each test in the script, appends the test and any data requirements, and recreates the relationship between script to test and test to data.
                Do While Not rs.EOF
                    currentTS = DMin("TS_ID", "TestsForScripts_temp", "[Script_ID]=" & Me.Script_ID) 'Capture the TS_ID of the first found test still in the temp script.
                    currentRef = DLookup("Ref_ID", "TestsForScripts_temp", "[Script_ID]=" & Me.Script_ID & " AND " & "[TS_ID]=" & currentTS) 'Captures the Ref_ID to use later to find the record in the new table.
 
                    'Appends the test to the TestsForScripts table.
                    strSQL = "INSERT INTO TestsForScripts ( Ref_ID, PageTitle, PageAddress, Client, [Field/Element], TestFor, ExpectedResult, TypeOfTest, Comments, DateAdded, AddedBy )" & _
                        "SELECT TestsForScripts_temp.Ref_ID, TestsForScripts_temp.PageTitle, TestsForScripts_temp.PageAddress, TestsForScripts_temp.Client, TestsForScripts_temp.[Field/Element], TestsForScripts_temp.TestFor, TestsForScripts_temp.ExpectedResult, TestsForScripts_temp.TypeOfTest, TestsForScripts_temp.Comments, TestsForScripts_temp.DateAdded, [TestsForScripts_temp].[AddedBy]" & _
                        "FROM [TestsForScripts_temp] " & _
                        "WHERE [TestsForScripts_temp].[TS_ID]=" & currentTS
                    DoCmd.RunSQL strSQL
 
                    'Appends any data requirements for this test to the DataForScripts table.
                    MySQL = "INSERT INTO DataForScripts ( Data_ID, Data_Requirement, Ref_ID, DateAdded, AddedBy)" & _
                        "SELECT DataForScripts_temp.Data_ID, DataForScripts_temp.Data_Requirement, DataForScripts_temp.Ref_ID, DataForScripts_temp.DateAdded, [DataForScripts_temp].[AddedBy]" & _
                        "FROM [DataForScripts_temp] " & _
                        "WHERE [DataForScripts_temp].[TS_ID]=" & currentTS
                    DoCmd.RunSQL MySQL
 
                    'Uses Script_temp to re-create the relationship between the script and the test.
                    DoCmd.OpenQuery "Script_TS_RelationshipQuery"
                    'Finds the new TS_ID of the current test that was appended to the TestsForScripts table.
                    Me.TS_temp = DLookup("TS_ID", "TestsForScripts", "[Script_ID]=" & Me.Script_temp & " AND " & "[Ref_ID]=" & currentRef)
                    DoCmd.OpenQuery "TS_DS_RelationshipUpdateQuery" 'Uses TS_temp to re-create the relationship between the test and any data requirements it has.
 
                    'Delete that test from the temp table now that it's been added to the TestsForScripts table.
                    'Referential integrity means that any data requirements are deleted as well.
                    strSQL = "DELETE * " & _
                        "FROM [TestsForScripts_temp] " & _
                        "WHERE [TestsForScripts_temp].[TS_ID]=" & currentTS
                    DoCmd.RunSQL strSQL
 
                    rs.MoveNext 'This is used to let the system decide if EOF = true, and whether the loop needs to be run again.
                    Loop
 
                'Delete the empty temp script once all the tests have been individually appended.
                strSQL = "DELETE * " & _
                    "FROM [ScriptLibrary_temp] " & _
                    "WHERE [ScriptLibrary_temp].[CreatedBy]='" & Me.CreatedBy & "'"
                DoCmd.RunSQL strSQL
 
                DoCmd.Close 'Close the form and open the main menu that corresponds to the current user's user type.