Forms, Query, Column Headins...Oh MY!

[anotherlevel]

I have about 10 columns with data in each column...the columns dont need to show every time. I want to set it up where the user pics the column heading from a drop-down menu and then that column alone will show, none of the other columns are visible...can this be done?

 

[grnzbra]

Why do I feel like I've suggested a solution for this elsewhere?

If you have a query with 10 columns, the select statemen is of the form
SELECT Col1,Col2,Col3...Col10 FROM...

Set the code for code box to declare a query def,
set the query def equal to the query in question and then
set the qdf.SQL equal to the SQL of the existing query with
the SELECT clause selecting only the selection from the combo box.

 

[anotherlevel]

Im sorry I am really slow at this, can you give me a sample code to play with? Is this as complicated as it reads?

 

[grnzbra]

Set your query up to select all the fields from the table. (If you are working with a query that joins several tables and selects certain fields from each, treat that query as a table for the new query. Let's call the new query MyQuery.

You will need a table with a list of the ten fields in the query. Use that as the source for your combo box which we will call MyCbx. You only want one field in this table. You don't need a key field for it.

Set the Data Source for the for the form to MyQuery and the data source for the TextBox which will contain the desired field to MyField.

In the AfterUpdate event for the enter the code

Dim db as database
Dim qdf as query definition
dim str as string

str = "SELECT " & Me.MyCbx & " AS MyField FROM MyQuery.

set db = CurrentDb
set qdf = db.QueryDefs("MyQuery")
qdf.SQL = str

If you are triggering things from a button, put the code in the OnClick for the button. You may have to play with the code a bit to see an instant change, but I don't think so.

 

[anotherlevel]

"Set the Data Source for the for the form to MyQuery and the data source for the TextBox which will contain the desired field to MyField." ---This part is unclear.

Nothing was made for "MyField", where is that... When you reference data source, is that the same as control source or are u speaking of row source? Also, when u say set the data source for the form, the form as a whole...and where would I do that?

Ill tell you what I have on the form:
A combo box with the column headings
and a text box that displays the info from the combo box, that has the appropriate selected column heading. Is there something missing?

Thanks for the assistance, as you can see I am not very familiar with access.

 

[grnzbra]

Let's go from the query to the form.

Create a new query. If you use the wizard, select the table/query with the ten fields and tell it you want all the fields. If you do it in design mode without the wizard, when the table list opens select the table/query that has the ten fields and drag them into the query grid. Name one of them MyField. Save the query as MyQuery.

Since you have the form already, open it in design mode and select Properties. In the data tab, select RecordSource and set it to MyQuery. Then go to the text box and select Properties. In the Data tab, select Control source and set it to MyField.

You now have the form looking at your query and the Testbox looking at one of the fields in the query.

Now type in the code as above. Put in a breakpoint on the top line to pause it and select Column3 in the combobox. Now step through the code by pressing F8. Right after you get past the line where you set the value of str, go to the View menu at the top of the screen and select Immediate Window. You will see a blank area open up somewhere on the screen (I would expect it to be at the bottom). Go into this area (the Immediate Window) and type
?str
and press enter. The contents of the str variable will be displayed. It should be something like:

SELECT Column3 AS MyField FROM TheTenColumnTableName

What should happen if that is what is in str is MyQuery should change (during the running of the code - I don't know if it will be a permanent change) to select the column you selected from the combo box and name it MyField, which is what the textbox is looking for.

 

[anotherlevel]

Seems like I am going to have to play with this for a while because it is not coming out the way I want it to...thanks for ur help tho!