OpenArgs On Form_Load not triggered

I have two forms which I navigate back and forth between then viewing associated records.  I use docmd.openform with the openargs being the ID of the record I want to goto.  In the load event for each form I use this code to get to the record I need:

If Not IsNull(Me.OpenArgs) Then

  Set rs = Me.RecordsetClone
  rs.FindFirst "SDVN_NUM = '" & Me.OpenArgs & "'"
      If Not rs.NoMatch Then
      Me.Bookmark = rs.Bookmark
    End If
End If

This works great the first time, however when both forms are opened the load event is not triggered and it does not perform navigation.  I would like to solve this without having to close the forms in between calls. Just wondering how others handle this type of situation?

LVL 11
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The event you want is Form_Activate

Create a normal module, say basGlobal, containing:

    Global gstrArgsForFormA As String

Whenever you need the form A, use:

    gstrArgsFormFormA = "cboID;123"   ' or whatever you would feed as OpenArg
    Docmd.OpenForm "FormA"

In Form A, use:

Private Sub Form_Activate()

    If gstrArgsForFormA <> "" Then
        ' use the argument here
        gstrArgsForFormA = ""   ' (reset)
    End If

End Sub

This works, I use it all over the place... :)
if you are not closing and disposing  of the form (Set Form = Nothing) then the form, once loaded, stays in memory, and Form_Load is NOT called again.  The form is displayed (.Visible = True), but NOT re-loaded.  That is why Form_Load is the wrong place.  When .Visible = True occurs, the Form_AcTivate is then called (the form has been 're-activated')

Not quite, AW

Form_Activate is the "On Got Focus" of forms. It has little to do with .Visible (an unvisible form can't get the focus).

Also "set Form = Nothing" has no clear meaning in this context. It's more confusing than helpful, imho.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BillPowellAuthor Commented:
Good suggestion harfang.  My only workarounds previously was to ensure I closed the calling form so that the onload would get triggered again when I needed that form again.  I have used a modified version of your method in the past, but had always thought of it as less than ideal solution because of having to resort to using a global variable.  Now that I see others use it, I guess it cant be that bad.
Well, ok, if you want the purist's version...

Declare a public string in FormA, as in:

    Public ActivateArgs as String

Use any of the "FormIsOpen()" functions (there is one in Access Help, btw), and then go...

    If FormIsOpen("FormA") Then
        With Forms!FormA
            !ActivateArgs = "xxxxx"
        End With
        DoCmd.OpenForm "FormA", OpenArgs:="yyyyy"

Now you need to check Me.OpenArgs in Form_Open *and* Me.ActivateArgs in Form_Activate....
It it really better?

Now the really, really, really object oriented approach...
(provided that your form isn't a dialog box, which seems not to be the case)
Create a public *method*!!! In FormA:

Public Sub JumpToRecord(pstrKey)
   ' do your stuff here
End Sub

Then you can...

    DoCmd.OpenForm "FormA"

Well, there you go!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sorry, in the last line I meant:
   Forms!FormA.JumpToRecord Me.strMyKey
although the bracketed version should have worked in that particular case...
BillPowellAuthor Commented:
Thanks for the extra tidbit.  Im thinking Ill stick to the global variable option.  I might add a custom property to write which form was the calling form.  That way I can opt out of navigating to the record if the form was not called by the last form that modified the global variable.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.