Requery when entering new record line

[Annelie]

Synchronizing subforms with mainforms seemed to be a real problem. Doing it
with a button works, but I would like for the forms to refresh from the
background.

I am using MainForm, with 2 subforms (Both Continues forms).
All three are linked by SSNo. of the Employee

The user selects an employee from a combo box based on the employee table.
In the first subform, the user enters the JobNo and Hrs worked on that job
for the week. After tabbing through the fields, when going to the second
line, it should show the job related info on the subform.

The second subform just shows information about the job the employee worked
for. So after completing the entry for the job or preferably on exiting the
JobNo field, the 2nd subform should display the information about the job.

I tried several different methods for the on exit event, but just can't get
it right.
Any Help would be appreciated.

 

[Marshall Barton]

Synchronizing subforms with mainforms seemed to be a real problem. Doing it
with a button works, but I would like for the forms to refresh from the
background.

I am using MainForm, with 2 subforms (Both Continues forms).
All three are linked by SSNo. of the Employee

The user selects an employee from a combo box based on the employee table.
In the first subform, the user enters the JobNo and Hrs worked on that job
for the week. After tabbing through the fields, when going to the second
line, it should show the job related info on the subform.

The second subform just shows information about the job the employee worked
for. So after completing the entry for the job or preferably on exiting the
JobNo field, the 2nd subform should display the information about the job.

I tried several different methods for the on exit event, but just can't get
it right.

It sounds like you should be using the subforms' Link
Child/Master properties to keep the sub forms in sync with
the value in the combo box. Check it out in Help and see if
it does what you want.

 

[John Vinson]

Synchronizing subforms with mainforms seemed to be a real problem. Doing it
with a button works, but I would like for the forms to refresh from the
background.

I am using MainForm, with 2 subforms (Both Continues forms).
All three are linked by SSNo. of the Employee

The user selects an employee from a combo box based on the employee table.
In the first subform, the user enters the JobNo and Hrs worked on that job
for the week. After tabbing through the fields, when going to the second
line, it should show the job related info on the subform.

The second subform just shows information about the job the employee worked
for. So after completing the entry for the job or preferably on exiting the
JobNo field, the 2nd subform should display the information about the job.

I tried several different methods for the on exit event, but just can't get
it right.
Any Help would be appreciated.

No code is needed at all!

Instead, set the Master Link Field property of the second subform to

[Forms]![mainformname]![firstsubform].Form![JobNo]

and the Child Link Field to JobNo. This will display the information
pertinant to the jobno on the selected record of the first subform.

If I've misunderstood your table/form structure please post back.

 

[Annelie]

My Mainform : FrmEmployeeCurrentData - linked to 1st Subform by SSNo.
1st SubForm: FrmEmployeeCurrentData1 - Data Entry Form ;Parent SSNo - Child
SSNo. - Need to link JobNo to 2nd SubForm
2nd SubForm: FrmEmployeeCurrentData2- Has SSNo Field and JobNo field -
Displays Only Job Information.

I tried typing into the Link Master Field:
[Forms]![FrmEmployeeCurrentData]![FrmEmployeeCurrentData1].Form![JobNo] and
[JobNo] into the Link Child Field.
When I try to run it, I am asked to enter a parameter.
Annelie

Synchronizing subforms with mainforms seemed to be a real problem. Doing it
with a button works, but I would like for the forms to refresh from the
background.

I am using MainForm, with 2 subforms (Both Continues forms).
All three are linked by SSNo. of the Employee

The user selects an employee from a combo box based on the employee table.
In the first subform, the user enters the JobNo and Hrs worked on that job
for the week. After tabbing through the fields, when going to the second
line, it should show the job related info on the subform.

The second subform just shows information about the job the employee worked
for. So after completing the entry for the job or preferably on exiting the
JobNo field, the 2nd subform should display the information about the job.

I tried several different methods for the on exit event, but just can't get
it right.
Any Help would be appreciated.

No code is needed at all!

Instead, set the Master Link Field property of the second subform to

[Forms]![mainformname]![firstsubform].Form![JobNo]

and the Child Link Field to JobNo. This will display the information
pertinant to the jobno on the selected record of the first subform.

If I've misunderstood your table/form structure please post back.

 

