B
Billp
Hi,
I have A report form where you can add notes for a project and at the end
upload those notes to another table.
I wish to take the current, shown records in a subform and append these to
another table.
As each row is appended a checkbox becomes true in the original subform.
This tick box is then part of the criteria in a query to show only those
records which have not been sent.
I have the following
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
Dim fld As DAO.Field
For Each fld In rst.Fields
strOtherfields = ",CustomerID,Action_By,To_Do_date" _
& ",Project_Notes,Status"
strsql = "INSERT INTO [tblProjectNotes] " _
& "(Works_Number" & strOtherfields & ") " _
& "SELECT '" & Me!CustomerID & "' As NewCustomerID" _
& 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
End If
How can I add a routine that checks a yesno box called Sent_Input for each
record appended????
As a record is appended a check box is ticked in the original subform.
Tahnk you
Regards
Bill
I have A report form where you can add notes for a project and at the end
upload those notes to another table.
I wish to take the current, shown records in a subform and append these to
another table.
As each row is appended a checkbox becomes true in the original subform.
This tick box is then part of the criteria in a query to show only those
records which have not been sent.
I have the following
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
Dim fld As DAO.Field
For Each fld In rst.Fields
strOtherfields = ",CustomerID,Action_By,To_Do_date" _
& ",Project_Notes,Status"
strsql = "INSERT INTO [tblProjectNotes] " _
& "(Works_Number" & strOtherfields & ") " _
& "SELECT '" & Me!CustomerID & "' As NewCustomerID" _
& 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
End If
How can I add a routine that checks a yesno box called Sent_Input for each
record appended????
As a record is appended a check box is ticked in the original subform.
Tahnk you
Regards
Bill