Requery Form data

[Tara]

I have a form which displays general employee data. From this form, the user
can access another form which displays general 401K information. From THAT
form, they can access another form that allows additions or changes to more
specific 401K info. Everything is linked via EmployeeID. What happens
currently is that the user chooses the employee on the first form and the
second form automatically defaults to that employees 401k general data. The
third form in turn, defaults to this employee's 401k specific data. What I'd
like to see happen is that the second form defaults to the employee chosen on
the first form, BUT, I'd like to make it possible for the user to choose a
different employee from a combo on the 2nd form and then have the form data
update. At that point, if the users opens the 3rd form, I want it to default
to the employee chosen on the 2nd form.

Does that make any sense at all?

Thanks for any help!

 

[Jeff Boyce]

Tara

If I'm understanding your situation, you might want to consider using a
single form plus a tab control. The tab control would allow you to display
different sub-forms on the tabs, based on an employee selected on the main
form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tara]

Thanks for responding Jeff. I see your line of thinking, but that's not
quite what I'm looking for. Essentially I want to be able to choose a
different employee from either form. For example, I choose John Doe on
frmEmployee, then click a button to open the 2nd form, frm401KGeneral.
Frm401kGeneral would open to John Doe's info - His name would be displayed in
a combo box and all text boxes would display his 401k General information.
However, if, while I'm looking at John Doe's info on frm401KGeneral, I
remember that I need to update info in Tom Smith's record, I can then choose
Tom Smith from the combo box on frm401kGeneral and click a button to open
frm401kSpecific. Frm401kSpecific would then open to display Tom Smith's
info, not John Doe's.

Does that make more sense?

 

[Jeff Boyce]

Tara

I don't have experience doing it that way, but maybe one of the other
newsgroup readers can assist.

When I need to check on details for some entity, and have (potentially)
multiple different categories of details (i.e., "child records"), I tend to
use a tab control because:
* the main form contains the combobox for looking up entities (and no
other form needs one)
* the main form displays the "parent" information in one place
* the main form (via the tab control) displays a multitude of "child"
information (via subforms in tab pages)
* and I only need to re-use the (single) combobox on the main form to deal
with a new entity's details

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Toews [MVP]]

Thanks for responding Jeff. I see your line of thinking, but that's not
quite what I'm looking for. Essentially I want to be able to choose a
different employee from either form. For example, I choose John Doe on
frmEmployee, then click a button to open the 2nd form, frm401KGeneral.
Frm401kGeneral would open to John Doe's info - His name would be displayed in
a combo box and all text boxes would display his 401k General information.
However, if, while I'm looking at John Doe's info on frm401KGeneral, I
remember that I need to update info in Tom Smith's record, I can then choose
Tom Smith from the combo box on frm401kGeneral and click a button to open
frm401kSpecific. Frm401kSpecific would then open to display Tom Smith's
info, not John Doe's.

Ah, so you want to have frmEmployee open twice at the same time for
two different employees. Yes, you can do that although there is some
wierdnesses that occasionally happen. What they all are I misremember
and I probably haven't encountered them all. So you'll have to figure
them all out as you go along as each situation could be slightly
different.

Note that the following code opens the form as a separate object. So
the form might or might not already be open using the standard
docmd.openform syntax.

1) You need to declare a global form variable for each form you want
open a second time. If you want to open a form a third time you need
to declare another variable for it.

Public frmDFT As Form

If you define the form variable in a calling form then the called form
will close as soon as you close the called form.

And how do I know this? Yup, wasted some time before I had the Homer
Duhh! moment.

2) You can't pass in a where clause like you are used to. So you need
to store such variables elsewhere. As I always have a hidden form
bound to a GlobalOptions table in the backend I just add an extra
control on that form.

The code to open a form the second time is:

Forms!globaloptionshidden!DFTIDToView = Me![dfthID]

Set frmDFT = New Form_DailyFieldTicket
frmDFT.SetFocus

Note that you have to have that unusual syntax Form_ in there.