[Marshall Barton]

Synchronizing subforms with mainforms seemed to be a real problem. Doing it
with a button works, but I would like for the forms to refresh from the
background.

I am using MainForm, with 2 subforms (Both Continues forms).
All three are linked by SSNo. of the Employee

The user selects an employee from a combo box based on the employee table.
In the first subform, the user enters the JobNo and Hrs worked on that job
for the week. After tabbing through the fields, when going to the second
line, it should show the job related info on the subform.

The second subform just shows information about the job the employee worked
for. So after completing the entry for the job or preferably on exiting the
JobNo field, the 2nd subform should display the information about the job.

I tried several different methods for the on exit event, but just can't get
it right.
Any Help would be appreciated.

No code is needed at all!

Instead, set the Master Link Field property of the second subform to

[Forms]![mainformname]![firstsubform].Form![JobNo]

John, I was under the impression that the Link Master had to
be a main form text box with the expression:

=[firstsubform].Form![JobNo]

I.e. the text box acts as an intermediary between the two
subforms.

 

[John Vinson]

John, I was under the impression that the Link Master had to
be a main form text box with the expression:

=[firstsubform].Form![JobNo]

I.e. the text box acts as an intermediary between the two
subforms.

I had thought so too, but someone here posted an example of using the
*FULLY QUALIFIED* (no shortcuts) form reference; at least in A2000 and
A2002 it seems to work.

The Relay textbox that you suggest *certainly* works, and has been
often recommended, so it might be worth the hassle of the extra
control.

 

[Annelie]

I am glad you guys know what you are talking about.
You left me behind. Have no clue what you are saying. I found the post about
the fully qualified, could you explain it to me? Please,
Annelie

John, I was under the impression that the Link Master had to
be a main form text box with the expression:

=[firstsubform].Form![JobNo]

I.e. the text box acts as an intermediary between the two
subforms.

I had thought so too, but someone here posted an example of using the
*FULLY QUALIFIED* (no shortcuts) form reference; at least in A2000 and
A2002 it seems to work.

The Relay textbox that you suggest *certainly* works, and has been
often recommended, so it might be worth the hassle of the extra
control.

 

[Annelie]

I am determined learn to understand the relation ship between the forms and
subforms. By shear luck I get some to work at times and at other times not.
I am somewhat unclear about what everyone is talking about when it comes the
"subforms controls".

Everyone explains that the reference has to be to the subform control not
the subform name itself. I understand that. But are you talking about the
"control source" or the control source's name? When a relationship has to be
established between a text box to/from a field to the subform, what is the
control, the control source or the name field. Since an unbound text box has
no control source, it must be the name.

Am I referring to the control source's name always, or only in case of an
unbound control? If I reference a bound field, do I use the name or the
control source?
I am sure I am not the only one confused about this, would someone please
explain? I am getting confused just writing this.

The second problem is, where to I put these statements? I just type it into
the master or child link properties? That is what I did using John Vinson's
suggestion (and I think he got my layout just fine) just brought up a
message box asking me for parameter values.
Please give me a hand and explain the whooooooole thing.
Annelie

Synchronizing subforms with mainforms seemed to be a real problem. Doing it
with a button works, but I would like for the forms to refresh from the
background.

I am using MainForm, with 2 subforms (Both Continues forms).
All three are linked by SSNo. of the Employee

The user selects an employee from a combo box based on the employee table.
In the first subform, the user enters the JobNo and Hrs worked on that job
for the week. After tabbing through the fields, when going to the second
line, it should show the job related info on the subform.

The second subform just shows information about the job the employee worked
for. So after completing the entry for the job or preferably on exiting the
JobNo field, the 2nd subform should display the information about the job.

I tried several different methods for the on exit event, but just can't get
it right.
Any Help would be appreciated.

No code is needed at all!

Instead, set the Master Link Field property of the second subform to

[Forms]![mainformname]![firstsubform].Form![JobNo]

and the Child Link Field to JobNo. This will display the information
pertinant to the jobno on the selected record of the first subform.

If I've misunderstood your table/form structure please post back.

 

[John Vinson]

