Is this possible - Select Field Query (1 Viewer)

[John Jnr]

I asked a question related to this yesterday with no luck. So I will ask a simple question:
Can a query be designed so from choices the user makes in a form made up of combo boxes with field lists. The query then outputs the results of those field selections?
I have tried putting in the field box in the query design grid:
Expr1: [Forms]![Form1]![Combo0]
The query just outputs the combo0 entry! Why wont the query use the result from the combo selection as the field name?

You help would heal a broken soul.

Thanks

 

[Rich]

Download the qrySamp97 from Microsoft, look the query by form as a good place to start

 

[Jon K]

In a parameter query, the parameter is entered in the Criteria cell, not in the Field cell.

For example, if you want to retrieve records of a client, in the ClientName column, set the criteria
[Forms]![MyForm]![Combo0]

With a parameter query, you can tell Access which records to retrieve, but not which fields to retrieve.

 

[John Jnr]

Jon,
I just want to use a form to select the fields which the query uses to examine the table?
The query then lists all records for the selcted fields?

I hope I am not just being dim?

Can this be done?

I have search the whole forum with no luck, looked at the query be form samples. However they don't show how to do my problem. It seems a simple problem?

Cheers

John

 

[John Jnr]

Sorry for the errors within my post, deadlines, headache, frustration etc.

Heeellllllppp!

 

[Shane_Mc]

Well, If you want to pull all records from certain fields in a Table all you have to do is put the fields you want in the query. Leave out what you don't want. You don't even need to set any criteria.

Am I still not hearing exactly what you want?

 

[John Jnr]

No I don't want to add any criteria in the query.
I want the user to select fields for the query to run. The combo box or list box bit is easy. Then when the user hits the query button, the selected fields from the combo boxes gets transfered into the field boxes in the query design grid. The query runs and outputs all the records for the selected fields.

As previous I tried: Expr1: [Forms]![Form1]![Combo0] in the field box in the query design grid, so the query uses this as the field name. But all the query does is:
Project Desription Expr1
x 2003 (this is combo box entry)
y 2003
z 2003
a 2003
b 2003
c 2003

I have also tried this method in a report. So the combo box selection is carried over tot he field box in the detail section of the report. However as the query result the report just shows the combo box selection and doesn't use that selection as the field name.

I hope this helps?

 

[Pat Hartman]

Parameters are used to supply specific data values for a query. Parameters cannot be used to supply SQL elements such as column names, table names, or relational operators. You are trying to build a dynamic query. One who's field list changes each time it is run. The ONLY way to do that is with VBA. Build the SQL string in VBA and then run it. You cannot use a stored querydef.

 

[fratezone]

John,

Yes it's possible. Most of my forms are designed with the user in mind and having them select from a combobox or even simply enter some text or numbers is something I include all the time.

Unfortunately, it's a very complex and lengthy process. Most is code written using VBA and if you are up for the challenge I suggest you get yourself a copy of ACCESS 97 EXPERT SOLUTIONS - written by none other than Stan Leszynski (not for the faint at heart).

His style of programming is you might call HEAVY DUTY. He includes in his book a sample of exactly what you are looking for.

HTH
Ray

 

[John Jnr]

Fratezone,
Thanks for the reply, but I am a little surprised att he response.
Does this also apply to forms?
For example if the user selected a number of field names from a combo box within a form. Could you not use those combo box selections as field names for another form? e.g. use the same expression in the contro source of a text box in the detail section of a form:
=Forms!Form1!Combo0

I have tried it but the form just returns the selction in the text box from the combo.

Again is this solved by complex VBA?

Thanks

Ps. The book is looking like some late night reading!

 

[fratezone]

Could you not use those combo box selections as field names for another form? e.g. use the same expression in the contro source of a text box in the detail section of a form:
=Forms!Form1!Combo0

I personally have never seen it done that way however, I can assure you that book has what you want. I had a look at the code and it's not that bad. It's not as simple as you would hope it is. I'll try posting a snapshot of the form itself and you'll have a good idea of what the sample does. Unfortunately, I can;t post the code cause it's not mine and the code specifically forbids any copying without permission.

I not sure how to include the image with my post so I'll have to do an attachment. (notes on inserting images are very vague !!!!)

 

[fratezone]

sorry the bmp file was too large. try this!

 

[Jon K]

"I have search the whole forum with no luck, looked at the query be form samples. However they don't show how to do my problem."


To show you how to do it in a form, I have attached a small demo database here. Run the form, select the fields and click on the View Records button.

I have used a list box so that more than one fields can be selected.

The table/query to use is specified in the On Load event procedure of the form. The rest of the code is in the On Click event procedure of the command button.

The database (SelectFields_demo.zip 15K) was written in Access 97, so DAO was used. However, it has been tested in Access 2000.

If you write your own code in Access 2000, you must make a reference to Microsoft DAO 3.6 Object Library (when the code window is open, select menu Tools, Reference...)

 

[John Jnr]

JK,
Thanks that was perfect.

I can only offer you a little joke in repayment:

Q: If the dove is the bird of peace, what is the bird of true love?
A: The swallow.


Cheers

John