Currently an existing form that Users input --- gets stored into an existing table. I would like for that data entry form to be stored into 2 tables at a time. Can someone plz assist with the logic/code needed in Access VBA? :-) Thanks ahead of time. Your help is much appreciated.

~Danny

__________________
~Danny

Although you call it a 'data entry form', do you mean that the form is bound and the 'Data Entry' property is set True? If so, you may want to use the entry form's Before Update event to run a line of DAO code:

Dim db As Database
Set db = CurrentDb
db.Execute ("Insert into tbl2 (ID, ProjectID, JobName, StartDate) Values(" & Me.txtID & ", " & _
  Me.txtProjectID & ", '" & Me.txtJobName & "', #" & Me.txtStartDate & "#)")
If db.RecordsAffected = 0 Then
    Cancel = True
    Msgbox "Please check the field values as the record is missing required values or values are invalid"
End if

If the form isn't a data entry form, you can check the form's .NewRecord property to see whether the query should be an "Insert Into" append query or an "Update tbl1 Set" update query using the primary key in a where clause.

An alternative is to use the form's after update event to run an append query from the first table to the second table selecting on a where clause that is limited to the primary key value read from the form. The after update shouldn't require you to check the records affected since the record would not have been written unless the fields are valid. The downside is that if an error occurs between the insert and the append fires, such as a loss of database connection, then the tables will more likely go out of synch.

The syntax of the append query selecting on a single key value is simpler than the sample text given above which shows the delimiter usage required for numeric, text and date type values when inserting form control values.



[email protected]
Edmonton AB Canada