Open report in a replicia with differing sql statement

[Guest]

I have the following, and it does work in the master, but we get an error in
the replicia that this is a read only db. Since I am using Access 2000, I
can't use the openargs for the me.recordsource in report_open. So what do I
do?

Dim stDocName As String
Dim varnumber As Variant
Dim sfilt As String
Dim lngLen As Long
Dim sfld As String
Dim SSqL As String
Dim QUERYNAME As String

sfld = Me.GRPSELECT.Column(3)
stDocName = "rptAGING_OPEN_BASE"
QUERYNAME = "qryAGE_ISSUE_BASE"

SSqL = "SELECT [qryAllOpenIssues].[" & sfld & "] AS field1, " & _
"Sum([qryAGE_ISSUES].[Current]) AS SumOfCurrent, " & _
"Sum([qryAGE_ISSUES].[Ninety]) AS SumOfNinety, " & _
"Sum([qryAGE_ISSUES].[OneEighty]) AS SumOfOneEighty, " & _
"Sum([qryAGE_ISSUES].[Over180]) AS SumOfOver180, " & _
"Sum([qryAGE_ISSUES].[Overyr]) AS SumOfOveryr " & _
"FROM [qryallOpenIssues] " & _
"INNER JOIN qryAGE_ISSUES " & _
"ON [qryallOpenIssues].[Issue Reference
Number]=[qryAGE_ISSUES].[Issue Reference Number]" & _
"WHERE ((([qryallOpenIssues].[Issue Status Name])='OPEN') AND "
& _
"(([qryAllOpenIssues].[txtISSUE_TYPE])<3)) OR " & _
"((([qryAllOpenIssues].[txtISSUE_TYPE]) Is Null)) " & _
"GROUP BY [qryallOpenIssues].[" & sfld & "]"

'Reports(stDocName).RecordSource = SSqL


CurrentDb.QueryDefs(QUERYNAME).SQL = SSqL

'If users choose the all records option
If Option69 = 1 Then
DoCmd.OpenReport stDocName, acPreview
Exit Sub
End If

'if users select records to filter by in the list box
For Each varnumber In List67.ItemsSelected
sfilt = sfilt & "'" & List67.ItemData(varnumber) & "',"
Next

'remove trailing comma, add field name, in operator and brackets
lngLen = Len(sfilt) - 1
If lngLen > 0 Then
sfilt = "[field1] in (" & Left$(sfilt, lngLen) & ")"
End If
DoCmd.OpenReport stDocName, acPreview, , sfilt

Exit_Command72_Click:
Exit Sub

Err_Command72_Click:
MsgBox Err.Description
Resume Exit_Command72_Click

End Sub

I saw an earlier post of Allen's that:

If you are using Access 2000 or earlier, you will need to use a public
string variable instead of OpenArgs.

I don't know what he meant by that and whether that would help in this case.

 

[Rod]

I haven't looked at the code you are using but the alternative to the
openargs is simple.

You need to create a module by selecting Modules in the database window and
clicking New.

In this you place a variable gstrOpenArgs

Dim gstrOpenArgs As String

Save the module - the name doesn't matter.

The variable gstrOpenArgs is now accessible in all parts of the program. If
you set it before you call the report its value can be accessed within the
report just like the OpenArgs value can be in forms in Access 2000.

Instead of using OpenArgs use gstrOpenArgs.

Rod.

I have the following, and it does work in the master, but we get an error
in
the replicia that this is a read only db. Since I am using Access 2000, I
can't use the openargs for the me.recordsource in report_open. So what do
I
do?

Dim stDocName As String
Dim varnumber As Variant
Dim sfilt As String
Dim lngLen As Long
Dim sfld As String
Dim SSqL As String
Dim QUERYNAME As String

sfld = Me.GRPSELECT.Column(3)
stDocName = "rptAGING_OPEN_BASE"
QUERYNAME = "qryAGE_ISSUE_BASE"

SSqL = "SELECT [qryAllOpenIssues].[" & sfld & "] AS field1, " & _
"Sum([qryAGE_ISSUES].[Current]) AS SumOfCurrent, " & _
"Sum([qryAGE_ISSUES].[Ninety]) AS SumOfNinety, " & _
"Sum([qryAGE_ISSUES].[OneEighty]) AS SumOfOneEighty, " &
_
"Sum([qryAGE_ISSUES].[Over180]) AS SumOfOver180, " & _
"Sum([qryAGE_ISSUES].[Overyr]) AS SumOfOveryr " & _
"FROM [qryallOpenIssues] " & _
"INNER JOIN qryAGE_ISSUES " & _
"ON [qryallOpenIssues].[Issue Reference
Number]=[qryAGE_ISSUES].[Issue Reference Number]" & _
"WHERE ((([qryallOpenIssues].[Issue Status Name])='OPEN') AND "
& _
"(([qryAllOpenIssues].[txtISSUE_TYPE])<3)) OR " & _
"((([qryAllOpenIssues].[txtISSUE_TYPE]) Is Null)) " & _
"GROUP BY [qryallOpenIssues].[" & sfld & "]"

'Reports(stDocName).RecordSource = SSqL


CurrentDb.QueryDefs(QUERYNAME).SQL = SSqL

'If users choose the all records option
If Option69 = 1 Then
DoCmd.OpenReport stDocName, acPreview
Exit Sub
End If

'if users select records to filter by in the list box
For Each varnumber In List67.ItemsSelected
sfilt = sfilt & "'" & List67.ItemData(varnumber) & "',"
Next

'remove trailing comma, add field name, in operator and brackets
lngLen = Len(sfilt) - 1
If lngLen > 0 Then
sfilt = "[field1] in (" & Left$(sfilt, lngLen) & ")"
End If
DoCmd.OpenReport stDocName, acPreview, , sfilt

Exit_Command72_Click:
Exit Sub

Err_Command72_Click:
MsgBox Err.Description
Resume Exit_Command72_Click

End Sub

I saw an earlier post of Allen's that:

If you are using Access 2000 or earlier, you will need to use a public
string variable instead of OpenArgs.

I don't know what he meant by that and whether that would help in this
case.