query column headings

I

InventoryQueryGuy

Hi,

Is it possible to generate a query for column headings in a particular table
so that I can use the results in a combo box?


Thank you.
 
D

Duane Hookom

What? Are you attempting to fill a combo box with field names? If so, there
is a RowSourceType that does this for you. If not, you should provide a
better description of what you want.
 
M

MGFoster

InventoryQueryGuy said:
Hi,

Is it possible to generate a query for column headings in a particular table
so that I can use the results in a combo box?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to write some VBA code to get the data. You can use ADO
similar to this:

Sub TableColumns(strTable As String)

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "Data Source=" & CurrentDb.Name
End With

Set rs = cn.OpenSchema(adSchemaColumns)

' find the table we want
With rs
.Find "TABLE_NAME='" & strTable & "'"
Do While .EOF <> True
Debug.Print !TABLE_NAME, !COLUMN_NAME
.MoveNext
.Find "TABLE_NAME='" & strTable & "'", , adSearchForward
Loop
End With
cn.Close

End Sub

You can change the above to just return the COLUMN_NAME & put that in
the ComboBox's drop-down menu - using the .Add method or just make it a
Value List by making the column names a semi-colon delimited string:

col1;col2;col3;col4;col5

And putting the result in the ComboBox's RowSource property (be sure to
change the RowSourceType property to Value List.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbhyGYechKqOuFEgEQKlvgCfWcgod58miuF0Cme6U5jIcMhyHFUAnijd
Cx6BSlh37W6p9GWY6n8r9c+3
=tECk
-----END PGP SIGNATURE-----
 
I

InventoryQueryGuy

You're right Duane, I wasn't very clear with what I am trying to accomplish.
In the product/service table, I have about 25 columns (which I think you
referred to as fields). Lets say for example some columns are MSPiC, Talent,
Mentoring.
I need a query which returns only results above 4:
SELECT [Employee List].Name
FROM [Employee List] INNER JOIN [Product/Service] ON [Employee List].ID =
[Product/Service].ID
WHERE ((([Product/Service].MSPiC)>4));

So what I would like to do is have a combo box on a form, which
automatically fills with MSPiC, Talent, Mentoring, etc. This value would then
automatically enter into the query WHERE ((([Product/Service].***Combo
Box***)>4));

Any thoughts?
 
D

Duane Hookom

My thoughts are that your table structure is not normalized which causes your
issues. If I understand, you are using "MSPiC, Talent, Mentoring,..." as
field names and assigning a numeric value to each. I would change the
structure so that these are values in a field rather than field names.

If you can't change the structure, consider creating a normalizing union
query like:

quniProductServices
===============
SELECT ID, [MSPiC] as Rating, "MSPiC" as Category
FROM [Product/Service]
UNION ALL
SELECT ID, [Talent], "Talent"
FROM [Product/Service]
UNION ALL
SELECT ID, [Mentoring], "Mentoring"
FROM [Product/Service]
UNION ALL
--etc--

Then you could use a query like:
SELECT [Employee List].Name, Rating, Category
FROM [Employee List] INNER JOIN quniProductServices ON [Employee List].ID =
quniProductServices.ID
WHERE quniProductServices.Category = Forms!frmName!cboCategory
AND Rating>4;
 
I

InventoryQueryGuy

Thats the one: RowSourceType.
I can't change the table structure and so now I'm looking to run the query
with the result from the combo box.

SELECT [Employee List].Name
FROM [Employee List] INNER JOIN [Product/Service] ON [Employee
List].ID=[Product/Service].ID
WHERE ((([Product/Service].Forms!Form1!Combo4)>4));

So I run the query and it still comes back "Enter Parameter Value."

hmm??


Thanks again.
 
D

Duane Hookom

Your solution won't work like you want. As per my other suggestion, change
your table structure.
 
I

InventoryQueryGuy

I will take your word for it and give it a go next week.
Thanks for your time! :)



Duane Hookom said:
Your solution won't work like you want. As per my other suggestion, change
your table structure.
--
Duane Hookom
Microsoft Access MVP


InventoryQueryGuy said:
Thats the one: RowSourceType.
I can't change the table structure and so now I'm looking to run the query
with the result from the combo box.

SELECT [Employee List].Name
FROM [Employee List] INNER JOIN [Product/Service] ON [Employee
List].ID=[Product/Service].ID
WHERE ((([Product/Service].Forms!Form1!Combo4)>4));

So I run the query and it still comes back "Enter Parameter Value."

hmm??


Thanks again.
Click to expand...
Click to expand...
 
Top