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
Damian S - 12 Dec 2006 02:36 GMT
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.
> 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...
[quoted text clipped - 93 lines]
> ' Debug.Print SQL
> End Sub