Determine field names at run time?????

[cyberbiker]

I wonder if it is possible to determine field names in a table at run time?.
I am creating a "little ad hoc" report program in ACCESS 97 where the user will be able to choose a select clause from a dropdown combo box (All she will see is a "plain English translation such as "Equipment search&quot.
I then want to fill a couple of more dropdown combo boxes with the available fields in the applicable tables.
At this point she will choose "1 from column a". an operator from another list box and "1 from column b" to "finish writing the where clause and orderby clause"
The coding will be a bit more complex than what I am making it sound, but if I can fill the combo boxes with field names for her to choose from at run time by determining what fields are actually available in the applicable tables, things will be much easier in the long run.
Any ideas would be appreciated.
Terry (cyberbiker)

 

[jebry]

Hi Terry!

Change the Row Source Type to Field List and the Row Source to the appropriate table and Access will do the rest.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[SBendBuckeye]

One of the RowSourceTypes for combo and list boxes is FieldNames. Will this help you?

If you want to give something more readable, you can create a combo or list box based on the underlying recordset. For the following code to work, you will need to make sure that DAO is selected in your Tools, References list.

Dim dbs As Database
Dim tdf As TableDef
Dim qdf As QueryDef
Dim flds As Fields
Dim fld As Field

Set dbs = CurrentDb
If table Then
Set tdf = dbs.TableDefs("YourTableName&quot
Set flds = tdf.Fields
Else
Set qdf = dbs.QueryDefs("YourQueryName&quot
Set flds = qdf.Fields
End If

For Each fld In Fields
Build string or array or whatever with fld.Name
Next fld

Good LucK!

 

[cyberbiker]

Thanks to both. I knew I was overlooking something when I posted. The problem actually is when I have a select clause with 2 (or more) joined tables. I need to have fields from both tables listed something like this:
equipment.type
customer.name
etc.

SBendBuckeye: Am I blind? I see no way to select DAO any where and no "references" under tools. I am using ACCESS 97.

I do have a work around, but it will be tedious and a bit "clunky" (for want of a better term).
I can create another small table containing the available ffields and fill the combo box that way. I would just like to find a "slick" way to do this.
Terry (cyberbiker)

 

[jebry]

Hi!

It seems to me that you can use the Field Name row source type. What you need is another combo box to allow them to choose the table first. Then in the after update of the first combo box set the row source of the second to get the field list. This will allow you to concatenate the two combo boxes in code to get your table.field for the SQL.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[SBendBuckeye]

When you are in the VB code editor select Tools on the main menu and then select References. The ones currently selected for that database will be at the top. If there is nothing that says DAO scroll down through the list until you come to Microsoft and then watch for DAO. Select the highest version available to you (eg 3.6 over 3.5) by checking it so its methods and properties will be availabel to you.

Good LucK!

 

[cyberbiker]

Thanks again.
That would probably be an option.
I think I am not clear though. What I intend is to create a SQL statement through concantenating a selected prewritten select statement (stored in tblSQL) with strings selected from a drop down list. I care not what is values are in the fields at this point. I only want to read the schema of the tables instead of storing the names of the various available fields in another table somewhere.
The goal is to stop a programmer from having to drop everything, write a quick SQL, and print a report because a user needs to know "all the customers that we sent John to visit last year" or "all customers in OR" etc.
I have determined that 9 simplistic canned reports with the ability to change search criteria
If the user has too many decisions to make, I will be back where I started

Terry (cyberbiker)

 

[cyberbiker]

Thanks SBendBuckeye!. I cannot believe that I did not ever realize that. I have been cursing ACCESS 97 for quite some time now not realizing that I'm the idiot.
Your solution should solve my problem.
thanks Terry (cyberbiker)