Dynamic Query – ColumnHidden Property (XP, Access 2K3)

Topic

New Reply

Greetings All!

I feel rather lowly for failing to see the err in my ways, but for the life of me I can’t figure out what I’ve done wrong here. I’ve got code that generates a query (several actually), and I simply want several of the columns hidden when it opens for viewing:

strSQL2 = “SELECT tblPhCodes” & Combo0.Value & “.*, JobProduction.* ” & _
“FROM tblPhCodes” & Combo0.Value & ” LEFT JOIN JobProduction ON tblPhCodes” & Combo0.Value & “.PhaseCode = JobProduction.PhaseCode ” & _
“ORDER BY tblPhCodes” & Combo0.Value & “.PhaseCode;”
Set qdf2 = dbs.CreateQueryDef(“UtilProduction”, strSQL2)
DoCmd.OpenQuery “UtilProduction”
dbs.QueryDefs!UtilProduction.Fields![JobProduction.PhaseCode].Properties(“ColumnHidden”) = True
dbs.QueryDefs!UtilProduction.Fields![JobProduction.PhaseCodeDescrip].Properties(“ColumnHidden”) = True
dbs.QueryDefs!UtilProduction.Fields![JobProduction.BidQty].Properties(“ColumnHidden”) = True
dbs.QueryDefs!UtilProduction.Fields![CostCode].Properties(“ColumnHidden”) = True

Some of the field names are generated with the ‘.’ in the name, but I thought the square braces would deal with that. Otherwise, I’m not sure.

Thanks in advance, and any advice is sincerely appreciated.

Reply | Quote

Replies

Try omitting the table name and dot in the field name, e.g. change

Fields![JobProduction.PhaseCode]

to

Fields![PhaseCode]

Reply | Quote

Thanks for the help!

Now I get an error message as follows : Item not found in this collection

Also, there are another 3 fields with ‘.’ in the name that I’m not wanting to hide. Specifically, there is a tblPhaseCode5031.PhaseCode as well as a JobProduction.Phase Code, so on and so forth.

Thanks again for any advice.

Reply | Quote

Sorry, if you have duplicate field names, you do need to include the table name.

1. Try omitting the line DoCmd.OpenQuery “UtilProduction”, or put it at the end, after hiding some columns.
2. Check the names very carefully – even the slightest typo will cause the “Item not found” error. Similar code does work for me.

Reply | Quote

Hmm….

Now I’m getting: “Property Not Found”

It’s actually a query that joins a crosstab with another query, if that makes a difference.

Much obliged to you, sir.

Reply | Quote

Look up CreateProperty in the VBA help and click Example. The sample code shos how to create a property on the fly if it doesn’t exist.

It that doesn’t help, could you post a stripped down copy of the database?

Reply | Quote

Thanks, Hans!

I added the CreateProperty lines and whatnot, now the code runs, but does not hide the fields in the resulting query. Here’s a stripped version. Everything is jumpstarted with the frmPickJob. (I only left a few tables in here for size purposes, so there will be more choices in the dropdown list than will work)

Much appreciated sir.

Reply | Quote

You almost got it right. The ColumnHidden property should be of type dbBoolean (i.e. True/False) instead of dbLong (a long integer number). So:

Set prp1 = fld1.CreateProperty(“ColumnHidden”, dbBoolean, True)

and similar for the others.

Reply | Quote

Excellent!

Thank you sir. I found the example in the help, and it actually has dbLong there. I should have figured that one out. Much obliged.

Reply | Quote

One last thing:

Set fld5 = dbs.QueryDefs!UtilProduction.Fields![“tblPhCodes” & Combo0.Value & “.PhaseCode”]

This doesn’t work – I’m getting an ‘Item not found in collection’ error. In building the SQL statements, using the & and combining the variable value with the “tblPhCodes” works fine. Do you know how I might be able to code this to work? The number of tables will be constantly expanding, hence why I’m trying to achieve this in this particular manner.

Thanks again for sharing your genius with us.

Reply | Quote

Try this instead:

Set fld5 = dbs.QueryDefs!UtilProduction.Fields(“tblPhCodes” & Combo0.Value & “.PhaseCode”)

Fields(“fieldname”) is a way of referring to a field through its name as a string; this string can be assembled in code.

Reply | Quote

Genius.

Works like a charm!

Reply | Quote