Recordset - looping thru to append to another table


B

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.
 
Ad

Advertisements

Ad

Advertisements

P

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

Billp said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top