Subform Default Key Value when adding new records to table

[James]

I have a form for table A that calls another form for table B through
a button. When I hit the button on the form for A it will give me all
records in B that correspond to the key value in A but when I want to
create new records in B the default value for the key is zero. I
want the default value of the key in B to be the key value from
record in A. Can this be done and if so how do I do it?

Thanks for your help.

James

 

[James]

Turns out I asked the question too soon. I just figured it out.
Please disregard my request.

 

[Allen Browne]

Hi James

Glad you figured it out.

As a side issue, consider setting the Required property to Yes for the
foreign key field in your table, so you cannot accidentally save a record
without a foreign key. (This tends to happen if you enter a record in the
subform when the main form is at a new record.)

 

[Sandra]

I'm having the same problem, and I haven't figured it out
yet LOL! Can someone clue me in please?
Thanks,
Sandra

 

[Allen Browne]

HI Sandra.

1. Open your table in design view (the one the subform is bound to).

2. Select the foreign key field (the one that matches a value in the main
form).

3. In the lower pane, remove the zero beside the Default Value property.
This will stop Access from trying to assign the nonsensical value of 0 when
no other value is assigned.

4. At the same time, set the Required property of the field to Yes. This
stops Access from saving a subform record if the foreign key is left blank.

 

[James]

Thanks for the tip on the required property.

What I did was set the default value property for the key field in the
subform to the value of the field in the main form like this:

=[Forms]!["Main form name"]!["Key field"]