Results 1 to 5 of 5

Thread: report builder

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: report builder

    I have a form in a database with unbound fields. I would like my users to select from 1 or multiple unbound fields and click a button that would give them a report based on their selections. I've gotten this far with help from other sources but I still need some guidance. Can someone please look at my code and tell me why am I getting the error: "too few parameters. expected 1"

    Code:
    Private Sub cmdPreview_Click()
    On Error GoTo Err_cmdPreview_Click
    
    Dim strSQL As String, intCounter As Integer
    Dim ctl As Control, strname As String, rs As Recordset, Db As Database, strnewQuery As String
    Dim strRptSel As String
    Set Db = CurrentDb
    
    
          'Build SQL String
          For Each ctl In Me.Form
          If ctl.Tag = "input" Then
          'strname = "me." & ctl.Name
          If ctl.Value > "" Then
          strSQL = strSQL & "txt.AlphaName " & " like " & Chr(34) & ctl.Value & Chr(34) & " And "
          End If
          End If
        
         Next ctl
    
    
         If strSQL <> "" Then
              If strSQL <> "" Then
              strSQL = Left(strSQL, (Len(strSQL) - 5))
              
            ' Set the value of the parameter.
            strnewQuery = "Select qryOccurences_All.*, qryOccurences_All.Txt_AlphaName FROM qryOccurences_All WHERE " & strSQL & ";"
              Debug.Print strnewQuery
              
            ' Create the recordset
              Set rs = Db.OpenRecordset(strnewQuery)
              If rs.RecordCount > 0 Then
              Else
              MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
              Exit Sub
              End If
           Else
            DoCmd.OpenReport "rptOccur_All", acViewPreview
            DoCmd.Close acForm, "frmReportbuiler"
            Exit Sub
          End If
          End If
          
           DoCmd.Minimize
     
    DoCmd.OpenReport "rptOccur_All", acViewPreview, , strSQL
    DoCmd.Close acForm, "frmReportbuiler"
    
    Exit_cmdPreview_Click:
        Exit Sub
    
    Err_cmdPreview_Click:
        Select Case Err.Number
            Case 2501 'OpenReport action was cancelled
                Resume Exit_cmdPreview_Click
            Case Else
                MsgBox Err.Description
                Resume Exit_cmdPreview_Click
                Resume
            End Select
    
    End Sub
    Thank you

  2. #2
    Join Date
    Oct 2003
    Posts
    21
    That error, more times than not, indicates bad sql not missing items.

    What is the result of
    Debug.Print strnewQuery?

    I suspect you will find that there is an error in the sql that is not readily visible when you are formatting the query. Paste the results of
    Debug.Print strnewQuery
    into a new query and see if you can get a better error message.

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    This is what is in the immediate window. I pasted in a new query and it ran with the results I was looking for. What's wrong? Would I have to use a where clause for each unbound field?


    Code:
    Select qryOccurences_All.*, qryOccurences_All.Txt_AlphaName FROM qryOccurences_All WHERE txt_AlphaName  like "Roberts - Little, Connie";

  4. #4
    Join Date
    Mar 2006
    Posts
    163
    What's txt.AlphaName?

    Is it a field in the table?

  5. #5
    Join Date
    Jul 2004
    Posts
    214
    Yes. it should be txt_alphaname Now I'm getting the error "type mismatched". What am I doing wrong. please help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •