Query criteria defined by split form filter?

[stephenaa5]

Hello, all. Darn near done with the project, and the last reporting needs are causing trouble.

First, I have to pull together data from 2 databases of different types. (Quickbooks, Access). Figured that out.


Now for the reporting, I have a split form that the users are already accustomed to using in Access for a different purpose, so I thought I would use it as the "selector" for the reports. Sounded like a good idea. User filters down to the items on which he/she would like to see the report data. The problem is I cannot figure out how to get the criteria to pass from the form to the queries.

The form holds a key field of ControlNumber, which is a number.

The other data sources have fields which hold the same number, albeit as TEXT. These fields are used as the join criteria.

Any idea how I can use the selections as the criteria in a query?

Thanks,

Stephen.

[June7]

What do you mean by 'selections'? The results of the user filter actions?

The form and report have the same fields in RecordSource? Can pass the form's filter criteria to the report:

DoCmd.OpenReport "reportname", , , Me.Filter

[stephenaa5]

Yes, you can define the report that way, but I have yet to find a way to pass that definition on to the query. The only way I've found to do it is rather low rent. Create a report, use me.filter to constrain the report. Output the report to a spreadsheet, and join that spreadsheet into the rest of the query, thus defining data set criteria for the query.

It works well, but isn't self contained, and as I said feels sort of low rent.

[June7]

Don't understand, why do you have to "pass that definition on to the query"? Isn't the goal to filter report?

[uncletreetrunk]

Why use the split form? Can't you use an unbound form that calls a query to filter your results and then you could open the split form that they are used to seeing for final results.

Split forms are good for displaying table or query information in an easy to read format, but I don't think it's the best choice to filter results with. Just my opinion

[stephenaa5]

There are a whole lot of data fields and thousands of records. Decisions as to the next filter are made based on ehat the current selection set has yeilded.

The reason to pass the selection set to yet another query is because that query (actually multiple queries) drive multiple other reports.

Seems like my current method works fine, actually fairly quickly.

[uncletreetrunk]

Ahh I did not realize you were passing it to more queries/reports with so much data. Did you figure out a solution? I was thinking if you name the boxes in the split form and have a button that opens another form that loads with values from the split form. In that form you could have users alter entries? If you wanted or not open the form at all and have the button run a query based off of their record selection in the split form.

If you want the split form to have potential to run multiple queries you could have combo box with different queries and depending on which one user chooses it will run said query

[stephenaa5]

Yes, but i cannot get the selections made to pass cleanly to the query. The only way i have found is to use docmd.openrrport, me.filter to update a report, which can then be exported to a spreadsheet. That spreadsheet can then be linked to the database and used as a part of the queries. Inelegant, but it works. I wish there was a command to do a maketable out of a report, instead of creating a bogus spreadsheet.

Its all behind tge curtain, user doesnt see it, but definitely hokey.

[June7]

You can use QueryDefs collection to modify or delete or create query. Example:

Private Sub btnRun_Click()
If IsNull(Me.tbxFilter) Then
Me.tbxFilter = "SELECT * FROM ProjectRatesMainSub WHERE grading Like '*' " & GetMisc() & " ORDER BY projects.proj_num, grading;"
End If
Dim qdfUser As DAO.QueryDef
CurrentDb.QueryDefs.Delete ("UserQuery")
Set qdfUser = CurrentDb.CreateQueryDef("UserQuery", Me.tbxFilter)
DoCmd.OpenQuery "UserQuery"
End Sub