Add new record to related table and show data on form

[bymarce]

I have three tables Data, Conditions, and Comments. All are related by a 1
to 1 relationship of the ID field which is in each table. I have a form in
datasheet view, Table All Data, that contains records from all the tables.
The ID field in the data table is an autonumber field. I want a new record
automatically added to the Comments and Conditions tables with the ID fields
matching Data.ID when Data.ID is filled in on the form. I've attemped to do
this by I setting up two append querries. The append queries are working but
the IDs from Comments and Conditions don't fill in on the form. How can I
get those values to show up on the Table All Data form? The code I'm using
to execute the queries is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.CmtsRID) Then
DBEngine(0)(0).Execute "DataComments", dbFailOnError
End If
If IsNull(Me.CdnsRID) Then
DBEngine(0)(0).Execute "DataConditions", dbFailOnError
End If
End Sub

I based this on code I found in a post by Allen Browne.

 

[Allen Browne]

Form_BeforeUpdate is too early. The record has not been saved at this time.

If you only want to do this when a new record has been added, use
Form_AfterInsert. If you want to do this after every edit, use
Form_AfterUpdate.

You will then need to requery the subforms before the new record shows up,
e.g.:
Me.[Sub1].form.Requery
using the name of your subform control in place of Sub1.

To test whether the action query did anything, after the Execute you could
test:
Debug.Print dbEngine(0)(0).RecordsAffected

 

[bymarce]

Thank You!! It worked. Your posts and website have been very helpful.
Marcie

Form_BeforeUpdate is too early. The record has not been saved at this time.

If you only want to do this when a new record has been added, use
Form_AfterInsert. If you want to do this after every edit, use
Form_AfterUpdate.

You will then need to requery the subforms before the new record shows up,
e.g.:
Me.[Sub1].form.Requery
using the name of your subform control in place of Sub1.

To test whether the action query did anything, after the Execute you could
test:
Debug.Print dbEngine(0)(0).RecordsAffected

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have three tables Data, Conditions, and Comments. All are related by a 1
to 1 relationship of the ID field which is in each table. I have a form
in
datasheet view, Table All Data, that contains records from all the tables.
The ID field in the data table is an autonumber field. I want a new
record
automatically added to the Comments and Conditions tables with the ID
fields
matching Data.ID when Data.ID is filled in on the form. I've attemped to
do
this by I setting up two append querries. The append queries are working
but
the IDs from Comments and Conditions don't fill in on the form. How can I
get those values to show up on the Table All Data form? The code I'm
using
to execute the queries is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.CmtsRID) Then
DBEngine(0)(0).Execute "DataComments", dbFailOnError
End If
If IsNull(Me.CdnsRID) Then
DBEngine(0)(0).Execute "DataConditions", dbFailOnError
End If
End Sub

I based this on code I found in a post by Allen Browne.