Problem opening a Form

[Lyn]

Hi,
I am working on a genealogy project in which I have two tables:

Person -- one record for each person in the family. Each record has a
unique Autonum field (IDPerson).

Partnerships -- one record for each marriage. Each record has a unique
Autonum field (ID), and also contains two fields IDPartnerMale and
IDPartnerFemale. Both of the latter fields match records in the Person
table via IDPerson.

I have a main form (frmPerson) for displaying a record from the Person
table. This form contains a subform (sfmPartners) which lists in tabular
form certain fields from the Partnerships record (including ID) for which
Person.IDPerson matches either IDPartnerMale or IDPartnerFemale in the
Partnerships table. There may be multiple rows of Partnership displayed if
the Person married more than once.

So far, so good. The subform correctly displays marriage records for the
person displayed in the main form.

In the subform, I have added an Update command button at the end of each
data row. The Click event opens the update form (frmPartnership) and
displays the full Partnerships record whose Update button was clicked in the
subform sfmPartners. At least, that's the theory.

The Update button Click event has the following code:

Private Sub Update_Click()
DoCmd.OpenForm "frmPartnership", , , "Partnerships.ID =
[forms]![sfmPartners]![ID]", acFormEdit
End Sub

If my understanding is correct, this should open frmPartnership with the
Partnerships record matching the ID value of the row in sfmPartners whose
Update button was pressed. However, there appears to be something wrong
with my syntax, because I get prompted to type in the value for
Forms!sfmPartners!ID. If I type in this value, then form frmPartnership
displays with the correct record.

However, if I run sfmPartners as a main form, clicking the Update button
displays the correct update form with the correct value (without prompting
for input). It is only when sfmPartners is opened as a subform of frmPerson
that it fails as described. Can anyone see what I am doing wrong?

One further question -- I have placed the Update button in each row of
Partnerships displayed in sfmPartners. How can I have a single Update
button that opens sfmPartners for the record which is currently SELECTED?
This would seem to be a simpler and more elegant solution.

Thanks in advance.

--
Cheers,
Lyn.

[Allen Browne]

Hi Lyn.

First issue is that subforms are not open in their own right, i.e. they are
part of the forms collection. You can refer to them through their parent,
e.g.:

Forms![NameOfYourMainFormHere]![NameOfYourSubfomrControlHere].[Form]![ID]
That is explained in article:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

However, because this code occurs in the context of the form, you don't have
to worry about the parent form. Just concatenate the value from the form
into the string:
DoCmd.OpenForm "frmPartnership", , , "Partnerships.ID = " & Me![ID]

If you would prefer not to have the button on every row, you can place it in
the Form Footer section. Open the subform in design view. From the View
menu, choose Form Header/Footer (not Page Header/Footer). In the Properties
of the form, set the Default View to Continuous Form so the header show up.
The value is automatically interpreted as the value from the currently
selected row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyn" <lh******@ihug.com.au> wrote in message
news:cb**********@lust.ihug.co.nz...

Hi,
I am working on a genealogy project in which I have two tables:

Person -- one record for each person in the family. Each record has a
unique Autonum field (IDPerson).

Partnerships -- one record for each marriage. Each record has a unique
Autonum field (ID), and also contains two fields IDPartnerMale and
IDPartnerFemale. Both of the latter fields match records in the Person
table via IDPerson.

I have a main form (frmPerson) for displaying a record from the Person
table. This form contains a subform (sfmPartners) which lists in tabular
form certain fields from the Partnerships record (including ID) for which
Person.IDPerson matches either IDPartnerMale or IDPartnerFemale in the
Partnerships table. There may be multiple rows of Partnership displayed if the Person married more than once.

So far, so good. The subform correctly displays marriage records for the
person displayed in the main form.

In the subform, I have added an Update command button at the end of each
data row. The Click event opens the update form (frmPartnership) and
displays the full Partnerships record whose Update button was clicked in the subform sfmPartners. At least, that's the theory.

The Update button Click event has the following code:

Private Sub Update_Click()
DoCmd.OpenForm "frmPartnership", , , "Partnerships.ID =
[forms]![sfmPartners]![ID]", acFormEdit
End Sub

