select query using user-defined fields 1

[AndrewWolford]

Here's my problem:

I'm trying to write a query that selects a field from a table (easy part) based on a user-specified field (hard part). I have a form with a text box (txtUnion). I want whatever fieldname the user types in this text box to be selected from the table (testdata). Here's the query I've tried that doesn't work:

select forms![union].[txtUnion] from testdata;

Access doesn't recognize that what the user types in is a field name and should pull that field name. Instead the query just spits back what the user typed in. How can I make the SQL statement realize that I want to pull the field the user enters? Does anyone know how to do this?

PS I'm not married to using a form as my user input

Thanks for any help,

Andrew

 

[scriverb]

Try this. Create a query named "qryTestQuery" with any field from the table selected and save it. After you have entered the field name in the forms control put this into a button control to execute the query "qryTestQuery":

Dim db As DAO.Database
Set db = CurrentDb
db.QueryDefs("qryTestQuery&quot.SQL = "Select TestData." & Me![txtUnion] & " From TestData;"
db.Close
DoCmd.OpenQuery "qryTestQuery"

This could be expanded to provide for a dropdown combo box of field names from the table so that the user doesn't provide for a table name typo thus causing the query to error out. Multiple field names could also be handled by some sort of checkbox in a list or subform and then building the SQL string from those fields selected.

Let me know how this works for you.

Bob Scriver

 

[scriverb]

I took it a step further to use a list box of field names for you. Using the query from the previous posted example try the following steps:

1. Create a table named tblFieldNames with the field names for your table that you would like the user to be able to select and in the order of selection. Your table should have just one column named FieldName. Enter the field names in the table.
2. Create a listbox control on your form and call it FieldNameList. Set the following parameters: RowSource - tblFieldNames, ColumnCount - 1, ColumnWidths - 2, BoundColumn - 1, MultiSelect - Simple. Now size and stretch the control to make visible all of the fields you want for ease in picking the field names.
3. Put the following code behind a button:

Dim frm As Form, ctl As Control, vSQLString As String
Dim varItm As Variant, intI As Integer
Dim db As DAO.Database
Set db = CurrentDb
vSQLString = "Select "
Set frm = Forms!Union
Set ctl = frm!FieldNameList
For Each varItm In ctl.ItemsSelected
vSQLString = vSQLString & " TestData." & ctl.Column(0, varItm) & ","
Next varItm
db.QueryDefs("qryRunSQL&quot.SQL = Mid$(vSQLString, 1, Len(vSQLString) - 1) & " From TestData;"
DoCmd.OpenQuery "qryTestQuery"

I think this has everything. Get back with me if we need to make adjustments or if I made a typo. But, it works on my form. Just select multiple fields and they will be displayed in your query as requested. Bob Scriver

 

[scriverb]

Oops!! Had a typo.

db.QueryDefs("qryRunSQL&quot.SQL = Mid$(vSQLString, 1, Len(vSQLString) - 1) & " From TestData;"

should be

db.QueryDefs("qryTestQuery&quot.SQL = Mid$(vSQLString, 1, Len(vSQLString) - 1) & " From TestData;"
Bob Scriver

 

[AndrewWolford]

Bob, Thanks for your helpful posts... I think I'm on the right track now thanks to your help...

-Andrew

 

[scriverb]

Andrew, I do have a little .mdb file with these examples completed that I could send you. Let me know your email address and i will send it to you.

Thanks for the Star. Bob Scriver

 

[AndrewWolford]

I got working on another projected and haven't checked this thread in about a week, sorry for the delay. You can send it to: andrew_wolford@ml.com

thanks!

 

[scriverb]

Andrew, I have forwarded a demo database of these techniques so that you may have a working example of how to do this.

Good luck with your project. Bob Scriver