User Form To Allow User To Select Query Output Fields

[mdray00]

Hi Guys,

I have an access database and I have a used a form so that the user can input variables into a query.

What I would like is to have a form that has all of the fields on the query so that the user can tick or untick fields that they would like to see in the query output.

I am guessin that in the VBA code somewhere it would be something Query name then query field and then show or hide
but I dont know that syntax. Could any1 help with this.

Many thanks

Mike

 

[DCrake]

The problem is not building up the sql statement that represents the query but once you have done so the tricky bit is being able to apply filters to the said query. This is because you will not know which fields to apply the filter to as the fields are dynamically created.

Also where do you intend displaying this query to the user?

As a subform
Exported to Excel
In a report
Contents of a list box

Dunno?

David

 

[mdray00]

I have a query made. In the criteria options I have it so that it takes data from a form i have created. This works fine. It is just a select query.

In the query design view you can click the tick so that the field is not shown.

I want a form with tick boxes. So that if the box is ticked the the corrisponding field is shown.

I can work an "if" statment so that it says if box = true then .

I dont know how to say that the field should be hidden.

Thanks

 

[DCrake]

You can use the column.Hidden = True syntax

or Column.Width = 0 approach

David

 

[mdray00]

Thanks alot.

1 thing though.

If for example i have 3 Columns

Name
ID Number
Limit

And I dont want the Limit Column to be shown what would the syntax be?

If the query was called eg. ShowName

How would I say that I want the field "Limit" in the "ShowName" query to be hidden.

Thanks

 

[DCrake]

How is the user viewing the query? This is going back to my earlier post. Its ok establishing what to hide and what to show, but next you need to establish how you are going to display the end result.

This will determine how the columns are shown/hidden

David

 

[mdray00]

Sorry the end result will just be a select query. So that the user can look though it or export to excel if they require.

I will not need to be in a report or table etc.

 

[mdray00]

I had a look around and it some1 sugested something like this

where

SRT Variable Query (Buyer 1)] = my query name
EID = the column name

Me.[SRT Variable Query (Buyer 1)].Queries![EID].ColumnHidden = True

But now it says

" Microsoft Access can't find the field "|" referred to in your expression."

Have I got the syntax wrong?

Thanks

 

[HAMMAMABUARQOUB]

As you try to make a dynamic query allow me to suggest this,,on each filed you tich.. change the SELECT statement that the query is based on...example...
1- if you have the fields ID, Nm, Salary and you wan to check them into or out of the query using adifferent selcet statement each time
2- or in the query design put a condition on the fields needed so that they get they are included if they are selected already in the form
regards

 

[mdray00]

Hi sorry im not new to this but not great at it either. lol

I cannot use different select quiries every time as there are over 100 fields in this query to to have every posibility as a select query would take ages lol.

I was kind of hoping that I could put check boxes on a form.

And then use the on change event so that if they tick a box it will run a peice of code. I can do this bit fine. So on change if box is ticked then set column a to hidden ect.

But I cannot find the systax to do this. If you are in the results of the query then you can highlight a column and chose format and then hide so i am assuming that there must be a peice of code that can do this? I just have no idea what it is?

 

[ashishprem]

what if the query is using some group by clause. in that case, hiding column will not be a solution. we need to build dynamic query to get those column filtered out from the query output..The Show property of in Query design does the same but how to get it done through VBA.. any help?

Thnks.,
Ashish

 

[boblarson]

Build a FORM to display the results of the query (you can make it a datasheet) and then you can hide fields and all on it. It will still LOOK like a query to the person but it then gives you the opportunity to hide columns.

 

[ashishprem]

That will work for a simple select query.. but what will happen if the query uses group by clause.. in that simply hiding the columns will not be a good solution as it will not give correct data.. we need to remove those column from group by clause also..