Open Report w/ Subreport based on Unbound Form

[bobbye_69]

Any help would be greatly appreciated.

I am trying to open a report (rptGMX-IRG) that includes a sub-report (srptRG) based on criteria that is set with two unbound fields on a form (frmGMXbook). This form has two dropdown boxes (cboGMX_No & cboMachSize) and a command button. The first dropdown box includes data located in the main form and the second dropdown includes data that is part of the sub-report.

I can get the report to open properly when I enter data into both fields on frmGMXbook.

:banghead:However, the problem I am having is the inability to have one or both fields be "Like '*'" and show all associated records.

Here is the code I have on the command button.

Private Sub GMX_Preview_Click()

Dim strCriteria As String
If IsNull(Me.cboGMX_No) Then
strCriteria = "[cboGMX_No] Like '*'"
Else
strCriteria = "[GMX_No] ='" & Me.cboGMX_No.Value & "'"
End If

' This is the section that works with the Machine size from the form.
If IsNull(Me.cboMachSize) Then
strCriteria = "[Machine Size] Like '*'"
Else
strCriteria = "[Machine Size] ='" & Me.cboMachSize.Value & "'"
End If

strCriteria = cboGMX_No.Value & "'*" & cboMachSize.Value & "*'"

DoCmd.OpenReport "rptGMX_Insert_w_RGs", acPreview, , strCriteria

DoCmd.Close acQuery, "qryRG_Numbers_via_Mach", acSaveNo

End Sub

 

[MarkK]

To select all records, remove all constraints, so SQL like . . .

SELECT Field1 FROM table

. . . selects all records. To select a subset of records, add constraints. So if your combo is null, do nothing.

Then, your subsequent code is overwriting previously written values in strCritieria. To compound, or concatenate, critieria, use code like . . .

If Not IsNull(Me.SomeConstraint) Then
[COLOR="Green"]   'we concatenate the next critieria, if present[/COLOR]
   strCriteria = strCriteria & "AND SomeConstraint = '" & Me.SomeConstraint & "' "
End If

Finally, before opening the report, show the criteria (in testing) and make sure it looks good . . .

Debug.Print strCritieria

Hope this helps,