Form: Dynamic Query (1 Viewer)

evictme

Registered User.
Local time
Today, 05:10
Joined
May 18, 2011
Messages
153
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

Nifty Access Guy
Staff member
Local time
Today, 11:10
Joined
Jul 9, 2003
Messages
13,221
I'm wondering if Allen Brownes "Search Form" is what you are looking for:-

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:10
Joined
Feb 19, 2002
Messages
32,221
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

Registered User.
Local time
Today, 05:10
Joined
May 18, 2011
Messages
153
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:10
Joined
May 21, 2018
Messages
5,081

evictme

Registered User.
Local time
Today, 05:10
Joined
May 18, 2011
Messages
153
Already done and more.
Any chance you can send me a copy of that Qry Viewer you posted in that last post?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:10
Joined
Jul 9, 2003
Messages
13,221
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:-



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

evictme

Registered User.
Local time
Today, 05:10
Joined
May 18, 2011
Messages
153
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:-



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

Nifty Access Guy
Staff member
Local time
Today, 11:10
Joined
Jul 9, 2003
Messages
13,221
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
 
Last edited by a moderator:

evictme

Registered User.
Local time
Today, 05:10
Joined
May 18, 2011
Messages
153
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.
 
Last edited by a moderator:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:10
Joined
Jul 9, 2003
Messages
13,221

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:-


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

Registered User.
Local time
Today, 05:10
Joined
May 18, 2011
Messages
153

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:-


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.
 

Users who are viewing this thread

Top Bottom