Recordset - looping thru to append to another table

[Billp]

Greetings,

I cannot seem to get the loop to actually work as in take each record as it
steps down and append that record to another table. With each record appended
I need to set a tickbox.
I have a subform which I am taking the viewable records as a clone set.

All I get for example is if there are 3 records all with a different ID, the
first record appends OK but does so to the number of records showing in the
original subform.

e.g

A0123 Fred
A0145 Diana
A0456 Bruce

A0123 appends to an existing record A01123 but 3 times
The other two are ignored

If I have 5 records the first one appends 5 times the remaining 4 do nothing

Any ideas greatly thanked and readily implemented on both questions.

Dim strsql As String
Dim strOtherfields As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

'find the first unchecked record
Set rst = Forms!frmProjectNotes_Input.RecordsetClone
If rst.RecordCount > 0 Then
'rst.MoveFirst 'first record of the subform and only the sub form
'remember that the subform only shows records that are not checked
'End If

Do While Not rst.EOF

'Dim fld As DAO.Field
'For Each fld In rst.Fields

strOtherfields = ",Action_By,To_Do_date" _
& ",Project_Notes,Status"

strsql = "INSERT INTO [tblsubProjectNotes] " _
& "(Works_Number" & strOtherfields & ") " _
& "SELECT '" & Me!Works_Number & "' As NewWorks_Number" _
& strOtherfields & " FROM tblProjectNotes_Input " _
& "WHERE Works_Number='" & Me!Works_Number & "';"


DBEngine(0)(0).Execute strsql, dbFailOnError
'remember to check all of these records as having been sent
'Next
rst.MoveNext
Loop
Else
'do nothing
End If
rst.Close.

 

[Alex Dybenko]

Hi
in strsql you have to use rst!Works_Number instead of Me!Works_Number


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Paolo]

Hi Billp,

with your insert into statement you append always the record shown on your
form i.e.
Me!Works_Number
Actually you must use the works_number of the current record in rst to
append all your records so you must correct your SQL statetement in this way

strsql = "INSERT INTO [tblsubProjectNotes] " _
& "(Works_Number" & strOtherfields & ") " _
& "SELECT '" & Me!Works_Number & "' As NewWorks_Number" _
& strOtherfields & " FROM tblProjectNotes_Input " _
& "WHERE Works_Number='" & rst!Works_Number & "';"

HTH Paolo

Greetings,

I cannot seem to get the loop to actually work as in take each record as it
steps down and append that record to another table. With each record appended
I need to set a tickbox.
I have a subform which I am taking the viewable records as a clone set.

All I get for example is if there are 3 records all with a different ID, the
first record appends OK but does so to the number of records showing in the
original subform.

e.g

A0123 Fred
A0145 Diana
A0456 Bruce

A0123 appends to an existing record A01123 but 3 times
The other two are ignored

If I have 5 records the first one appends 5 times the remaining 4 do nothing

Any ideas greatly thanked and readily implemented on both questions.

Dim strsql As String
Dim strOtherfields As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

'find the first unchecked record
Set rst = Forms!frmProjectNotes_Input.RecordsetClone
If rst.RecordCount > 0 Then
'rst.MoveFirst 'first record of the subform and only the sub form
'remember that the subform only shows records that are not checked
'End If

Do While Not rst.EOF

'Dim fld As DAO.Field
'For Each fld In rst.Fields

strOtherfields = ",Action_By,To_Do_date" _
& ",Project_Notes,Status"

strsql = "INSERT INTO [tblsubProjectNotes] " _
& "(Works_Number" & strOtherfields & ") " _
& "SELECT '" & Me!Works_Number & "' As NewWorks_Number" _
& strOtherfields & " FROM tblProjectNotes_Input " _
& "WHERE Works_Number='" & Me!Works_Number & "';"


DBEngine(0)(0).Execute strsql, dbFailOnError
'remember to check all of these records as having been sent
'Next
rst.MoveNext
Loop
Else
'do nothing
End If
rst.Close.