Search forms results not opening report in Preview

[Guest]

Can someone tell me what's wrong with this code? I have a search form where
you can either click to see results in query mode, or preview in a report...
I can see the query, but I cannot get the report to work.
Please help!


Private Sub cmd_PreviewPlanningReport_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strCountry As String
Dim strStructural As String
Dim strCategory As String
Dim strJurisdiction As String
Dim strBenefitType As String
Dim strCurrentStatus As String
Dim strHWContact As String
Dim strExternalContact As String
Dim strSQL As String
Dim stDocName As String
Set db = CurrentDb

If IsNull(Me.cboCountry.Value) Then
strCountry = " Like '*' "
Else
strCountry = "='" & Me.cboCountry.Value & "' "
End If

If IsNull(Me.cboCategory.Value) Then
strCategory = " Like '*' "
Else
strCategory = "='" & Me.cboCategory.Value & "' "
End If

If IsNull(Me.cboStructural.Value) Then
strStructural = " Like '*' "
Else
strStructural = "='" & Me.cboStructural.Value & "' "
End If

If IsNull(Me.cboCurrentStatus.Value) Then
strCurrentStatus = " Like '*' "
Else
strCurrentStatus = "='" & Me.cboCurrentStatus.Value & "' "
End If

If IsNull(Me.cboJurisdiction.Value) Then
strJurisdiction = " Like '*' "
Else
strJurisdiction = "='" & Me.cboJurisdiction.Value & "' "
End If

If IsNull(Me.cboBenefitType.Value) Then
strBenefitType = " Like '*' "
Else
strBenefitType = "='" & Me.cboBenefitType.Value & "' "
End If

If IsNull(Me.cboHWContact.Value) Then
strHWContact = " Like '*' "
Else
strHWContact = "='" & Me.cboHWContact.Value & "' "
End If

If IsNull(Me.cboExternalContact.Value) Then
strExternalContact = " Like '*' "
Else
strExternalContact = "='" & Me.cboExternalContact.Value & "' "
End If

If IsNull(Me.cboCurrentStatus.Value) Then
strCurrentStatus = " Like '*' "
Else
strCurrentStatus = "='" & Me.cboCurrentStatus.Value & "' "
End If


strSQL = "SELECT tbl_ideas_bank.* " & _
"FROM tbl_ideas_bank " & _
"WHERE tbl_ideas_bank.countryid " & strCountry & _
"AND tbl_ideas_bank.ideacategory " & strCategory & _
"AND tbl_ideas_bank.structural " & strStructural & _
"AND tbl_ideas_bank.ideajurisdiction " & strJurisdiction & _
"AND tbl_ideas_bank.hwcontact " & strHWContact & _
"AND tbl_ideas_bank.externalcontact " & strExternalContact
& _
"ORDER BY tbl_ideas_bank.ideadescription;"


DoCmd.OpenForm "rpt_STRAPbycountry", , acPreview

' DoCmd.Close acForm, Me.Name

Set qdr = Nothing
Set db = Nothing

' Debug.Print SQL
End Sub

 

[Guest]

Hi chickalina,

You said you were trying to open a report... but you have used openform
DoCmd.OpenForm "rpt_STRAPbycountry", , acPreview

also, you are building an SQL string, but not passing it to anything...
does your report require this or does it generate its own?

What about DoCmd.OpenReport "rpt_STRAPbycountry", acViewPreview,,WHERE

where WHERE is the where portion of your SQL string.

Damian.

 

[tina]

you don't need the complete SQL statement, only the WHERE clause itself
(minus the WHERE keyword). and btw, if the report is based on tbl_ideas_bank
only, or on multiple tables that do not include any identically named
fields, you don't need to keep repeating the table name in the SQL string.
try

strSQL = "countryid " & strCountry _
& " And ideacategory " & strCategory _
& "And structural " & strStructural _
& "And ideajurisdiction " & strJurisdiction _
& "And hwcontact " & strHWContact _
& "And externalcontact " & strExternalContact

DoCmd.OpenReport "rpt_ideasbycountry", _
acViewPreview, , strSQL

hth

 

[tina]

That worked GREAT!!! Thanks so much.

you're welcome.

Is there anyway to not list every ISNULL for every item in the search form
too?

well, if you mean can the SQL string be built without including as criteria
those fields where the search control on the form is null - then, yes it can
be. try

Private Sub cmd_PreviewPlanningReport_Click()

Dim strSQL As String

If Not IsNull(Me!cboCountry) Then
strSQL = "countryid = '" _
& Me!cboCountry & "'"
End If

If Not IsNull(Me!cboCategory) Then
strSQL = strSQL & " And ideacategory = '" _
& Me!cboCategory & "'"
End If

If Not IsNull(Me!cboStructural) Then
strSQL = strSQL & " And structural = '" _
& Me!cboStructural & "'"
End If

If Not IsNull(Me!cboJurisdiction) Then
strSQL = strSQL & " And ideajurisdiction = '" _
& Me!cboJurisdiction & "'"
End If

If Not IsNull(Me!cboHWContact) Then
strSQL = strSQL & " And hwcontact = '" _
& Me!cboHWContact & "'"
End If

If Not IsNull(Me!cboExternalContact) Then
strSQL = strSQL & " And externalcontact = '" _
& Me!cboExternalContact & "'"
End If

If Left(strSQL, 5) = " And " Then
strSQL = Right(strSQL, Len(strSQL)-5)
End If

DoCmd.OpenReport "rpt_ideasbycountry", _
acViewPreview, , strSQL

End Sub

note a few changes: first, you don't need all those variables; just keep
adding onto the strSQL variable. next, in VBA the default property of a
control is the Value property, so you don't need to reference that property
explicitly when assigning it to the variable. next, you're not using the db,
qdf and strDocName variables in the code you posted, or in the amended code
i posted, so i removed those from the procedure. and last, in your original
code, you created strBenefitType and strCurrentStatus variables, and set
their values with If statements - but didn't use either variable in the SQL
statement that you constructed. so i did not include the values from the
cboCurrentStatus and cboBenefitType controls in the solution posted above.

hth