I was stuck with this problem for a number of months trying to figure out how to do this. I searched forum after forum and posted numerous questions and never really got a straight answer for what I wanted.
Before I start though, I's like to thank johnwm and lebisol for their suggestions on this issue as they put me on the right track. I would be unfair not to recognise their efforts.
I started of this successful method by first following the steps at the Dr Dev page located below:
AS the page above states, Create a records set on the page where you have the data that you want to transfer across to the new table. Then create the form and the fields for each of the records, the same as you would when you create and update form, placing the record data into the form fields. Make sure that this is in one row of a table as we will be making a repeating region for these records.
When you have all of that setup, place the repeating region over the row where you have the records displayed so that you will see all of the records. I haven't tried setting up the table for paging, but hey, try it and see. The worst that could happen is that you will have to add records from each page before you proceed to the next.
OK, Now go into the code area of the page and set up a variable to identify each record and place just below the repeat region code:
<% While ((Repeat1__numRows <>0) AND (NOT Recordset1.EOF)) %> <% intRecID =(Recordset1.Fields.Item("RecID").Value) ' Store the current RecordID in a variable %>
Now, place this variable in front of each form name:
AS you can see, I placed the variable and showed it in the first cell of each row. You don't have to do this, but You will need to place the hidden field in each row (highlighted in green)
Once this is done, place another hidden field at the bottom of your form. This is explained in Dr Devs page and it holds the records for later on when we create our array. outside of the repeat region.
You can show this field during the development of your page for now to confirm that you code is working. Once you are happy with that is works, just hide it again.
OK, That's it for the form for now. Now we add a chunk of code from the Dr Dev site into the header section of our page. Here it is:
You can read about this code in the Dr Dev page I linked to above.
Just make sure that if you changed the variable names for you page, you need to match the variable name in this code as well.
This code will identify which record you have modified and only add those records. In my situation, I was going to modify every record by thye use of a select field, so I place the "Onchange" event in that field like so:
NOW, the last chunk of code I had to modify from Dr Devs page as his was to update records while I wanted to add records to a new table. The principle is the same though.
I'll place the entire chunk here, you should be able to see how it all works;
<% If Request("button")<>"" Then intRecID = Replace(Request("hidRecID"), "*", "") arrRecIDs = Split(intRecID, ", ") For i = 0 to Ubound(arrRecIDs) SN=Replace(Request("Surname"&arrRecIDs(i)),"'","''") FN = Replace(Request("Firstname"&arrRecIDs(i)),"'","''") EM = Replace(Request("Email"&arrRecIDs(i)),"'","''") PH = Replace(Request("Ph" & arrRecIDs(i)), "'", "''") MOB = Replace(Request("Mob" &arrRecIDs(i)),"'","''") GP = Replace(Request("GroupID" & arrRecIDs(i)),"'","''")
MbrIns.CommandType = 1 MbrIns.CommandTimeout = 0 MbrIns.Prepared = true MbrIns. Execute() Next Response.Redirect("Index.asp") End IF %>
So, at the top is an if then statement to activate the code when the user hits the submit button the next section of code strips the asterix from the number and generates the array. You an then make variables for as many fields as you want to transfer across to the new table. The last 4 or 5 in my example were actually hidden fields storing Session data so I can sort the records later. After that is the SQL Code. This is where I became unstuck in a big way until I got some help, but this code works nicely. Just remember, you need to place the order of the values in the same order as the fields.
After that we redirected the user and closed off the process.
And that is it! all from the one page. I hope that this helps you. If anybody reading this (meaning those millions of people that have far more experience than I) see fundamental flaws in this process, or can improve on it in any way. Feel free.
I wish to stress however, that when you give your input, write it in such a manner that people with only a little knowledge can understand and use you code. No "skeleton" code. This is an FAQ and as such it should allow people to copy code and paste it into their projects in order to learn how to do these tasks.