Main form not updating

[Guest]

Here's the issue: I have a main form with a subform on it. The main form's
record source is called "tblMainForm" with one field in it-MainFormPK-the
primary key. The subform's record source is another table called "tblSubform"
and it has three fields-SubformPK (the primary key), MainFormPK (foreign key)
and DataEntry-a standard text field.

I want to create a new record so I open the main form and am immediately
brought to the subform's text box whose control source is DataEntry. I enter
something into the text box and close the form. What I entered into the text
box is saved in tblSubform but the main form did not create a new record
therefore MainFormPK was not automatically entered as the foreign key in
tblSubform and there is no link between the forms. (By the way, the Master
and Child fields are set properly.)

My question: Is it mandatory that the main form containing a subform have a
data entry field? If not, does anyone know of a way around it? Thanks for any
help.

 

[BruceM]

Is MainFormPK an autonumber? If so, there is no apparent point to the main
form record. You are attempting to relate subform records to an arbitrary
number. I have never tried to do that, but I know that if you use a form to
navigate to a new record that has an autonumber PK, then navigate away from
that record, the record is not saved. As far as Access is concerned, the
record never existed. You could probably explicitly save the main record,
but why?
If MainFormPK is not an autonumber, what is it?

 

[Guest]

Yes, MainFormPK is an autonumber and your point would be well taken if I
really only had just one subform. In my real-world case, however, I have six
subforms under tabs on the main form and they all need to be related via the
MainFormPK.

 

[BruceM]

The number of subforms is not relevant. My point is that an autonumber is
meaningless.

You need another field besides autonumber. I don't think there is a way of
saving a record that contains only an autonumber. You could devise your own
incrementing number, but again I don't see the point. Let's say a record
ends up with the number 12345 as its only field (MainID). Now you can
relate the subform record source tables to MainID, so that all six subforms
show records that have 12345 in common with the main record. But what is
12345? Does it mean anything that the one thing the various records have in
common is 12345?

What do the six subforms have in common with each other that you want them
grouped together? Whatever that common element is, it needs to be in the
main form's record source.

 

[Guest]

The six subforms are actually six different pages used by loan officers in
order to gather data and perform calculations on the viability of a proposed
loan. The six pages are naturally related by the loan number but that number
sometimes isn't known until well into the approval process. So in lieu of
the loan number I thought I could just relate the six subforms with the
auto-generated MainFormPK. From what you write, however, that doesn't seem
possible. Do you think it could be done using your example of my own
incrementing number? If so, could you please elaborate a little more about
that?

 

[BruceM]

You could make the one field a Long Integer Number field (rather than
autonumber), and use something like this in the form's Current event:

If Me.NewRecord Then
Me.MainID = Nz(DMax("[MainID]","tblMain"),0) + 1
End If

I suggest looking up DMax and Nz so that you get a better understanding of
what is going on with the code.

But don't you want something else there like the applicant or the date or
the loan officer or something? I mean, isn't a loan request submitted by a
person or a company? That information should be on the main form.

 

[Guest]

I would if I had designed the forms. The forms were already designed the way
they wanted them when I got here. They asked me to put them in Access and
make them work smoothly.

Thank you very much for your assistance. I think your idea about the
self-incrementing long integer field is excellent and I will try it out
immediately.

You could make the one field a Long Integer Number field (rather than
autonumber), and use something like this in the form's Current event:

If Me.NewRecord Then
Me.MainID = Nz(DMax("[MainID]","tblMain"),0) + 1
End If

I suggest looking up DMax and Nz so that you get a better understanding of
what is going on with the code.

But don't you want something else there like the applicant or the date or
the loan officer or something? I mean, isn't a loan request submitted by a
person or a company? That information should be on the main form.

The six subforms are actually six different pages used by loan officers in
order to gather data and perform calculations on the viability of a
proposed
loan. The six pages are naturally related by the loan number but that
number
sometimes isn't known until well into the approval process. So in lieu of
the loan number I thought I could just relate the six subforms with the
auto-generated MainFormPK. From what you write, however, that doesn't
seem
possible. Do you think it could be done using your example of my own
incrementing number? If so, could you please elaborate a little more
about
that?