Access 2003; Pass information between forms

If I pick an order in a form, I want to open the information in a different form by passing the OrderID.

I'm sure there are multiple methods in doing this.  any suggestions?

Thanks
OnsiteSupportAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
It pretty straight forward. Example:

1) Button on the Form to open the other form:

Private Sub btnOpenOrder_Click()
    DoCmd.OpenForm "Form1", , , , , , OpenArgs:=Me.OrderID
End Sub

2) Load event of form you are opening:

Private Sub Form_Load()
     Me.Filter = "[OrderID] = " & CLng(Me.OpenArgs)  ' assumes OrderID is Numeric
     Me.FilterOn = True
End Sub

This will open the form filtered on the OrderID from the other form.

mx
0
 
MINDSUPERBCommented:
The link below would be a good starting point:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26540269.html

Sincerely,
Ed
0
 
MINDSUPERBCommented:
0
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.

 
mbizupCommented:
You can also do this from a single event procedure such as a command button on the form used to open the other form, like this:

docmd.openform "YourFormName", WhereCondition:="[OrderID] =" & me.OrderID

If OrderID is Text:

docmd.openform "YourFormName", WhereCondition:="[OrderID] =" & chr(34) &  me.OrderID & chr(34)

The difference between filtering as in MX's post and using a WhereCondition like this is that the WhereCondition effectively reduces the recordsource to just the record(s) with the specified orderID.  To get back to all records or a different set of critera, you would have to close the form and reopen it with no criteria (or different criteria).

Filtering on the other hand, keeps the recordsource the same (all the records) but just displays those with the specified criteria.  You can turn the filter off, or change the filter without closing and reopening the form.

Either method will work; it all depends on preference and/or needs.

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Huh?  Filtering requeries the data source, filtering down to the specified records ... effectively the same as opening with a WHERE clause.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
In fact, from Help:

"The Filter property is a string expression consisting of a WHERE clause without the WHERE keyword. "

mx
0
 
mbizupCommented:
Yup you're right - I was sure I had tried mixing filtering with a Where clause with a total lack of success in the past.  

Just tried it again and both methods work exactly the same.

I almost never use filtering when opening a form myself (probably apparent from my last comment :-) ).  I generally just use the one-liner with the WhereCondition for any criteria.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
In general, OpenArgs is potentially more versatile ... as you can pass multiple values and use Split() in the Load event.
0
 
mbizupCommented:
The flexibility is definitely there and I use openargs for plenty of other things... I've just personally never needed it for filtering records (so it doesn't justify the extra code for me).

Just boils down to preferences and needs.
0
 
OnsiteSupportAuthor Commented:
Works Great!!!!
Is there a way to avoid a NULL error if I open the form without passing OpenArgs?  I also would like to open the 2nd form directly without being called from the 1st.
0
 
mbizupCommented:
You can add a check to see if openargs are present.  Mx can show you how to do that since it complements his method.

GIve this a try too - without the openargs:

docmd.openform "YourFormName", WhereCondition:="[OrderID] =" & me.OrderID

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Who are you asking the question to?
0
 
mbizupCommented:
Just to be clear, that should be tried without the load event code.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What Null condition ?
0
 
mbizupCommented:
mx,

With the code in the load event, the form won't open unless it is opened with the OpenForm statement with OpenArg criteria (invalid use of null when OpenArgs aren't specified).   For it to work in other contexts, the Load Event needs a null check.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Right.  However, no other context was mentioned per se.  And your method would not use that code in the Load event.

Anyway ..

To cover no OpenArgs ....

Private Sub Form_Load()
    If Nz(Me.OpenArgs, "")>"" Then
          Me.Filter = "[OrderID] = " & CLng(Me.OpenArgs)  ' assumes OrderID is Numeric
          Me.FilterOn = True
    End If
End Sub
0
 
OnsiteSupportAuthor Commented:
 In the load event:
Will this work?
 if isnull(Me.OpenArgs) then Me.Filter = "[OrderID] = " & CLng(Me.OpenArgs)
     Me.FilterOn = True
Else
 Just load the form and pray the end user doesn't want any more modifications to this application.
End if
0
 
OnsiteSupportAuthor Commented:
 In the load event:
Will this work?
 if isnull(Me.OpenArgs) then Me.Filter = "[OrderID] = " & CLng(Me.OpenArgs)
     Me.FilterOn = True
Else
 Just load the form and pray the end user doesn't want any more modifications to this application.
End if
0
 
OnsiteSupportAuthor Commented:
Thanks!!
0
 
mbizupCommented:
Offhand, That should do the trick.

The best test of course is always your own...

Does it run without error when you simply double click the form's name in the database window rather than calling it from code that passes openargs?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" Just load the form and pray the end user doesn't want any more modifications to this application."

ha ha ... that never happens!

mx
0
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.

All Courses

From novice to tech pro — start learning today.