Solved: OpenArgs problem

[jmentor]

I have a calling form with the following code

[VBA]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[/VBA]

On the Report's OnOpen Event I have

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

And on the Report's On Activate Event I have

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

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

[Norie]

Is there actually an OpenArgs property for a report?

I've just checked help for OpenReport and it didn't have a OpenArgs argument.

I also checked OpenArgs and apparently it only applies to a Form object.

I'm running Access 2000.

[jmentor]

Norie

It does work with Reports. I've achieved that already. Its when
I insert additional arguments that it fails.

[Norie]

Like I said I'm running 2000, I've just checked and it looks like OpenArgs was added for reports in 2003.

What are you actually trying to do?

Could you not just use parameters?

[xCav8r]

jmentor,

I think you need to take a closer look at what you're doing. (Use your locals window and step through what's happening.) Your first comma is here: [vba]SELECT tblSupp.SuppName,[/vba]

So...

[VBA]strStr1 = "SELECT tblSupp.SuppName"[/VBA]

Don't separate your parameters in OpenArgs with a comma. That's a bad habit that leads to oversights like this. Why didn't you use a pipe like I had suggested? Or at least some other character that wouldn't be in a SQL statement? .

HTH

PS. Please use the nifty [VBA] tags when you post code. It makes it easier to read.

[jmentor]

xCav8r

You were spot on again.
Too many commas. Replaced the comma
with another delimiter and got it to work.

Yes, I saw the pipe method somewhere
else but not entirely sure how it works.

Thanks

[xCav8r]

Glad you're up and working.

Yes, I saw the pipe method somewhere else but not entirely sure how it works.

I mentioned using the pipe character in your other thread regarding the use of opening arguments. (See my post above for a link). Anyway, it's the name of a character. At least, it's the American word for this character: |

Use it like this:

[VBA]DoCmd.OpenReport "rptList", acViewPreview, , , , strSQL & "|" & strTitle [/VBA]