Main Form Text Box Referencing a Subform Field

[William Wisnieski]

Hello Everyone,

Access 2000

I have a main form with a continuous subform. On the main form I have a
text box that references a field on the subform. What I'd like it to do is
show the value in the field of the last record entered on the subform. But
what its doing is referencing whatever record the user clicked on last
before closing all the forms. Also if the user cleared the subform field, I
would like the text box on the main form to show a null value as well.

Here is what I have for the control source of the text box on the main form

=[Forms]![frmStudentRecor d]![sfrmCommunicati on]![CommStatus]

Thanks for your help.

William

[Larry Linson]

Re: Main Form Text Box Referencing a Subform Field

This Control needs to be requeried to update the contents. It'll be up to
you to determine when/where that Requery should be placed in code. Offhand,
it would seem to me that it should be requeried in the AfterUpdate event of
the Control to which its Control Source refers.

Me.parent!<name ofmainformcontr ol>.Requery

Presumably, it will be requeried whenever you move to a new record on the
main form, and will reference whichever record is selected in the subform at
the time of the reference.

Larry Linson
Microsoft Access MVP

"William Wisnieski" <wwisnieski@adm issions.umass.e du> wrote in message
news:3ff9dc14$1 @news-1.oit.umass.edu ...[color=blue]
> Hello Everyone,
>
> Access 2000
>
> I have a main form with a continuous subform. On the main form I have a
> text box that references a field on the subform. What I'd like it to do[/color]
is[color=blue]
> show the value in the field of the last record entered on the subform.[/color]
But[color=blue]
> what its doing is referencing whatever record the user clicked on last
> before closing all the forms. Also if the user cleared the subform field,[/color]
I[color=blue]
> would like the text box on the main form to show a null value as well.
>
> Here is what I have for the control source of the text box on the main[/color]
form[color=blue]
>
> =[Forms]![frmStudentRecor d]![sfrmCommunicati on]![CommStatus]
>
> Thanks for your help.
>
> William
>
>[/color]

[Phil Freihofner]

Re: Main Form Text Box Referencing a Subform Field

"William Wisnieski" <wwisnieski@adm issions.umass.e du> wrote in message news:<3ff9dc14$ 1@news-1.oit.umass.edu >...[color=blue]
> Hello Everyone,
>
> Access 2000
>
> I have a main form with a continuous subform. On the main form I have a
> text box that references a field on the subform. What I'd like it to do is
> show the value in the field of the last record entered on the subform. But
> what its doing is referencing whatever record the user clicked on last
> before closing all the forms. Also if the user cleared the subform field, I
> would like the text box on the main form to show a null value as well.
>
> Here is what I have for the control source of the text box on the main form
>
> =[Forms]![frmStudentRecor d]![sfrmCommunicati on]![CommStatus]
>
> Thanks for your help.
>
> William[/color]

Hi -

Maybe instead of pulling the data from the subform into the main form
you should "push" it instead. Or some combination. From the subform,
on the form_insert event, push the value back onto the parent. Or
maybe use the CommStatus.Afte rUpdate event. To reach your field, you
would use Me.Parent("Name OfField"). I'm not sure what the "bang"
equivalent is. I never use them.

If it is really vital to identify the last record inserted, you need a
way of identifying it. Are you using an autonumber for the ID or key
field? That's usually a good practice, or else add a date field to
track when the record is inserted, then use a "SELECT MAX ..." sql
string for a recordset to get the value.

Hope this helps.

Phil Freihofner