By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,086 Members | 1,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,086 IT Pros & Developers. It's quick & easy.

How to loop an append query

100+
P: 137
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.
Mar 28 '11 #1

✓ answered by Adam Tippelt

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Save_Click()
  2. Dim db As dao.Database
  3. Dim rs As dao.Recordset
  4. Dim currentTS As Integer
  5. Dim currentRef As Integer
  6.  
  7.                 Set db = CurrentDb
  8.                 Set rs = db.OpenRecordset("TestsForScripts_temp")
  9.  
  10.                 'Finds the new Script_ID of the latest script that the user appended to the ScriptLibrary table.
  11.                 Me.Script_temp = DMax("Script_ID", "ScriptLibrary", "[ScriptName]='" & Me.ScriptName & "'" & " AND " & _
  12.                                                    "[Client]='" & Me.Client & "'" & " AND " & "[CreatedBy]='" & Me.CreatedBy & "'" & _
  13.                                                    " AND " & "[LastEditedBy]='" & Me.LastEditedBy & "'")
  14.                 '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.
  15.                 Do While Not rs.EOF
  16.                     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.
  17.                     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.
  18.  
  19.                     'Appends the test to the TestsForScripts table.
  20.                     strSQL = "INSERT INTO TestsForScripts ( Ref_ID, PageTitle, PageAddress, Client, [Field/Element], TestFor, ExpectedResult, TypeOfTest, Comments, DateAdded, AddedBy )" & _
  21.                         "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]" & _
  22.                         "FROM [TestsForScripts_temp] " & _
  23.                         "WHERE [TestsForScripts_temp].[TS_ID]=" & currentTS
  24.                     DoCmd.RunSQL strSQL
  25.  
  26.                     'Appends any data requirements for this test to the DataForScripts table.
  27.                     MySQL = "INSERT INTO DataForScripts ( Data_ID, Data_Requirement, Ref_ID, DateAdded, AddedBy)" & _
  28.                         "SELECT DataForScripts_temp.Data_ID, DataForScripts_temp.Data_Requirement, DataForScripts_temp.Ref_ID, DataForScripts_temp.DateAdded, [DataForScripts_temp].[AddedBy]" & _
  29.                         "FROM [DataForScripts_temp] " & _
  30.                         "WHERE [DataForScripts_temp].[TS_ID]=" & currentTS
  31.                     DoCmd.RunSQL MySQL
  32.  
  33.                     'Uses Script_temp to re-create the relationship between the script and the test.
  34.                     DoCmd.OpenQuery "Script_TS_RelationshipQuery"
  35.                     'Finds the new TS_ID of the current test that was appended to the TestsForScripts table.
  36.                     Me.TS_temp = DLookup("TS_ID", "TestsForScripts", "[Script_ID]=" & Me.Script_temp & " AND " & "[Ref_ID]=" & currentRef)
  37.                     DoCmd.OpenQuery "TS_DS_RelationshipUpdateQuery" 'Uses TS_temp to re-create the relationship between the test and any data requirements it has.
  38.  
  39.                     'Delete that test from the temp table now that it's been added to the TestsForScripts table.
  40.                     'Referential integrity means that any data requirements are deleted as well.
  41.                     strSQL = "DELETE * " & _
  42.                         "FROM [TestsForScripts_temp] " & _
  43.                         "WHERE [TestsForScripts_temp].[TS_ID]=" & currentTS
  44.                     DoCmd.RunSQL strSQL
  45.  
  46.                     rs.MoveNext 'This is used to let the system decide if EOF = true, and whether the loop needs to be run again.
  47.                     Loop
  48.  
  49.                 'Delete the empty temp script once all the tests have been individually appended.
  50.                 strSQL = "DELETE * " & _
  51.                     "FROM [ScriptLibrary_temp] " & _
  52.                     "WHERE [ScriptLibrary_temp].[CreatedBy]='" & Me.CreatedBy & "'"
  53.                 DoCmd.RunSQL strSQL
  54.  
  55.                 DoCmd.Close 'Close the form and open the main menu that corresponds to the current user's user type.

Share this Question
Share on Google+
4 Replies


100+
P: 207
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
Expand|Select|Wrap|Line Numbers
  1. Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
  2.  
