Confused about docmd.openform

[Thelma Lubkin]

formA determines some_where and some_value and issues

docmd.close ,Me
docmd.openform "formB", , ,some_where, , ,some_value

formB receives the correct some_where and some_value
After completing its work formB issues

docmd.close ,Me
docmd.openform "formA"

formA determines a new some_where and some_value and again issues

docmd.close ,Me
docmd.openform "formB", , ,some_where, , ,some_value

formB receives the correct some_where but no update of some_value

I also note that the forms' OnOpen event-code executes only the first
time that the forms are opened. How do I pass a new OpenArgs value to
formB after the first time?

I'm suspicious about where I've placed the close statements, but
neither order makes sense to me: if I close the form first, how does
the closed form execute the open statement that comes after the close
statement? ...but if I open the next form first, haven't I left the
environment where the close statement exists? And if my open
statements are failing, then why is *part* of the open statement
executing correctly [the some_where]?

--thelma
rephrasing an unanswered question

[Bas Cost Budde]

I have only a few bits here.

I suggest docmd.close acform, me.Name (you have just Me and I explicitly
address the Name property; I added the acform to be sure)

The Close event happens before Unload. That means the closing form can
still execute code. Anyway, as long as the events run, the code still
executes, I can't recall interruption there.

I suspect that when you open form A from form B, and form A opens form
B, code execution has not ended before OpenForm is reached, so form B
has never left memory. But that is just a guess.

I only recently tried to pass a new OpenArgs (because the called form
does something with it, of course) and failed. So, I created a public
sub in the form (routines in a form are private by default) that does
the settings. I can then call this from its own Open event, but the
caller can also call it.

So instead of

docmd.openform theform, openargs:= some_value

you can write

docmd.openform theform
forms!theform.Sync some_value

inside the form module you have

Public Sub Sync(theValue as Whatever)
' your code
End Sub
Thelma Lubkin wrote:

formA determines a new some_where and some_value and again issues

docmd.close ,Me
docmd.openform "formB", , ,some_where, , ,some_value

formB receives the correct some_where but no update of some_value

I also note that the forms' OnOpen event-code executes only the first
time that the forms are opened. How do I pass a new OpenArgs value to
formB after the first time?

I'm suspicious about where I've placed the close statements, but
neither order makes sense to me: if I close the form first, how does
the closed form execute the open statement that comes after the close
statement? ...but if I open the next form first, haven't I left the
environment where the close statement exists? And if my open
statements are failing, then why is *part* of the open statement
executing correctly [the some_where]?

--thelma
rephrasing an unanswered question

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[rkc]

Thelma Lubkin wrote:

I'm suspicious about where I've placed the close statements, but
neither order makes sense to me: if I close the form first, how does
the closed form execute the open statement that comes after the close
statement? ...but if I open the next form first, haven't I left the
environment where the close statement exists? And if my open
statements are failing, then why is *part* of the open statement
executing correctly [the some_where]?

Close the form second and all your problems are solved.
Code execution is "interrupted" by the opening form's OnLoad
and OnOpen events, but returns to the procedure that issued the
open statement.

As for why part of the open statement is working, who knows unless
you show the actual code instead of an outline. If you are referencing
the form you just closed it should throw an error.

[Thelma Lubkin]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

: Thelma Lubkin wrote:
:>
:> I'm suspicious about where I've placed the close statements, but
:> neither order makes sense to me: if I close the form first, how does
:> the closed form execute the open statement that comes after the close
:> statement? ...but if I open the next form first, haven't I left the
:> environment where the close statement exists? And if my open
:> statements are failing, then why is *part* of the open statement
:> executing correctly [the some_where]?

: Close the form second and all your problems are solved.
: Code execution is "interrupted" by the opening form's OnLoad
: and OnOpen events, but returns to the procedure that issued the
: open statement.

: As for why part of the open statement is working, who knows unless
: you show the actual code instead of an outline. If you are referencing
: the form you just closed it should throw an error.

I tried it that way and OpenArgs are still not updating. Also,
this order of open, close produces erratic behavior: it often
doesn't respond to clicking the Return button [a toggle switch]
in formB, but not always. I wasn't able to do enough testing
to pick up the pattern of good and bad returns.

Thanks for trying.

--thelma