If my understanding is correct, this should open frmPartnership with the
Partnerships record matching the ID value of the row in sfmPartners whose
Update button was pressed. However, there appears to be something wrong
with my syntax, because I get prompted to type in the value for
Forms!sfmPartners!ID. If I type in this value, then form frmPartnership
displays with the correct record.

However, if I run sfmPartners as a main form, clicking the Update button
displays the correct update form with the correct value (without prompting
for input). It is only when sfmPartners is opened as a subform of frmPerson that it fails as described. Can anyone see what I am doing wrong?

One further question -- I have placed the Update button in each row of
Partnerships displayed in sfmPartners. How can I have a single Update
button that opens sfmPartners for the record which is currently SELECTED?
This would seem to be a simpler and more elegant solution.

Thanks in advance.

--
Cheers,
Lyn.

[Lyn]

Allen, thanks for your prompt reply. I knew it was something simple. I had
tried various formats including "Me!ID". The only problem was that I
included the whole thing inside the quotes instead of concatenating the
control reference outside the quotes. It works now. Many thanks.

I will check out your article and also using the form footer.

Thanks again.
--
Cheers,
Lyn.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...

Hi Lyn.

First issue is that subforms are not open in their own right, i.e. they are part of the forms collection. You can refer to them through their parent,
e.g.:

Forms![NameOfYourMainFormHere]![NameOfYourSubfomrControlHere].[Form]![ID]
That is explained in article:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

However, because this code occurs in the context of the form, you don't have to worry about the parent form. Just concatenate the value from the form
into the string:
DoCmd.OpenForm "frmPartnership", , , "Partnerships.ID = " & Me![ID]

If you would prefer not to have the button on every row, you can place it in the Form Footer section. Open the subform in design view. From the View
menu, choose Form Header/Footer (not Page Header/Footer). In the Properties of the form, set the Default View to Continuous Form so the header show up. The value is automatically interpreted as the value from the currently
selected row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyn" <lh******@ihug.com.au> wrote in message
news:cb**********@lust.ihug.co.nz...

Hi,
I am working on a genealogy project in which I have two tables:

Person -- one record for each person in the family. Each record has a
unique Autonum field (IDPerson).

Partnerships -- one record for each marriage. Each record has a unique
Autonum field (ID), and also contains two fields IDPartnerMale and
IDPartnerFemale. Both of the latter fields match records in the Person
table via IDPerson.

I have a main form (frmPerson) for displaying a record from the Person
table. This form contains a subform (sfmPartners) which lists in tabular form certain fields from the Partnerships record (including ID) for which Person.IDPerson matches either IDPartnerMale or IDPartnerFemale in the
Partnerships table. There may be multiple rows of Partnership displayed

if

the Person married more than once.

So far, so good. The subform correctly displays marriage records for the
person displayed in the main form.

In the subform, I have added an Update command button at the end of each
data row. The Click event opens the update form (frmPartnership) and
displays the full Partnerships record whose Update button was clicked in

the

subform sfmPartners. At least, that's the theory.

The Update button Click event has the following code:

Private Sub Update_Click()
DoCmd.OpenForm "frmPartnership", , , "Partnerships.ID =
[forms]![sfmPartners]![ID]", acFormEdit
End Sub

If my understanding is correct, this should open frmPartnership with the
Partnerships record matching the ID value of the row in sfmPartners whose Update button was pressed. However, there appears to be something wrong
with my syntax, because I get prompted to type in the value for
Forms!sfmPartners!ID. If I type in this value, then form frmPartnership
displays with the correct record.

However, if I run sfmPartners as a main form, clicking the Update button
displays the correct update form with the correct value (without prompting for input). It is only when sfmPartners is opened as a subform of

frmPerson

that it fails as described. Can anyone see what I am doing wrong?

One further question -- I have placed the Update button in each row of
Partnerships displayed in sfmPartners. How can I have a single Update
button that opens sfmPartners for the record which is currently SELECTED? This would seem to be a simpler and more elegant solution.

Thanks in advance.

--
Cheers,
Lyn.