Solved

Create query based on forms Recordsource property and filter (combined)

Posted on 2016-09-15
8
25 Views
Last Modified: 2016-09-15
I need to create a master query that will be used for exporting data.  This query will be based on the forms current RecordSource, which is easy enough to do.  However, it may also need to incorporate any filters that may be in place.  

Can anyone let me know a clean/accurate/safe way of doing this?
0
Comment
Question by:Andy Brown
8 Comments
 
LVL 6

Expert Comment

by:Anders Ebro (Microsoft MVP)
ID: 41799428
The simplest would be to work directly of the recordSET of the form. E.g. below:
'Get the recordset
   Dim rs As DAO.Recordset
   Set rs = Me.Form.RecordsetClone
   If Not rs.BOF Then rs.MoveFirst
'Create Excel with Late Binding
 Dim oExcel As Object
   Set oExcel = CreateObject("Excel.Application")
   Dim oWorkBook As Object
   Set oWorkBook = oExcel.Workbooks.Add
  Dim oWS As Object
  Set oWS = oWorkBook.Worksheets(1)
'Copy data
oWS.Range("a1").CopyFromRecordset rs

'Turn over to user
  oExcel.visible = True
 Set oExcel = Nothing

Open in new window


You can expand upon this to include the field names if you like.

I tried an alternative, to create a query and then apply the filter, but that went south pretty quickly, due to issues with resolving form parameters in the query.
0
 
LVL 6

Expert Comment

by:Anders Ebro (Microsoft MVP)
ID: 41799430
To include field names:
   Dim iCols As Integer
   For iCols = 0 To rs.Fields.Count - 1
    ows.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
   Next
   ows.Range(ows.Cells(1, 1), ows.Cells(1, rs.Fields.Count)).Font.Bold = True
   rs.MoveFirst
   ows.Range("A2").CopyFromRecordset rs
   ows.Application.visible = True

Open in new window

0
 

Author Comment

by:Andy Brown
ID: 41799448
Hi Anders - thanks for that, but it's not quite what I'm after.

We have an MS Access form, with a Recordsource based on an existing query called qryRecordSource (it may already has a WHERE clause as part of the query).  From here, I need to create a query say, qryExport (which is what I will be using to export data from).  If no form-filter exists, it's easy, I literally copy the query qryRecordSource to qryExport.

However, if the user has created a Filter (within the Access form), I need to add the filter criteria to qryExport (again via the WHERE clause).

I've done this before on an old version of my database, but it was complex and slightly cumbersome (in my eyes) and was wondering if there is tried and tested way of doing this,

Thanks for the help so far.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41799469
The extremely simple method is to read the filter setting of the form and apply that to the report:
    Dim Filter As String

    Filter = Me.Filter
    Reports("NameOfYourReport").Filter = Filter
    Reports("NameOfYourReport").FilterOn = (Filter <> "")

Open in new window

/gustav
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 13

Accepted Solution

by:
John Tsioumpris earned 500 total points
ID: 41799612
if you can keep the fields names short then you can use this code
Dim rs As Object
Dim strRecordSource As String
Set rs = Me.RecordsetClone
strRecordSource = rs.Name
If Len(Me.Filter) > 0 Then
If InStr(strRecordSource, "WHERE") > 0 Then
strRecordSource = Left(strRecordSource, Len(strRecordSource) - 1) & " AND " & Me.Filter
Else
strRecordSource = Left(strRecordSource, Len(strRecordSource) - 1) & " WHERE " & Me.Filter
End If
End If
Set rs = Nothing
MsgBox strRecordSource

Open in new window


The annoying thing is that rs.name can hold up to 256 chars....so it depends if you can use it effectively
0
 

Author Closing Comment

by:Andy Brown
ID: 41799622
Thank you John.  That's very similar to the method I have used previously - but looks like the best option (although, I may end up creating a second query (based on qryExport), with the filter added.

All the best and thanks again everyone for your help.
0
 
LVL 6

Expert Comment

by:Anders Ebro (Microsoft MVP)
ID: 41799640
Note that the method i proposed originally does include the filters, and only export the filtered records.

But if you DO want the querydef, you can try this: I ended up removing the form name references, as it was giving me grief in the export execution.

Dim qdf As DAO.QueryDef
On Error Resume Next
CurrentDb.QueryDefs.Delete "TempExport"
CurrentDb.QueryDefs.Delete "qdfExport"
On Error GoTo 0
Set qdf = CurrentDb.CreateQueryDef("TempExport", Me.RecordSource)
Dim qdfEX As DAO.QueryDef
Dim SQL As String
Dim WhereClause As String
WhereClause = Me.Filter
WhereClause = Replace(WhereClause, "[" & Me.Name & "].", "")
WhereClause = Replace(WhereClause, Me.Name & ".", "")
SQL = "SElect * from TempExport Where (" & WhereClause & ")"
Set qdfEX = CurrentDb.CreateQueryDef("qdfExport", SQL)

Open in new window

0
 

Author Comment

by:Andy Brown
ID: 41799702
Thanks Anders - a good option.

Currently, I think the solution that seems easiest (for me) is this:

* Save qryRecordSource as qryExport
* If a form filter exists create qryExportFilter - based on qryExport (but with filter added)
* When exporting - simply choose the relevant query as the source

It might not be pretty - but it will be easy to implement.

Thanks again for all of your help.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now