Controlling what fields get shown in a query/report

[lbaccess101]

This seems so basic but I'm drawing blanks.


I want to make a form where users can manually select what fields they want to be displayed in a resulting query. This is different from filtering. They may want to filter by color or place or field values, but then I want there to be option buttons or checkboxes for the user to control what associated fields to display...birthdate, name, parent....etc.... All I see in the query is the "show" checkbox. Can this be controlled at the form level?

thanks!!!

[pbaldy]

Well, it can be requested at the form level, not controlled. The query is easy, just have it return all fields. You can also use dynamic SQL and only have it return the requested fields. The report gets tricky. You can set control sources in the open event, but you'd run into issues of textbox widths being appropriate for some fields but not others (name wide, birth date not), formats being different (numeric, date, etc), and so on. You could create textboxes for all fields and then hide/unhide them as appropriate, but it would probably look funky (gaps). You can move the textboxes with code, but would have to keep track of which you've made visible, how wide they were, etc. It's doable, but not for the faint-of-heart. Search on dynamic reports and you may find examples. I've never bothered; I do a lot of dynamic filtering, but typically the fields are pretty static (other than crosstabs).

[Lena01]

Create a simple report

Applies To: Access 2016 Access 2013 Access 2010 Access 2007

You can create a variety of different reports in Access, ranging from the simple to the complex. Begin by thinking about your report's record source. Whether your report is a simple listing of records or a grouped summary of sales by region, you must first determine which fields contain the data you want to see in your report, and in which tables or queries they reside.
After you choose your record source, you will usually find it is easiest to create your report by using the Report Wizard. The Report Wizard is a feature in Access that guides you through a series of questions and then generates a report based on your answers.

[June7]

Find out from users what data output they want routinely and design queries/reports to meet those requirements. Catering to users to allow them to design queries and reports 'on the fly' is a coding nightmare. I have done this once and only to generate a custom query for export to Excel, not a report. Code used QueryDefs collection to modify query definition. Problem with this is this is a programmatic design change to the db. Would not work in an executable database.

[orange]

You might get some insight into "customized/customizable reports" from this article by Martin Green - Dynamic Reports.
I recommend you read through his article to get a better understanding of what is involved. As June pointed out - letting users make selections related to report output can involve very complex code and logic. You may decide to identify the 10 most needed/useful/popular reports and work along that approach.

Good luck.

[pbaldy]

Code used QueryDefs collection to modify query definition. Problem with this is this is a programmatic design change to the db. Would not work in an executable database.

I don't think that's accurate. I distribute accde's and most have code to modify query SQL using QueryDef (pass through queries). I don't think you can modify form/report design in that environment.

[June7]

Okay, thanks pbaldy. Case of making an unfounded assumption.

[lbaccess101]

You might get some insight into "customized/customizable reports" from this article by Martin Green - Dynamic Reports.
I recommend you read through his article to get a better understanding of what is involved. As June pointed out - letting users make selections related to report output can involve very complex code and logic. You may decide to identify the 10 most needed/useful/popular reports and work along that approach.

Good luck.

thank you Orange...
So I did find Green's page already. Very useful however, I'm curious about building a dynamic query, not a dynamic report. I see now that I may just use this dynamic report as a starting point because it seems most flushed out, I can possibly adapt it to my needs. I'm just curious, Allene Browne's whole building a query in VBA, saying how that's faster and more reliable got me trying to do that. However, I am trying to run now a SELECT query via and onclick event on a form. It's not executing of course because of the starting directions I've used...now I've learned only runs action queries when I use something like docmd.run sql. Any links on how to execute select queries from onclick events in VBA?

[June7]

Just Open (DoCmd.OpenQuery) saved Access query objects or reference as RecordSource for forms and reports then open form or report.

In VBA SELECT queries are used to set recordset objects.

[lbaccess101]

sorry but I have no idea what that means. If you have time, please elaborate.

what's wrong with this statement:
Private Sub Command24_Click()
Dim strQuery As StringstrQuery = "SELECT PatientList.CRID, PatientList.LastName FROM PatientList INNER JOIN TransplantList ON PatientList.MRN = TransplantList.MRN Where IIf([Forms]![frmqrydataA]![fratxtype]=1, (PatientList.CONSULTTYPE),First, IIf([Forms]![frmqrydataA]![fratxtype]=2, (PatientList.CONSULTTYPE),Second));"
DoCmd.RunSQL strQuery

[June7]

The post needs a carriage return after Dim strQuery As String.

It's a SELECT query, can't use RunSQL with a SELECT statement. Only action statements (DELETE, UPDATE, INSERT) can be 'run'.

Build and save a query object (you will see it in the Access Navigation Pane). Then VBA can open the object with DoCmd.OpenQuery.

Using SELECT statements to set and manipulate recordsets in VBA is a whole other adventure.