Linking records in two tables

[luciegiles]

I have 2 tables, tblParticipantDetails and tblPreCourseQ - both have ParticipantID in them and this links the tables in relationships. ParticipantID is the primary key (and an autonumber) in tblParticipantDetails and is a number (long integer) in tblPreCourseQ. I want the ParticipantID to update in tblPreCourseQ with the same number as assigned by the autonumber ParticpantID in tblParticpantDetails. Data is entered via forms (frmParticpantDetails and frmPreCourseQ - source is tables as above) - frmPreCourseQ is opened from a command button on frmParticpantDetails using a macro. Is there anyway I can have ParticpantID fill in frm and tblPreCourseQ when a new record is opened from frmParticpantDetails?

I know I could use a subform but don't really want to do this as I have 3 instances where I want to do this and don't want to put everything in one table.

thanks

[Megalog]

In the Open event for the form that is bound to tblPreCourseQ (frmPreCourseQ) , you can do the following:

On Error Resume Next
If Me.newrecord then
    Me.txtParticipantID = [Forms]![frmParticipantDetails].[txtParticipantID]
End If

[luciegiles]

What do you mean by Open event?

[luciegiles]

No problem - done it as follows

Private Sub Form_Load()
 
On Error Resume Next
If Me.NewRecord Then
    Me.ParticipantID = [Forms]![frmParticipantDetails].[ParticipantID]
End If
 
End Sub

thanks for your help

[NeoPa]

To avoid setting the form as dirty (when it thinks that changes have been made and need to be either saved or discarded) I would suggest rather to set the Default property to the value required rather than the Value property.

[Megalog]

Yeah I meant Load event.. The coffee hasnt kicked in yet this morning =)

That code should work fine as long as the frmParticipantDetails form is always open whenever frmPreCourseQ is loaded.