Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Query - ColumnHidden Property (XP, Access 2K3)

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

    Fields![JobProduction.PhaseCode]

    to

    Fields![PhaseCode]

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Query - ColumnHidden Property (XP, Access 2K3)

    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.

  5. #5
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Query - ColumnHidden Property (XP, Access 2K3)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Query - ColumnHidden Property (XP, Access 2K3)

    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?

  7. #7
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Query - ColumnHidden Property (XP, Access 2K3)

    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.

  9. #9
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  10. #10
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Query - ColumnHidden Property (XP, Access 2K3)

    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.

  12. #12
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Genius.

    Works like a charm!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •