OpenArgs problem

[jmeek]

I have a calling form with the following code

Dim strTbl As String
Dim strTitle As String
Dim strSQL As String

strTbl = Me.cboRep.Column(0)
strTitle = Me.cboRep.Column(1)
strSQL = "SELECT tblSupp.SuppName, tblCurr.CurrName, * " _
& "FROM tblCurr INNER JOIN (" & strTbl & " INNER JOIN tblSupp " _
& "ON " & strTbl & ".SuppID = tblSupp.SuppID) " _
& "ON tblCurr.CurrID = " & strTbl & ".CurrID;"

DoCmd.OpenReport "rptList", acViewPreview, , , , strSQL & "," & strTitle

On the Report's OnOpen Event I have

Dim strStr1 As String
strStr1 = Left(Me.OpenArgs, InStr(Me.OpenArgs, ",") - 1)
Me.RecordSource = strStr1

And on the Report's On Activate Event I have

Dim strStr2 As String
strStr2 = Mid(Me.OpenArgs, InStr(Me.OpenArgs, ",") + 1)
Me.txtTitle = strStr2

This should work but it doesn't. The error states that the SQL statement
was not found. Of course, without the second concatenated argument
and Me.RecordSouce = Me.openArgs everything works fine.
Can anybody see where I have gone wrong

Thanks

 

[WayneRyan]

jmeeks,

I wasn't aware that OpenReport supported OpenArgs. I thought it was
only for forms.

Wayne

 

[jmeek]

OpenArgs

WayneRyan

Yes, both forms and reports are supported

 

[MarkK]

The InStr() function as you are using it returns the location of a different comma than the one you are expecting. Given the code you've supplied...

strStr1 = Left(Me.OpenArgs, InStr(Me.OpenArgs, ",") - 1)

...strStr1 will be assigned a value of "SELECT tblSupp.SuppName"

Consider using an unusual character as a delimiter...

[COLOR=Green]'use a tab character as a delimiter for openargs elements[/COLOR]
DoCmd.OpenReport "rptList", acViewPreview, , , , strSQL & [COLOR=DarkRed]vbTab[/COLOR] & strTitle

And if your version of Access supports Strings.Split(), then this might work in the report...

Private Sub Report_Open(Cancel as Integer)
  Dim var As Variant
[COLOR=Green]  'split the openargs string into a variant array
[/COLOR]  var = Split(Me.OpenArgs, [COLOR=DarkRed]vbTab[/COLOR])
[COLOR=Green]  'assign the first element to the record source
[/COLOR]  Me.RecordSource = var(0)
[COLOR=Green]  'assign the second element to txtTitle
[/COLOR]  Me.txtTitle = var(1)
End Sub

 

[jmeek]

OpenArgs

lagbolt

Brilliant. How did you spot that ?
However, you can't assign the bit

'assign the second element to txtTitle
Me.txtTitle = var(1)

on the OpenReport Event. This has to be
under the OnActivate, but it doesn't work

I have this under the OnActivate

Dim var As Variant
var = Split(Me.OpenArgs, vbTab)
Me.txtTitle = var(1)

Have I put everything that is
needed for the OnActivate ?

Jmeek

 

[MarkK]

OnActivate seems a little unusual, but I'm not a report wiz. If txtTitle is a textbox I'd make the assignment to it in the Format() event of the section in which it appears. If it represents the Caption of the report, I'd assign it directly to Me.Caption in the Form_Open() event, but I haven't tested any of this.

Glad to be of what help I could be though,
Cheers,
Mark