Form: Dynamic Query

[evictme]

Hello everyone,

I recently had a request from users to be able to build a custom query thru a Form interface that they can use and check off the options they want.

Not sure even where to start (besides building the form and adding checkboxes).

I was trying to find the best way to do this and im think maybe querydef? But im not sure how to even start.

Thankfully, its only 1 table - [Employee] and a collection of fields - [Status], [StatusDate], [Address], [Phone], [IDNo], , etc.

How can I create a subform/query that will change dynamically based on the corresponding boxes checked, without including, ALL the fields in the table?

Thank you for your help

 

[Uncle Gizmo]

I'm wondering if Allen Brownes "Search Form" is what you are looking for:-

Microsoft Access tips: Search criteria

Free sample database for Microsoft Access 2000 and later, demonstrating how to build criteria from many optional entries, and handle different field types, exact matches, partial matches, and ranges.

allenbrowne.com

 

[Pat Hartman]

Although building a WHERE clause on the fly for a form is common and doesn't affect the design of a form, you don't want to build the the SELECT clause on the fly because that impacts the entire form and its controls. However, if you are building a query that will select data for export to Excel or a CSV, then building both SELECT and WHERE clauses is fine.

Please tell us more about your objective.

 

[evictme]

I want a form that will allow the user to check the fields they want from a specific table and then click a button that will display the query and allow the user to export it to excel.

 

[jdraw]

Not clear of your objective, but you may get some ideas from this Dynamic Reports material from Martin Green.

 

[MajP]

Solved - Selected fields from a table/query to generate Report and export to Excel & PDF in MS Access

Hi Team, Please help me build this up! I have a Master DB Table with about 34 fields and I have specific requirements filtering out the data and export the fields to Excel or PDF. I have built the queries with the requirements of the criteria. The requirement is to create a form with ListBox of...

www.access-programmers.co.uk

Already done and more.

 

[evictme]

Solved - Selected fields from a table/query to generate Report and export to Excel & PDF in MS Access

Hi Team, Please help me build this up! I have a Master DB Table with about 34 fields and I have specific requirements filtering out the data and export the fields to Excel or PDF. I have built the queries with the requirements of the criteria. The requirement is to create a form with ListBox of...

www.access-programmers.co.uk

Already done and more.

Any chance you can send me a copy of that Qry Viewer you posted in that last post?

 

[Uncle Gizmo]

I want a form that will allow the user to check the fields they want from a specific table and then click a button that will display the query and allow the user to export it to excel.

With the new information you've provided, I wondered if this might be suitable?

If it is, you are welcome to a free copy you can download it from my website here:-

Show-Hide-Export to Excel - Nifty Access

Show/Hide & Export to Excel What started out as an answer to a question on Expert Exchange, took on a life of its own and became a pop-up form which allows you to select the columns you want to display in a datasheet. Now, this is not at all a groundbreaking technology! It's actually already

www.niftyaccess.com

Use coupon code:- voe5p1i to get a free copy.

 

[evictme]

With the new information you've provided, I wondered if this might be suitable?

If it is, you are welcome to a free copy you can download it from my website here:-

Show-Hide-Export to Excel - Nifty Access

Show/Hide & Export to Excel What started out as an answer to a question on Expert Exchange, took on a life of its own and became a pop-up form which allows you to select the columns you want to display in a datasheet. Now, this is not at all a groundbreaking technology! It's actually already

www.niftyaccess.com

Use coupon code:- voe5p1i to get a free copy.

Thank you! I have imported the columns frm and the necessary code into the button for my form, however, when the I click the button it gives me an error when calling the .fSetUp.

Any suggestions?

 

[Uncle Gizmo]

however, when the I click the button it gives me an error when calling the .fSetUp.

Any suggestions?

Please send me a copy of your database. Only include the form and table (or tables) that are necessary. Make sure that there is no confidential information. My email address is xx

 

[evictme]

Please send me a copy of your database. Only include the form and table (or tables) that are necessary. Make sure that there is no confidential information. My email address is xx

Sent.

 

[Pat Hartman]

I removed Tony's email address from the two posts.

 

[Uncle Gizmo]

Sent.

Hi evictme​

Unfortunately your database structure is not compatible with my code.

I had a a good look at your database, about an hour I reckon. You have a table with around 150 Fields. This is generally an indication of a design problem.

Many of your field names contain special characters like "/" "#" "-" It is not a good idea to use them in your field names, it makes VBA programming very difficult. See Microsoft help here for more information:-

Errors using special characters in Access databases - Microsoft 365 Apps

Lists the special characters to avoid using when you work with the database object names or the field names in all versions of Access.

docs.microsoft.com

In particular I draw your attention to to this statement from Microsoft

If the field name contains a number sign (#), you receive the following error message:
The expression you entered has an invalid date value.

A lot of your field names have spaces in, again, I refer you to Microsoft above, don't use spaces.

 

[evictme]

Hi evictme​

Unfortunately your database structure is not compatible with my code.

I had a a good look at your database, about an hour I reckon. You have a table with around 150 Fields. This is generally an indication of a design problem.

Many of your field names contain special characters like "/" "#" "-" It is not a good idea to use them in your field names, it makes VBA programming very difficult. See Microsoft help here for more information:-

Errors using special characters in Access databases - Microsoft 365 Apps

Lists the special characters to avoid using when you work with the database object names or the field names in all versions of Access.

docs.microsoft.com

In particular I draw your attention to to this statement from Microsoft

If the field name contains a number sign (#), you receive the following error message:
The expression you entered has an invalid date value.

A lot of your field names have spaces in, again, I refer you to Microsoft above, don't use spaces.

Thank you for your time and help. Yea, renaming fields and better design is on the "todo" list, for now, Im maintaining.