I am determined learn to understand the relation ship between the forms and
subforms. By shear luck I get some to work at times and at other times not.
I am somewhat unclear about what everyone is talking about when it comes the
"subforms controls".

Everyone explains that the reference has to be to the subform control not
the subform name itself. I understand that. But are you talking about the
"control source" or the control source's name? When a relationship has to be
established between a text box to/from a field to the subform, what is the
control, the control source or the name field. Since an unbound text box has
no control source, it must be the name.

Am I referring to the control source's name always, or only in case of an
unbound control? If I reference a bound field, do I use the name or the
control source?

It *is* confusing - since you can do *either*. The Forms Wizard uses
the Control Source or, more precisely, the name of the field in the
Form's Recordsource property (this field need not even be bound to a
control on the form, unless you want it to be visible).

HOWEVER, it's *also* possible to use the name of a control (bound or
unbound) in the Master Link Field property. The Child Link Field must
be a fieldname, not a control name - but the Master lets you do either
one.

The other part of the confusion is the peculiar syntax required to
reference a control on a Subform on a Form. There's a heirarchy of
objects:

- a Form object - your main form, let's call it MyMainform
- A Subform control - the box on the mainform, which has its own Name
property, let's call it subMySubform1
- another Form object, the one being used as subMySubform1's Source
Object - let's call it sbfrm1. (Access *usually* makes these names
identical, just to confuse things further, but the Name of the form
object in the subform control is *irrelevant* to the reference. Told
you it was peculiar!)
- A Control on sbfrm1, let's say it's txtID

To unambiguously refer to the value in this control, from anywhere,
you can use

[Forms]![MyMainform]![subMySubform1].Form![txtID]

Note the bangs and dots; the above *does* work though (again
confusingly) some ! can be replaced with . if you wish.

My suggestion was to use this unambiguous reference to a (bound, in
this case, though again it doesn't need to be bound) Control as the
Master Link Field property of a second subform. Doing so will link the
currently-selected record of subMySubform1 to all matching records in
the second subform.

 

[Annelie]

Thank you John,
I had given up on my post after a couple of days, when nobody answered . I
just happened to scroll for some answers again and I found your explanation.
I appreciate your taking the time and it is well explained. I copied your
answer to my help list, so I can refer to it on occasion.
Annelie

I am determined learn to understand the relation ship between the forms and
subforms. By shear luck I get some to work at times and at other times not.
I am somewhat unclear about what everyone is talking about when it comes the
"subforms controls".

Everyone explains that the reference has to be to the subform control not
the subform name itself. I understand that. But are you talking about the
"control source" or the control source's name? When a relationship has to be
established between a text box to/from a field to the subform, what is the
control, the control source or the name field. Since an unbound text box has
no control source, it must be the name.

Am I referring to the control source's name always, or only in case of an
unbound control? If I reference a bound field, do I use the name or the
control source?

It *is* confusing - since you can do *either*. The Forms Wizard uses
the Control Source or, more precisely, the name of the field in the
Form's Recordsource property (this field need not even be bound to a
control on the form, unless you want it to be visible).

HOWEVER, it's *also* possible to use the name of a control (bound or
unbound) in the Master Link Field property. The Child Link Field must
be a fieldname, not a control name - but the Master lets you do either
one.

The other part of the confusion is the peculiar syntax required to
reference a control on a Subform on a Form. There's a heirarchy of
objects:

- a Form object - your main form, let's call it MyMainform
- A Subform control - the box on the mainform, which has its own Name
property, let's call it subMySubform1
- another Form object, the one being used as subMySubform1's Source
Object - let's call it sbfrm1. (Access *usually* makes these names
identical, just to confuse things further, but the Name of the form
object in the subform control is *irrelevant* to the reference. Told
you it was peculiar!)
- A Control on sbfrm1, let's say it's txtID

To unambiguously refer to the value in this control, from anywhere,
you can use

[Forms]![MyMainform]![subMySubform1].Form![txtID]

Note the bangs and dots; the above *does* work though (again
confusingly) some ! can be replaced with . if you wish.

My suggestion was to use this unambiguous reference to a (bound, in
this case, though again it doesn't need to be bound) Control as the
Master Link Field property of a second subform. Doing so will link the
currently-selected record of subMySubform1 to all matching records in
the second subform.