New record in subform

[Guest]

I am baffled about this! I'm Using Access 2002. Have a form (frm1), based
on tbl1. Another form, frm2, is also based on tbl1, and has a subform on it
which is based on tbl2. tbl2 is in a many-to-one relationship with tbl1.
The primary key in tbl1 is SamplingID, in tbl2 it is SamplingID and MachineNo.

I'm trying to open frm2, with the subform open to a new record. The
samplingID from frm1 is the SamplingID being opened in frm2. I want to add a
new record to the subform (tbl2), with an incremented MachineNo.

What is happening is that, instead of a new subform record, the subform is
opening to the second (if there are 2+ records) record for this SamplingID,
and overwriting the MachineNo with the new MachineNo.

Here is code in the OnOpen event of frm2. I used an OpenArgs statement in
frm1, to open frm2, which includes the incremented MachineNo field:

ElseIf Len(Me.OpenArgs) > 1 Then 'add new record with pre-set MachineNo
strSamplingID = Forms!frm1!SamplingID
Me.SamplingID = strSamplingID
Me.DataFileName = strDataFileName
Me.frm2Sub.SetFocus
DoCmd.GoToRecord , , acNewRec
Me.frm2Sub.Form!SamplingID = strSamplingID
strMachineNo = Right(OpenArgs, 1)
Me.frmWaveCorderInfoSub.Form!MachineNo = strMachineNo
End If

I hope this makes sense - I appreciate any help! Terri

 

[Guest]

Sometimes all I have to do is post a question and then the answer comes to me


Just realized that, in my code below, I was trying to open the second form
to a new record, rather than opening it to an existing record and opening the
SUBFORM to a new record. Instead of the overwriting, I was starting to get
the "You Can't Assign A Value to this object) error.

What I did - changed the code below to:

ElseIf Len(Me.OpenArgs) > 1 Then 'new subform record with pre-set
MachineNo
'First go to existing form record
DoCmd.GoToControl "SamplingID"
DoCmd.FindRecord strSamplingID, , True, , True
'Then open new subform record
Me.frm2Sub.SetFocus
DoCmd.GoToRecord , , acNewRec
strMachineNo = Right(OpenArgs, 1)
Me.frm2Sub.Form!MachineNo = strMachineNo
End If

This worked perfectly! Just FYI - Terri

 

[Guest]

Terri,

It looks like you have a better understanding of subforms than I do. I have
a main form that has 3 subforms. The first subform has a 1-1 relationship
with the record connected to the main form. The other 2 subforms are based
on children of the record connected to the first subform. I need to be able
to limit the user to create no more than 1 record on the first subform. Any
idea how to do that?

Thanks.

 

[John Vinson]

Terri,

It looks like you have a better understanding of subforms than I do. I have
a main form that has 3 subforms. The first subform has a 1-1 relationship
with the record connected to the main form. The other 2 subforms are based
on children of the record connected to the first subform. I need to be able
to limit the user to create no more than 1 record on the first subform. Any
idea how to do that?

PMFJI but... if you truly have a one to one relationship, you are
already covered. You won't be allowed to enter a second record.

However, one to one relationships are VERY rare in practice. Are you
intentionally Subclassing? or using a second table with security
applied so that you can get field-level security? If not, perhaps you
don't need a one to one relationship, and could instead simply put all
the fields in one table.

John W. Vinson[MVP]