The Solution to passing a parameter from one form to another

[MarcieFess]

My husband is a genius. He figured this out for me. It works PERFECTLY and I have incorporated it on almost every button and every form in this project!

Credit must be given to Cheryl who frequents these boards (I don't want to mention her last name or user name as I don't want to violate her privacy...Cheryl speak up and take credit if you'd like!
Create a button on the form that is PASSING the parameter to another form or to a report.

The code on this button should be:

'following is the error message boxes for this button on the form. I cannot take credit...Cheryl, who helped me tremendously, did most of this part of the process and wrote these error message boxes

Private Sub btnHMIS_Click() 'btnHMIS is the name of the button
On Error GoTo Err
If IsNull(Me.cboCompany) Then 'cboCompany is the name of the combo box passing the parameter
MsgBox ("Choose a Company!")
Me.cboCompany.SetFocus
Exit Sub
End If
If IsNull(Me.cboStore) Then 'cboStore is the name of the second combo box passing the parameter
MsgBox ("Choose a Store!")
Me.cboStore.SetFocus
Exit Sub
End If

'rptHMISReport is the name of the report this button is sending to Print Preview; StoreKey is the field that the report is going to be "filtered" on for lack of a better explanation. cboStore again is the combo box in question.
DoCmd.OpenReport "rptHMISReport", acViewPreview, , "[StoreKey] = " & Me.cboStore

ExitS:
DoCmd.Echo True
Exit Sub

Err:
'this error gets thrown if there is no data in the report AND the report must have the On No Data property set to CANCEL = True in the code window
'since you are going to filter the report now - if the user chooses a company or store that doesn't have records the report needs to be able to return this
If Err.Number = 2501 Then
MsgBox ("No records exist for this store/company!")
Else
MsgBox Error$
End If
End Sub

Now this next part is what my husband came up with to help me...I don't know if I just wasn't understanding it the way it was explained to me to do it another way...but this works a charm:

This is the code for the OnLoad part of the RECEIVING form or report:

Private Sub Form_Load()
Dim longID As Integer
If Not IsNull(Me.OpenArgs) Then
longID = CInt(Me.OpenArgs)
With Me.RecordsetClone
.FindFirst "[CompanyKey] =" & longID 'CompanyKey is the field for the parameter being passed in this case
If .NoMatch Then
MsgBox "Record Not Found!"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub


If anyone else is struggling with an issue like this...I hope this helps! It works for reports, too...now my reports are filtered perfectly and printing ONLY for the appropriate company/store!

 

[MStef]

Maybe your husband is a genius, but he have to learn more about Access.
Look at "Demo2FormsA20022003.mdb, (attachment, zip).
Open Form1 and try.
Look at VBA, (Form1, Form2, Report1).

 

[MarcieFess]

I understand what you're doing here...but I guess you don't realize that we're dealing with a large database that's divided several ways. The parameters that are passed from one form to another come from combo boxes, not user input. When the user makes a choice, the next form must present only the records that go with that choice.

Am I missing something?

 

[MStef]

Ok, I understand.