Multiple OpenArgs

R

rc-alex

Registered User.
Local time
Today, 07:55
Joined
Apr 29, 2011
Messages
106
Sending:

DoCmd.OpenReport "rptResults", acViewPreview, , , acDialog, strPRC = "Me.cbo1ID|Me.cbo2ID|Me.cbo3ID"

Open event:

Private Sub Report_Open(Cancel As Integer)
varSplitString = Split([strPRC], "|")
Me.[EngagementID].Value = varSplitString(0)
Me.[LocationID].Value = varSplitString(1)
Me.[ProviderID].Value = varSplitString(2)
End Sub

Error: "Access cannot find the field "|1" referenced in your expression."
The VarSplitString line is highlighted in DeBug.

Everything I hover over "has no value" except the right of the equals signs in the bottom 3 lines, which have "type mismatch"?
 
To use OpenArgs you pass in a string, and that string can then be referenced in the form's code using the OpenArgs property of the form.
What I guess you are trying to do is ...
Code: 
[COLOR="Green"]'calling code
'concatenates the values of the cbo's delimited by the "|"[/COLOR]
DoCmd.OpenReport "rptResults", acViewPreview, , , acDialog, Me.cbo1ID & "|" & Me.cbo2ID & "|" & Me.cbo3ID

[COLOR="Green"]'form open[/COLOR]
Private Sub Report_Open(Cancel As Integer)
  varSplitString = Split([COLOR="DarkRed"][B]Me.OpenArgs[/B][/COLOR], "|")
  Me.[EngagementID].Value = varSplitString(0)
  Me.[LocationID].Value = varSplitString(1)
  Me.[ProviderID].Value = varSplitString(2)
End Sub
hth,
Mark
 
Mark,

Thanks a lot! But what would you put in Me.OpenArgs? Would that not be
varSplitString = Split(strPRC, "|")
 
DoCmd.OpenReport "rptResults", acViewPreview, , , acDialog, strPRC = "Me.cbo1ID|Me.cbo2ID|Me.cbo3ID"

as you originally had in the open event.

Then you would use the code already posted
Private Sub Report_Open(Cancel As Integer)
Dim varSplitString as Variant
varSplitString = Split(Me.OpenArgs, "|")
Me.[EngagementID] = varSplitString(0)
Me.[LocationID] = varSplitString(1)
Me.[ProviderID] = varSplitString(2)

End Sub
 
The OpenArgs parameter of DoCmd.OpenForm is a string. The string you put in there is made available to the form, when the form opens, in the form's OpenArgs property. Consider...
Code: 
[COLOR="Green"]'calling code[/COLOR]
DoCmd.OpenForm "SomeForm", , , , , , "This is OpenArgs"
[COLOR="Green"]'form code[/COLOR]
Private Sub Form_Open(Cancel As Integer)
  Debug.Print Me.OpenArgs
End Sub
This writes the string, "This is OpenArgs" to the immediate pane when the form opens.
To restate: A single string is passed to the form in the OpenArgs parameter of the DoCmd.OpenForm command. The form then exposes that string to it's own code in its OpenArgs property.
Any variable you have in your calling code should remain out of scope in respect to the form, or any other procedure you may call.
Make sense?
Cheers,
 
That does make sense, I think, but I'm not sure what to put in for the string. Wouldn't the string that is constructed be different for every combination of OpenArgs that are sent by the origin form? Don't I need to use a variable of sorts?
Thanks
 
The OpenArgs is actually a Variant.

Passed as per Lagbolt’s method:-
DoCmd.OpenReport "rptResults", acViewPreview, , , acDialog, Me.cbo1ID & "|" & Me.cbo2ID & "|" & Me.cbo3ID

and:-
Code: 
Private Sub [b]Detail_Format[/b](Cancel As Integer, FormatCount As Integer)

    Me.[EngagementID] = Split(Me.OpenArgs, "|")(0)
    Me.[LocationID] = Split(Me.OpenArgs, "|")(1)
    Me.[ProviderID] = Split(Me.OpenArgs, "|")(2)

