export query

[slimjen]

All using ms 2010. I have a form with unbound fields to filter to view report. The form uses a query and the report uses only a few fields from the query. This works fine. Now I want to be able to export the query with all the fields based on the filter from the form to excel. All I have been able to export is the fields from the report. Can someone help me export all the fields in the query but only the records from the filtered form?

Heres the code I used for the report:

Private Sub cmdExport_Click()
On Error GoTo Err_cmdExport_Click
Dim strSQL As String, intCounter As Integer
Dim db As Database, rs As Recordset
Dim ctl As Control, strname As String, strnewquery As String
Dim stDocName As String
Dim strRptSel As String
Dim stMessage 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 & "[" & ctl.Name & "] " & " like " & Chr(34) & ctl.Value & Chr(34) & " And "
      End If
      End If
     Next ctl
      ' Set the value of the parameter.
    
    If Me.cboWEdateFrom & vbNullString <> "" And Me.cboWEdateTo & vbNullString <> "" Then
        strSQL = strSQL & ("[Week Ending] BETWEEN #" & cboWEdateFrom & "# And #" & cboWEdateTo & "# And " & "'")
   End If
   
   
   strnewquery = "Select qryQAReport.* FROM qryQAReport"
    If strSQL <> "" Then
        strSQL = Left(strSQL, (Len(strSQL) - 5))
        strnewquery = strnewquery & " WHERE " & strSQL & ";"
    End If
    Debug.Print strnewquery
    ' Create the recordset
    Set rs = db.OpenRecordset(strnewquery)
    If rs.RecordCount > 0 Then
     DoCmd.OpenReport "rptQAReport", acViewPreview, , strSQL
     DoCmd.Close acForm, "frmReportbuilder"
    Else
        MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
        Exit Sub
    End If

Exit_cmdExport_Click:
        Exit Sub
Err_cmdExport_Click:
    Select Case Err.Number
        Case 2501 'OpenQuery action was cancelled
            Resume Exit_cmdExport_Click
        Case Else
            MsgBox Err.Description
            Resume Exit_cmdExport_Click
            Resume
        End Select
End Sub

It's from the cmdPreview and now I am calling it cmdExport so I can export the filtered data to the query in excel.


Thanks

[June7]

Open a query or report that has all the desired fields included in its RecordSource.

Query object can be filtered by referencing the form controls as criteria parameters.

Report object can be filtered by the method you are already using in the posted code.

[slimjen]

I am not following. Are you saying I need to change my search form's recordsource? Can I use the same search form? Can you show me an example to get me started? I thought I could use the same form. Thanks for your reply

[slimjen]

Ok. I think I got it. You mean reference the query filter in the qry criteria like:

[form]![frmReport]![txtJobTitle]

When it opens the save dialog box comes up and saves the qry with nothing.. It also is prompt me for

[form]![frmReport]![TXT_AlphaName

Am I not suppose to put this in all the criteria of the query?
Thanks

[June7]

Almost, try:

Forms!frmReport!txtJobTitle

Review http://datapigtechnologies.com/flash...earchform.html

[slimjen]

I did everything the video showed me and I still get prompted for the criteria. What can be wrong? This is the way the author suggested I put in the query criteria field of each searchfield.

Like [Forms]![frmReportbuilder]![Dept] & "*"

[June7]

The technique works when applied properly. I don't know your db. Is Dept the name of textbox on form?

[slimjen]

Yes it is. I don't know what is the problem. I'll try again.