New record in subform

[Skunk]

Please help this amateur.

I have a form containing two subforms (not nested, same level).

Subform 1 displays records on a continuous form, bound to the same query as
its parent, user cannot edit but can select a record via button.

Subform 2 is a single form, bound to table that is not in the above query,
data entry (et al) = yes.

Selection button on subform 1 partially populates subform 2, user fills in
remainder.

My issue: Works great when no record exists in subform 2 table. When I
select a second record in subform 1, the first record is retrieved in subform
2 -- I need a blank/new (except for partial population) record.

My button code is:
Dim rs As Object
Set rs = Forms![2frmCancel].Recordset.Clone

' Find the record that matches the control.
rs.FindFirst "[InvNum] = " & Me![InvNum] & ""
If Not rs.EOF Then Forms![2frmCancel].Bookmark = rs.Bookmark

Parent!lblCancel.Visible = True
Parent!lblClose.Visible = False

Forms![2frmCancel]!CancelEntryfrm.Form!txtInvNum = Parent!txtInvNum
Forms![2frmCancel]!CancelEntryfrm.Form!DateCancelled = Date

[2frmCancel] is the shell containing the subforms.
CancelEntryfrm is subform 2.

Note that on record selection button event I reset [2frmCancel] to the
selected record. This is to update a control on that form.

Can anyone help me with a solution or give me some idea of resolution?

 

[dch3]

There's always more than one way to skin a cat in Access, although the cats
don't like being played with like that - makes them grumpy...

Don't worry about using code to display a record's detail or add a new
record in the second subform. You can link Master/Child fields between
subforms as in...

linkChildFields - lngTrailerActivityHeaderId
linkMasterFields - subfrmSubform1.form.lngTrailerActivityHeaderId

By fully qualifying the field in the subform to use as the master, Access
will treat the first subform as the parent and the second as a child. As
such, you wont have to do any searches for the relevant record nor will you
have to actually insert a record if one doesn't exist.

 

[Skunk]

Thank you. I did not know that could be done. Should be handy. I'll try
it sometime -- in this case I changed direction a little, so its not
necessary.

Again, thanks for the useful info.
--
skunk

There's always more than one way to skin a cat in Access, although the cats
don't like being played with like that - makes them grumpy...

Don't worry about using code to display a record's detail or add a new
record in the second subform. You can link Master/Child fields between
subforms as in...

linkChildFields - lngTrailerActivityHeaderId
linkMasterFields - subfrmSubform1.form.lngTrailerActivityHeaderId

By fully qualifying the field in the subform to use as the master, Access
will treat the first subform as the parent and the second as a child. As
such, you wont have to do any searches for the relevant record nor will you
have to actually insert a record if one doesn't exist.

Please help this amateur.

I have a form containing two subforms (not nested, same level).

Subform 1 displays records on a continuous form, bound to the same query as
its parent, user cannot edit but can select a record via button.

Subform 2 is a single form, bound to table that is not in the above query,
data entry (et al) = yes.

Selection button on subform 1 partially populates subform 2, user fills in
remainder.

My issue: Works great when no record exists in subform 2 table. When I
select a second record in subform 1, the first record is retrieved in subform
2 -- I need a blank/new (except for partial population) record.

My button code is:
Dim rs As Object
Set rs = Forms![2frmCancel].Recordset.Clone

' Find the record that matches the control.
rs.FindFirst "[InvNum] = " & Me![InvNum] & ""
If Not rs.EOF Then Forms![2frmCancel].Bookmark = rs.Bookmark

Parent!lblCancel.Visible = True
Parent!lblClose.Visible = False

Forms![2frmCancel]!CancelEntryfrm.Form!txtInvNum = Parent!txtInvNum
Forms![2frmCancel]!CancelEntryfrm.Form!DateCancelled = Date

[2frmCancel] is the shell containing the subforms.
CancelEntryfrm is subform 2.

Note that on record selection button event I reset [2frmCancel] to the
selected record. This is to update a control on that form.

Can anyone help me with a solution or give me some idea of resolution?