[Thelma Lubkin]

Bas Cost Budde <b.*********@heuvelqop.nl> wrote:
: I only recently tried to pass a new OpenArgs (because the called form
: does something with it, of course) and failed. So, I created a public
: sub in the form (routines in a form are private by default) that does
: the settings. I can then call this from its own Open event, but the
: caller can also call it.

: So instead of

: docmd.openform theform, openargs:= some_value

: you can write

: docmd.openform theform
: forms!theform.Sync some_value

: inside the form module you have

: Public Sub Sync(theValue as Whatever)
: ' your code
: End Sub
Thank you. This does work. But not from the OnOpen event,
because that is called only the first time that the form opens.
I'm using the OnCurrent event and I will now have to start
asking about why my attempt to use RecordSetClone to reset
the bookmark for a child of formB fails. But that's for another
thread.

Actually, my OpenArgs parameter is
so simple that I was able to communicate it simply by declaring an
existing global variable in formA as Public and referring to it in
formB by its full formal name

I still am interested in getting the open, close, dance of the
two forms working
--thelma
: Bas Cost Budde, Holland
: http://www.heuveltop.nl/BasCB/msac_index.html

[rkc]

Thelma Lubkin wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote: : As for why part of the open statement is working, who knows unless
: you show the actual code instead of an outline. If you are referencing
: the form you just closed it should throw an error.

I tried it that way and OpenArgs are still not updating. Also,
this order of open, close produces erratic behavior: it often
doesn't respond to clicking the Return button [a toggle switch]
in formB, but not always. I wasn't able to do enough testing
to pick up the pattern of good and bad returns.

I'd really like to see your code because I cannot make what you say is
happening happen. You are doing something to make it happen. Identify
what that is and stop doing it.

A return toggle button? What's that about?

Post the actual code.

[Bas Cost Budde]

Try the Activate event of the form, maybe. That fires every time the
form comes up front.

I meant to call my public sub from the caller! There is no Open event
running then.

Why do you close formA in the first place? In the end, you want it to be
open, albeit with new values?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[dreadnought8]

The requirement that needs this type of solution has me intrigued!
Assuming there's no other way around it, the following alternative
might be a solution.
It uses predeclared identifiers instead of OpenArgs to directly
call public functions in each form, handing over control
and passing parameters at the same time.
(Invoking a public function like this automatically
opens the form).

To test:
Build two forms, formA and formB.
FormA has one command button to kick the process off,
named cmdCallFormB.

If you create the two forms, add the code below,
open formA and click the button, the two forms will
swap data back and forth, printing the progress
in the debug window, until it stops at an arbitrary
point in fnA.
Here is the code behind formA:

'----------------------------------
Public Function fnA(param1, param2)
Debug.Print "A got " & param1 & " " & param2 & _
";About to close and call formB"
DoCmd.Close acForm, Me.Name
If Len(param1) < 8 Then
Form_formb.fnB param1 & "A", param2 & "A"
Else
Debug.Print "finished"
Stop
End If
End Function
Private Sub cmdCallFormB_Click()
'The whole show starts here
Dim i
For i = 1 To 100
Debug.Print " "
Next i
Debug.Print "About to close and call form b with A1,A2"
DoCmd.Close acForm, Me.Name
Form_formb.fnB "A1", "A2"
End Sub
'----------------------------------
formB has just this code behind it:
'----------------------------------
Public Function fnB(param1, param2)
Debug.Print "B got " & param1 & " " & param2 & "; about to close and
call fnA"
DoCmd.Close acForm, Me.Name
Form_forma.fnA param1 & "B", param2 & "B"
End Function
'----------------------------------

HTH

Terry Bell
Melbourne Australia
Thelma Lubkin wrote:

formA determines some_where and some_value and issues

docmd.close ,Me
docmd.openform "formB", , ,some_where, , ,some_value

formB receives the correct some_where and some_value
After completing its work formB issues

docmd.close ,Me
docmd.openform "formA"

formA determines a new some_where and some_value and again issues

docmd.close ,Me
docmd.openform "formB", , ,some_where, , ,some_value

formB receives the correct some_where but no update of some_value

I also note that the forms' OnOpen event-code executes only the first
time that the forms are opened. How do I pass a new OpenArgs value to
formB after the first time?