End Sub

Chris.
 
Thanks. That lets it open the report now without trouble. However, it doesn't seem like the openArgs are being passed. When I view the report, the first ID is always the same no matter what I set the combo boxes and the other two are always empty. And it lets me scroll through the report for all records. Not just one. Hmm.
 
Try isolating the problem to one test at a time:-

DoCmd.OpenReport "rptResults", acViewPreview, , , acDialog, 1 & "|" & 2 & "|" & 3

Do you get the passed arguments, Yes/No?

Yes = next test
No = fix
 
No, for some reason the fields in the report are still blank :(

Private Sub cmdProviderReportCard_Click()
DoCmd.OpenReport "rptProviderReportCard", acViewPreview, , , acDialog, 6 & "|" & 8 & "|" & 12
End Sub


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.[EngagementID] = Split(Me.OpenArgs, "|")(0)
Me.[LocationID] = Split(Me.OpenArgs, "|")(1)
Me.[ProviderID] = Split(Me.OpenArgs, "|")(2)
End Sub
 
What are EngagementID, LocationID and ProviderID?

Are they fields in the Report’s recordset?
Are they textboxes bound or unbound visible or invisible?
What section of the report are they in?
 
The are in the report's recordset. They are comboboxes visible, but will be invisible. They are in the report header. The report still loads, but as I scroll through the pages, the three values change accordingly. I should only be able to see the one page of results corresponding to the OpenArg.
 
Change the format event to:-
Code: 
Private Sub [b]ReportHeader_Format[/b](Cancel As Integer, FormatCount As Integer)

    Me.[EngagementID] = Split(Me.OpenArgs, "|")(0)
    Me.[LocationID] = Split(Me.OpenArgs, "|")(1)
    Me.[ProviderID] = Split(Me.OpenArgs, "|")(2)

End Sub
 
I would scrap the OpenArgs and use a where clause to open the report.
Build the where clause something like this on the form where the user chooses the engagement, location and provider:

Dim strWhere As String
strWhere = "EngagementID = " & Me.EngagementID
strWhere = strWhere & " AND LocationID = " & Me.LocationID
strWhere = strWhere & " AND ProviderID = " & Me.ProviderID
Debug.Print strWhere

DoCmd. OpenReport "ReportName", , , strWhere
 
I changed the format event but it didn't work. Sorry you lost me when we switched gears to the where clause. Make that the only code on the cmd button to open the report? No open event on the report?
 
Did you try Jeanette's code as in post #14? Change the report name to your report name.

You may also want to post the SQL of the Query that the Report is using.
(Getting ahead of myself here but I have heard that Reports in Access 2007 can cause severe flicker if there is no record when opened.)

First try Jeanette's code.
 
Thanks. What I'm asking is, what do I do with Jeanette's code? I assume I totally remove the open event from the form? (getting rid of openArgs). Where do I place her code? Sorry, where events are new to me.

Thank you.
 
Place Jeanette's code in your original Sending: section.
 
THANKS to you both!

Since the three ID's are related (yielding the report), I think requerying each of the other two boxes based on whatever one the user updates is the best way to ensure avoiding blank reports? (already tested - a blank report comes out if no record exists)

Although, doing this might not work - if the user selects the right 3 IDs, prints a report, and then just changes one of the ID's, I wonder what could happen. Well, if this is the right approach, I'll test and we'll see :)

Thanks.
 
You must log in or register to reply here.

Similar threads

C
Select Case if true
Replies
11
Views
732
Pat Hartman
P
C
Solved Passing values from form to report
Replies
5
Views
782
ClaraBarton
C
M
Solved Creating individual reports and send to multiple customers via email
Replies
13
Views
1,641
Momma
M
mike60smart
Solved Printing Labels
2
Replies
38
Views
2,475
Pat Hartman
P
R
e.mail all "assigned to"
Replies
1
Views
774
bastanu
bastanu

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom