Using Open Arguments in Access Report

[mpfohl]

I can't quite seem to use open arguments correctly. I have access 2003
but I want to make an MDE file at the end which I believe means I want
to continue saving my database in access 2000 format.

when I click a button on my form I want it to pass a string into the
report it is opening up. I've dumbed down the code as much as I can
below to simply print the text being passed into the report but it
still won't work. If I change "cat" to 1 (or any other integer) it
works. if i change the code to '(Cancel as String)' it still doesn't
work. What am I missing?

the code I have for the form is:
Private Sub tempTestbutton_Click()
Dim stDocName As String
stDocName = "my reports name"
DoCmd.OpenReport stDocName, acPreview, , , , "cat"
' where cat is the temparary string i'm trying to test by passing
into the report
end sub

the code for the report is:
Private Sub Report_Open(cancel As Integer)
msgbox me.OpenArgs
End Sub

 

[Allen Browne]

Sounds like you are trying to pass an entire expression in OpenArgs.

You might be able to use Eval() to evaluate the string you passed in.

It certainly won't work if it contains the text:
Cancel as String
since that is not a valid expression and Access would not be able to
evaluate it.

If you need to pass in a range of values, pass a delimited list. Then use
Split() to parse it in Report_Open, and assign the values to the various
things.

Example:
strOpenArgs = "cat;dog;fish"

Private Sub Report_Open(Cancel As Integer)
Dim avarOpenArgs
Dim i As Integer
avarOpenArgs = Split(Me.OpenArgs, ";")
If IsArray(avarOpenArgs) Then
For i = LBound(avarOpenArgs) To UBound(avarOpenArgs)
Debug.Print Trim(avarOpenArgs(i))
Next
End If
End Sub

BTW, you need to use A2002/3 format (not A2000) in order to create an MDE in
A2003.

 

[mpfohl]

Thanks. In a similar yet more complicated problem, I want to pass a
table from my form to my report.

My form builds the SQL and generates a record set showing a few columns
of the records found. The user then uses a button to open the report
and I need to pass the 'found records' on to the report some how.
either by filtering the report against the results the form found or by
creating VB code SQL to join the report's query to the found records.

Basically, my form generates the results and the only thing those
results have in common with the record set used in the report is the
IDNumber column, the rest is distinct, so it isn't easy to just build
the report off of the form.

I hope that makes sense. Thanks again for any help you may have.

 

[Allen Browne]

OpenArgs is a string, so you can pass a string only (not a table or
recordset).

But if you have already created the SQL for the form, you can pass that as
the OpenArgs for the report, and assign it to its Recordset:

DoCmd.OpenReport "Report1", acViewPreview, , OpenArgs = Me.RecordSource

Private Sub Report_Open(Cancel As Integer)
If Me.OpenArgs <> vbNullString Then
Me.RecordSource = Me.OpenArgs
End If
End Sub

 

[mpfohl]

On second reading that question still doesn't seem clear so let me try
one more time.

Currently, my database works like this: my search form builds the SQL
based on multiple search criteria chosen by the user. The SQL then
dumps records into a table in my database. The report then opens and
one of the many joins in that report is to that table, thereby limiting
that report to the records found by the form.

However, I want multiple users to be able to use the database at once
so if two users are trying to run a search at the same time it won't
work because there is only one static table. So I want to change this
entire search process into VB code so that multiple searches can run at
once without interfering with each other.

Thanks

 

[Allen Browne]

Split the database.

Place the temp table in the front end.

Since each user has an independent front end, their searches do not
interfere with each other.

 

[mpfohl]

Simple yet effective. perfect, thank you.

Split the database.

Place the temp table in the front end.

Since each user has an independent front end, their searches do not
interfere with each other.