I'm suspicious about where I've placed the close statements, but
neither order makes sense to me: if I close the form first, how does
the closed form execute the open statement that comes after the close
statement? ...but if I open the next form first, haven't I left the
environment where the close statement exists? And if my open
statements are failing, then why is *part* of the open statement
executing correctly [the some_where]?

--thelma
rephrasing an unanswered question

[rkc]

Thelma Lubkin wrote:

<snip all>

I do not see any reason to do battle with the simple way that
Access provides to do what you want to do.

Open the Agency form to a specific agency using:

strfilter = "agencyid = " & AgcID
DoCmd.OpenForm DoCmd.OpenForm "agency0toD", _
acNormal, , strFilter, _
acFormReadOnly, acWindowNormal, _
empID
DoCmd.Close , "frmChooseAgency"

In that order.
Call that from the combo box OnClick event.

Return to frmChooseAgency from the agency form using:

DoCmd.OpenForm "frmChooseAgency"
DoCmd.Close acForm, Me.Name

Check for a valid empId in openArgs in the agency form's
OnLoad Event. If one is found do your subform navigation
by calling a sub routine defined in the subform's code
module.

The only part of this that should be even a minor challenge
is getting the subform navigation right. First you have to
position the agency form on the agency the employee works
for. Then you have to navigate to that person in the subform.

Take baby steps. Get the opening and closing of the agency form
right. When that's working move on to the subform navigation
and don't change anything that's working.

[Larry Linson]

"Thelma Lubkin" wrote

Public AgcID, EmpKey As Long
Dim AgcCol, PCnt As Integer
Dim GString As String

Thelma, unless you are aware that AgcID and AgcCol are defined here as
Variants, you could be in for some surprises sometime. In VBA, each variable
must have its own "as Clause". My guess is that you intend, as is the case
in many other languages, but not in VBA, that AgcID be a Long, and AgcCol be
an Integer. If so, this should be rewritten as:

Public AgcID as Long, EmpKey As Long
Dim AgcCol as Integer, PCnt As Integer
Dim GString As String

I doubt that has anything to do with the problem you describe, but it's a
habit that you need to develop, lest you get some surprises later.

Larry Linson
Microsoft Access MVP

[Thelma Lubkin]

Larry Linson <bo*****@localhost.not> wrote:
: "Thelma Lubkin" wrote

: > Public AgcID, EmpKey As Long
: > Dim AgcCol, PCnt As Integer
: > Dim GString As String

: Thelma, unless you are aware that AgcID and AgcCol are defined here as
: Variants, you could be in for some surprises sometime.

Thank you. I, indeed, didn't know that. Can you recommend a
book for someone who prefers code to wizards but who isn't
so dedicated as to want to write the class for each button
from scratch. The few books I've seen seem to assume one or the
other of the two extreme positions.
--thelma
: Larry Linson
: Microsoft Access MVP

[Larry Linson]

The book that almost every experienced Access developer of my acquaintance
has and swears by (not at) is the "Access Developer's Handbook" by Litwin,
Getz, et al. There's an edition for every version of Access _except_ V 1
(developer stuff was kinda primitive) and 2003 (not much change from 2002).

Getz et al have also done a book just on VBA, but it is not particularly
Access-oriented. I didn't find it very useful, as I do little or no VBA for
Excel, Word, etc.

I used to recommend Dr. Rick Dobson's, "Programming Access <version>" for
people moving from power user to developer, but now I suggest you browse it,
too. It strongly emphasizes ADO, which knowledgeable Microsoft insiders no
longer recommend, and the Access ADP client to SQL Server. He writes well,
and is a good teacher, but the subjects may not be what you are looking for.
The Access 2000 version may be more useful than later ones.

Another book I like is "Access 2003 Inside Out" by John Viescas, from
Microsoft Press. Better browse it at your local bookstore, though, to see if
it has enough VBA.

There are book recommendations at the FAQ, http://www.mvps.org/access, too,
from knowledgeable Access folks.

Larry Linson
Microsoft Access MVP
"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> wrote in message
news:da**********@uwm.edu...

Larry Linson <bo*****@localhost.not> wrote:
: "Thelma Lubkin" wrote

: > Public AgcID, EmpKey As Long
: > Dim AgcCol, PCnt As Integer
: > Dim GString As String

