Form Criteria Report Open Run-time Error 3075

[misscrf]

I get this error in my form command button control, in the on-click event:

Run-time error '3075':

|1 in query expression '|2'.

--------------------------

This is the code in that event:

Private Sub cmdExDetRptSel_Click()
Dim strDocName As String
Dim strcriteria As String
Dim strOpenArgs As String
Dim strRgroup As Integer
Dim strSQL As String

strDocName = "rptProjExDetail"

If Me.lstExceptionTypes.ItemsSelected.Count = 0 Then
    MsgBox "You have no exclusions selected, please select 1 or more to run for selected exclusions.", vbOKOnly, "Please Select Some Exclusions"
End If

strcriteria = "[Projectgroupkey] = '" & Me.txtProjectGroupKey.Value & "'"
strcriteria = strcriteria & _
BuildIn(Me.lstExceptionTypes, "[ExType]", "'")

If Me.chkRptByBatch = -1 Then
    strOpenArgs = 1
Else
    strOpenArgs = 0
End If

DoCmd.OpenReport strDocName, acViewPreview, , strcriteria, , strOpenArgs

[Forms]![frmReportExclusionsMenu].Visible = False

End Sub

The error highlights the line

DoCmd.OpenReport strDocName, acViewPreview, , strcriteria, , strOpenArgs

when I debug the strcriteria, it comes out like this:

[Projectgroupkey] = '123456ProjectABC' AND [ExType] In ('Corrupt File')

Anyone know what is going on?

Thanks!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

What is the data type of ExType?

Why aren't you setting the strOpenArgs to string/text rather than numbers?

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

sorry for delay in response. Thank you for responding. ExType is a string. I didn't know it would be an issue to have the openargs be a number. If that is an issue, I can certainly change it. The criteria is filtering the report. The open args is just meant to be a boolean, based on a checkbox. I'm trying to set up the form to allow the user to turn grouping on and off on a report.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

I am pretty sure the openargs property returns a string, but the argument is a variant.

The openargs property of the form determines the string expression specified by the OpenArgs argument of the OpenForm method that opened a form. Read/write Variant

So I always cast my openargs back, and not sure that is your problem.

But can you test this by making a new query based on the forms recordsource? Then add this to the where statement "[Projectgroupkey] = '123456ProjectABC' AND [ExType] In ('Corrupt File')". See if strcriteria is correct.

 

[misscrf]

The form doesn't have a record source, because it is just an unbound form for a reporting menu. I could do that though. I just added some message boxes to try to grab what is going where. If those don't work, I will set the form to the same query as the report, and then push the criteria there, to see what you are suggesting.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

Sorry for the confusion. I said form when I should have said report. I just want you to test the strcriteria that you build to make sure it is a valid where statement.
So use the reports recordsource as a basis for a query and try to add the where statement

WHERE [Projectgroupkey] = '123456ProjectABC' AND [ExType] In ('Corrupt File')

 

[misscrf]

Thanks. The criteria is working. It is another part that I am troubleshooting now. It is the open args. I switched it from being a number to the following:

If Me.chkRptByBatch.Value = -1 Then
    strOpenArgs = "Yes"
Else
    strOpenArgs = "No"
End If

Then in the on open of the report I have this:

If Me.OpenArgs = "Yes" Then
    Me.GroupLevel(0).ControlSource = "AssetBatch"
    Me.GroupLevel(0).SortOrder = True
    Me.Section(5).Visible = True
    Me.txtGroupException.Visible = True
    MsgBox "open args was yes"
Else
    Me.GroupLevel(0).ControlSource = "=1"
    Me.Section(5).Visible = False
    Me.txtGroupException.Visible = False
    MsgBox "open args was not yes"
End If

I have a msgbox for the openargs in the form on-click to see what it is before it gets passed, and one in the report on-open to see what gets passed. It says "Yes" before on open, when the form check box is checked, but empty for the report on-open, and goes into the Else of the 2nd code above. I pass open args back and forth to forms and reports all the time. I'm not sure why this one is being so difficult! lol

Thanks.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

Make sure option explicit is set in your form
could be as simple as something like

dim stOpenArgs
stOpenArgs = "Dog"
DoCmd.OpenReport strDocName, acViewPreview, , strcriteria, , st[b]r[/b]OpenArgs

So strOpenArgs would be an empty string in this case.
It does not look the case, but check.