3) If your forms name has spaces in it use _ (underscore) in the VBA
code. That is New Form_Daily_Field_Ticket when the form name is Daily
Field Ticket.

4) If you need to reference controls on the form using this method you
will need to use the global form variable in your code. Something
like

DailyFieldTicketID = frmDFT.dftID

rather than your expected

DailyFieldTicketID = forms![Daily Field Ticket]!dftID

This can cause you some wierd things happening in other code you
created weeks or months earlier referencing the form. Thus I would
strongly suggest you use a find and replace code to go looking through
all your queries, forms, reports and VBA code looking for the form
name.

Hmm, what happens when you have a query referencing a control on such
a form? I don't know as I don't do that very often and haven't come
across that particular problem.

There are more things I'm sure I've forgotten as I generally don't do
this too often and I forget the gotchas. I should create a web page
on this topic. I have most if it already done. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tara]

Thanks for responding Tony. I think we're close to being on the same page,
but I still don't think it's quite what I need. Here's what I need in a
nutshell:

On frmEmployee I choose John Doe. I click a command button and
frm401kGeneral opens to John Doe's info. There's a combo on frm401kGeneral
in which all employees names and ID numbers show up, but the form opens with
John Doe's name selected in the combo. I want to be able to choose a
different name from that combo and then proceed on to frm401kSpecific and
have that person's info load, not John Doe's.

Can that work?

Thanks for responding Jeff. I see your line of thinking, but that's not
quite what I'm looking for. Essentially I want to be able to choose a
different employee from either form. For example, I choose John Doe on
frmEmployee, then click a button to open the 2nd form, frm401KGeneral.
Frm401kGeneral would open to John Doe's info - His name would be displayed in
a combo box and all text boxes would display his 401k General information.
However, if, while I'm looking at John Doe's info on frm401KGeneral, I
remember that I need to update info in Tom Smith's record, I can then choose
Tom Smith from the combo box on frm401kGeneral and click a button to open
frm401kSpecific. Frm401kSpecific would then open to display Tom Smith's
info, not John Doe's.

Ah, so you want to have frmEmployee open twice at the same time for
two different employees. Yes, you can do that although there is some
wierdnesses that occasionally happen. What they all are I misremember
and I probably haven't encountered them all. So you'll have to figure
them all out as you go along as each situation could be slightly
different.

Note that the following code opens the form as a separate object. So
the form might or might not already be open using the standard
docmd.openform syntax.

1) You need to declare a global form variable for each form you want
open a second time. If you want to open a form a third time you need
to declare another variable for it.

Public frmDFT As Form

If you define the form variable in a calling form then the called form
will close as soon as you close the called form.

And how do I know this? Yup, wasted some time before I had the Homer
Duhh! moment.

2) You can't pass in a where clause like you are used to. So you need
to store such variables elsewhere. As I always have a hidden form
bound to a GlobalOptions table in the backend I just add an extra
control on that form.

The code to open a form the second time is:

Forms!globaloptionshidden!DFTIDToView = Me![dfthID]

Set frmDFT = New Form_DailyFieldTicket
frmDFT.SetFocus

Note that you have to have that unusual syntax Form_ in there.

3) If your forms name has spaces in it use _ (underscore) in the VBA
code. That is New Form_Daily_Field_Ticket when the form name is Daily
Field Ticket.

4) If you need to reference controls on the form using this method you
will need to use the global form variable in your code. Something
like

DailyFieldTicketID = frmDFT.dftID

rather than your expected

DailyFieldTicketID = forms![Daily Field Ticket]!dftID

This can cause you some wierd things happening in other code you
created weeks or months earlier referencing the form. Thus I would
strongly suggest you use a find and replace code to go looking through
all your queries, forms, reports and VBA code looking for the form
name.

Hmm, what happens when you have a query referencing a control on such
a form? I don't know as I don't do that very often and haven't come
across that particular problem.

There are more things I'm sure I've forgotten as I generally don't do
this too often and I forget the gotchas. I should create a web page
on this topic. I have most if it already done. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/