Ivor:
You can do this using DAO.
In the VBA editor, open the Tools menu, select References, and, in the
References dialog, select Microsoft DAO 3.6. Close the References dialog.
In the database window, create a new Update query to update the Append field
to True (ie put True in the "Update To" Row) if the Append field is False
(ie put False in the "Criteria" Row).
You can now execute the Append and Update queries in the Form's Open event.
Here is some sample code for doing this, which assumes the Append query is
named "qryAppend" and the Update query is named "qryUpdate":
Private Sub Form_Open(Cancel As Integer)
Const QRY_APPEND As String = "qryAppend"
Const QRY_UPDATE As String = "qryUpdate"
Dim objDB As DAO.Database
Dim objQDF As DAO.QueryDef
Dim lngRecsAppended As Long
' Instantiate Database object:
Set objDB = CurrentDb()
' Point to the APPEND query:
Set objQDF = objDB.QueryDefs(QRY_APPEND)
' Execute the APPEND query:
objQDF.Execute
' See if the query appended any records:
lngRecsAppended = objQDF.RecordsAffected
' If no records were appended, finish:
If lngRecsAppended = 0 Then GoTo Finish
' Poitn to the UPDATE query:
Set objQDF = objDB.QueryDefs(QRY_UPDATE)
' Execute the query:
objQDF.Execute
' Update this form's recordset:
Me.Requery
Finish:
' Optional: Show message to user:
MsgBox "Records Appended = " & CStr(lngRecsAppended) _
& vbNewLine & vbNewLine _
& "The above number of records were appended " _
& "to the target table and updated in the source " _
& "table.", vbOKOnly + vbInformation, _
"Information"
Set objQDF = Nothing
Set objDB = Nothing
End Sub
Incidentally, on a small point, it would seem natural to rename the field
"Appended".
Regards
Geoff