: Thelma, unless you are aware that AgcID and AgcCol are defined here as
: Variants, you could be in for some surprises sometime.

Thank you. I, indeed, didn't know that. Can you recommend a
book for someone who prefers code to wizards but who isn't
so dedicated as to want to write the class for each button
from scratch. The few books I've seen seem to assume one or the
other of the two extreme positions.
--thelma
: Larry Linson
: Microsoft Access MVP

[Thelma Lubkin]

Larry Linson <bo*****@localhost.not> wrote:
: The book that almost every experienced Access developer of my acquaintance
: has and swears by (not at) is the "Access Developer's Handbook" by Litwin,
: Getz, et al. There's an edition for every version of Access _except_ V 1
: (developer stuff was kinda primitive) and 2003 (not much change from 2002).

: Getz et al have also done a book just on VBA, but it is not particularly
: Access-oriented. I didn't find it very useful, as I do little or no VBA for
: Excel, Word, etc.

: I used to recommend Dr. Rick Dobson's, "Programming Access <version>" for
: people moving from power user to developer, but now I suggest you browse it,
: too. It strongly emphasizes ADO, which knowledgeable Microsoft insiders no
: longer recommend, and the Access ADP client to SQL Server. He writes well,
: and is a good teacher, but the subjects may not be what you are looking for.
: The Access 2000 version may be more useful than later ones.

: Another book I like is "Access 2003 Inside Out" by John Viescas, from
: Microsoft Press. Better browse it at your local bookstore, though, to see if
: it has enough VBA.

: There are book recommendations at the FAQ, http://www.mvps.org/access, too,
: from knowledgeable Access folks.

Thank you. I'll look for these.
--thelma

: Larry Linson
: Microsoft Access MVP

[Thelma Lubkin]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:
: Thelma Lubkin wrote:

: <snip all>

: I do not see any reason to do battle with the simple way that
: Access provides to do what you want to do.

: Open the Agency form to a specific agency using:

: strfilter = "agencyid = " & AgcID
: DoCmd.OpenForm DoCmd.OpenForm "agency0toD", _
: acNormal, , strFilter, _
: acFormReadOnly, acWindowNormal, _
: empID
: DoCmd.Close , "frmChooseAgency"

: In that order.
: Call that from the combo box OnClick event.

: Return to frmChooseAgency from the agency form using:

: DoCmd.OpenForm "frmChooseAgency"
: DoCmd.Close acForm, Me.Name

: Check for a valid empId in openArgs in the agency form's
: OnLoad Event. If one is found do your subform navigation
: by calling a sub routine defined in the subform's code
: module.
Thanks very much to all of you who helped me to write my
first vba form [ie my first experience of the environment
altogether] Thank you:

Arno R, Bas Coste Budde, dreadnought, Larry Linson, rkc,
Tom Stiphout, Wayne Morgan. I'm sorry if I've forgotten anyone.

It works about the way I want it to now. My major stumbling
blocks were using a toggle where I needed only a simple command
button, and misunderstanding bookmarks so that I was trying to set a
parent-form's bookmark to that of its child.

--thelma

[Tony]

In a previous message you state:

I used to recommend Dr. Rick Dobson's, "Programming Access <version>" for
people moving from power user to developer, but now I suggest you browse
it,
too. It strongly emphasizes ADO, which knowledgeable Microsoft insiders no
longer recommend, and the Access ADP client to SQL Server. He writes well,
and is a good teacher, but the subjects may not be what you are looking
for.
The Access 2000 version may be more useful than later ones.

Can you expand on your statement about ADO and what is currently
recommended.
I am working through Dr. Dobson's "Programming Access 2003...".

Thanks

[Tony]

In a previous message you state:

I used to recommend Dr. Rick Dobson's, "Programming Access <version>" for
people moving from power user to developer, but now I suggest you browse
it,
too. It strongly emphasizes ADO, which knowledgeable Microsoft insiders no
longer recommend, and the Access ADP client to SQL Server. He writes well,
and is a good teacher, but the subjects may not be what you are looking
for.
The Access 2000 version may be more useful than later ones.

Can you expand on your statement about ADO and what is currently
recommended.
I am working through Dr. Dobson's "Programming Access 2003...".

Thanks