So psuedo code for the loop would be
Expand|Select|Wrap|Line Numbers
  1. While some criteria
  2.    Append main record
  3.    get main record ID
  4.    Append Child Records
  5.  
  6. Wend
  7.  
Mar 28 '11 #2

100+
P: 137
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?
Mar 29 '11 #3

100+
P: 137
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... :)
Mar 29 '11 #4

100+
P: 137
For anyone that might be interested, the solution I came up with was:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Save_Click()
  2. Dim db As dao.Database
  3. Dim rs As dao.Recordset
  4. Dim currentTS As Integer
  5. Dim currentRef As Integer
  6.  
  7.                 Set db = CurrentDb
  8.                 Set rs = db.OpenRecordset("TestsForScripts_temp")
  9.  
  10.                 'Finds the new Script_ID of the latest script that the user appended to the ScriptLibrary table.
  11.                 Me.Script_temp = DMax("Script_ID", "ScriptLibrary", "[ScriptName]='" & Me.ScriptName & "'" & " AND " & _
  12.                                                    "[Client]='" & Me.Client & "'" & " AND " & "[CreatedBy]='" & Me.CreatedBy & "'" & _
  13.                                                    " AND " & "[LastEditedBy]='" & Me.LastEditedBy & "'")
  14.                 '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.
  15.                 Do While Not rs.EOF
  16.                     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.
  17.                     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.
  18.  
  19.                     'Appends the test to the TestsForScripts table.
  20.                     strSQL = "INSERT INTO TestsForScripts ( Ref_ID, PageTitle, PageAddress, Client, [Field/Element], TestFor, ExpectedResult, TypeOfTest, Comments, DateAdded, AddedBy )" & _
  21.                         "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]" & _
  22.                         "FROM [TestsForScripts_temp] " & _
  23.                         "WHERE [TestsForScripts_temp].[TS_ID]=" & currentTS
  24.                     DoCmd.RunSQL strSQL
  25.  
  26.                     'Appends any data requirements for this test to the DataForScripts table.
  27.                     MySQL = "INSERT INTO DataForScripts ( Data_ID, Data_Requirement, Ref_ID, DateAdded, AddedBy)" & _
  28.                         "SELECT DataForScripts_temp.Data_ID, DataForScripts_temp.Data_Requirement, DataForScripts_temp.Ref_ID, DataForScripts_temp.DateAdded, [DataForScripts_temp].[AddedBy]" & _
  29.                         "FROM [DataForScripts_temp] " & _
  30.                         "WHERE [DataForScripts_temp].[TS_ID]=" & currentTS
  31.                     DoCmd.RunSQL MySQL
  32.  
  33.                     'Uses Script_temp to re-create the relationship between the script and the test.
  34.                     DoCmd.OpenQuery "Script_TS_RelationshipQuery"
  35.                     'Finds the new TS_ID of the current test that was appended to the TestsForScripts table.
  36.                     Me.TS_temp = DLookup("TS_ID", "TestsForScripts", "[Script_ID]=" & Me.Script_temp & " AND " & "[Ref_ID]=" & currentRef)
  37.                     DoCmd.OpenQuery "TS_DS_RelationshipUpdateQuery" 'Uses TS_temp to re-create the relationship between the test and any data requirements it has.
  38.  
  39.                     'Delete that test from the temp table now that it's been added to the TestsForScripts table.
  40.                     'Referential integrity means that any data requirements are deleted as well.
  41.                     strSQL = "DELETE * " & _
  42.                         "FROM [TestsForScripts_temp] " & _
  43.                         "WHERE [TestsForScripts_temp].[TS_ID]=" & currentTS
  44.                     DoCmd.RunSQL strSQL
  45.  
  46.                     rs.MoveNext 'This is used to let the system decide if EOF = true, and whether the loop needs to be run again.
  47.                     Loop
  48.  
  49.                 'Delete the empty temp script once all the tests have been individually appended.
  50.                 strSQL = "DELETE * " & _
  51.                     "FROM [ScriptLibrary_temp] " & _
  52.                     "WHERE [ScriptLibrary_temp].[CreatedBy]='" & Me.CreatedBy & "'"
  53.                 DoCmd.RunSQL strSQL
  54.  
  55.                 DoCmd.Close 'Close the form and open the main menu that corresponds to the current user's user type.
Mar 30 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.