Creating new record in a sub-form

[Neil Terry]

I have a form based on two linked tables. The linked form
is opened from the main form with a command button. It's
all working great with this exception: When I create a new
record in the linked form, the key that links the two
forms is not generated. I've done this before but have
forgotten how. I think it's based on a 'before update'
event in the sub-form but that's as far as I can go.

I will appreciate your help.

Thanks,
Neil

 

[Rick Brandt]

I have a form based on two linked tables. The linked form
is opened from the main form with a command button. It's
all working great with this exception: When I create a new
record in the linked form, the key that links the two
forms is not generated. I've done this before but have
forgotten how. I think it's based on a 'before update'
event in the sub-form but that's as far as I can go.

Technically, if it's not embedded in the main form then it's not a
"sub-form". An embedded sub-form would take care of this for you
automatically. When you pop up a separate form you need to take handle it
yourself.

You can use the code that opens the pop-up form to set the values in the
linked fields after opening it.

DoCmd.OpenForm "YourPopUp"
Forms!YourPopUp!SomeField = Me!SomeField

You can also use the OpenEvent of the pop-up to set the values by pulling
them from the calling form.
Me!SomeField = Forms!CallingForm!SomeField

The problem with both of those is that they Dirty the pop-up form every
time it is opened so it makes it more difficult to cancel the creation of a
record. The following two alternatives don't have that problem.

Assign the DefaultValue property of the field on the pop-up form
referencing the field value from the calling form.
Me!SomeControl.DefaultValue = Forms!CallingForm!SomeField

Assign the value of the field on the pop-up in its BeforeUpdate event or
BeforeInsert event.
Me!SomeField = Forms!CallingForm!SomeField

These last two methods assume that the calling form will always be open
whenever the pop-up form is used. If that is not true then you would need
to add a test for that.

 

[Neil Terry]

Thanks Rick.
Just what I needed.
Neil

-----Original Message-----


Technically, if it's not embedded in the main form then it's not a
"sub-form". An embedded sub-form would take care of this for you
automatically. When you pop up a separate form you need to take handle it
yourself.

You can use the code that opens the pop-up form to set the values in the
linked fields after opening it.

DoCmd.OpenForm "YourPopUp"
Forms!YourPopUp!SomeField = Me!SomeField

You can also use the OpenEvent of the pop-up to set the values by pulling
them from the calling form.
Me!SomeField = Forms!CallingForm!SomeField

The problem with both of those is that they Dirty the pop- up form every
time it is opened so it makes it more difficult to cancel the creation of a
record. The following two alternatives don't have that problem.

Assign the DefaultValue property of the field on the pop- up form
referencing the field value from the calling form.
Me!SomeControl.DefaultValue = Forms!CallingForm!SomeField

Assign the value of the field on the pop-up in its BeforeUpdate event or
BeforeInsert event.
Me!SomeField = Forms!CallingForm!SomeField

These last two methods assume that the calling form will always be open
whenever the pop-up form is used. If that is not true then you would need
to add a test for that.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com





.