Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2002
    Posts
    42

    Unanswered: Field Properties by code - Someone Please Help

    I have a table "TblLabs"
    There are only two constant fields "one" and "two"
    This tabel is generated by a make table query so at times there will be fields "one" "two" "three" "four" and sometimes "one" "two" "three" "four" "five" etc.
    I cannot use a form - the table must be opened as a table.
    The field "two" contains data that is irrelevant to the user but necessary for the proper display of data.
    HOW DO I HIDE THIS "two" COLUMN using code that is invoked when the table is opened.

  2. #2
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Re: Field Properties by code - Someone Please Help

    Can you use a query without the "two" field?
    Do not really get what you are getting at. Seems to be some other functionality around this table also, what is taking care of the "proper display of data" based on "two"?

    D.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you say
    the table must be opened as a table
    but have you considered useing a query: for a single table (as in your question) there is no way for the user to know if he is looking at a table or the return from a query EXCEPT you can show/hide whatever you like in the query return.

    the table must be opened as a table... in any case seems to be a questionable decision. if i were to abandon all rules in my programming life except one, the one i would hold on to is never, ever, ever let a user play with a table.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2002
    Posts
    42
    Hi Izzy - I need to explain more.
    The table that is generated pulls data from several other tables - which of course cannot be accessed.
    The data in the generated table is temporary - it doesn't matter what the user does to it.
    (the procedure to generate the table was written by someone else and is quite complex)

    I have tried the following code from Access Help but I cant get it to work:
    Public Sub SetColumnHidden()

    Dim dbs As DAO.Database
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Const conErrPropertyNotFound = 3270

    ' Turn off error trapping.
    On Error Resume Next

    Set dbs = CurrentDb

    ' Set field property.
    Set fld = dbs.TableDefs!Products.Fields!ProductID
    fld.Properties("ColumnHidden") = True

    ' Error may have occurred when value was set.
    If Err.Number <> 0 Then
    If Err.Number <> conErrPropertyNotFound Then
    On Error GoTo 0
    MsgBox "Couldn't set property 'ColumnHidden' " & _
    "on field '" & fld.Name & "'", vbCritical
    Else
    On Error GoTo 0
    Set prp = fld.CreateProperty("ColumnHidden", dbLong, True)
    fld.Properties.Append prp
    End If
    End If

    Set prp = Nothing
    Set fld = Nothing
    Set dbs = Nothing

    End Sub


    If I use a query based on the table (that is generated by a make-table query) the "two" field will always re-occur. Or can I programmatically leave it out each time the query is run?

    Thanks

  5. #5
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59
    What does this mean?

    "If I use a query based on the table (that is generated by a make-table query) the "two" field will always re-occur. Or can I programmatically leave it out each time the query is run?"

    Because the common theme among the responses (and my own suggestion), is to create a query to show to the user. Have the query pull all fields but "two" or all fields and instruct it not to diplay "two" if it must be there for some odd reason.

    Perhaps if you can clear up what you mean byt the two field will always re-occur and why that is a problem, we can be more responsive?

    Hope to help...

    Magee

  6. #6
    Join Date
    Dec 2002
    Posts
    42
    TblLabs is generated from a form called "FrmDataAnalysisCriteria" in which the user enters criteria selected from a list.
    The form is continuous so there can be many criteria entered. Adjacent to each criteria field is a data field into which
    the user adds data relevant to that particular criterion.
    The data is drawn from several tables.
    The different criteria become the column names and the data appear in the rows.
    This enables the user to "drill down" to produce a list of labs that conform to selected criteria and data.
    Every time the procedure is run the tbllabs table is deleted and then rebuilt using a maketable query and a crosstab query - often resulting in different column names.
    The first column contains the names of the labs and the second column contains a unique identifier for that row - without which the
    rows would not display all of the required data. So these first two columns (fields) are always present as named. The other columns
    vary depending on what criteria is entered into the form.
    Column "two" must always be present but contains a numerical string that means nothing to the user. The data will be different every time the table is rebuilt with different criteria.
    For cosmetic reasons I would like to hide the second column.
    So I would like to use the "SetColumnHidden" function from the Help file but I cant seem to make it do anything.
    Thanks - Sorry for the confusion.

  7. #7
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231

    Re: Field Properties by code - Someone Please Help

    How about setting the column width of column "Two" to 0, as opposed to making it hidden?

    Originally posted by Parki
    I have a table "TblLabs"
    There are only two constant fields "one" and "two"
    This tabel is generated by a make table query so at times there will be fields "one" "two" "three" "four" and sometimes "one" "two" "three" "four" "five" etc.
    I cannot use a form - the table must be opened as a table.
    The field "two" contains data that is irrelevant to the user but necessary for the proper display of data.
    HOW DO I HIDE THIS "two" COLUMN using code that is invoked when the table is opened.

  8. #8
    Join Date
    Dec 2002
    Posts
    42

    Re: Field Properties by code - Someone Please Help

    Originally posted by jmahaffie
    How about setting the column width of column "Two" to 0, as opposed to making it hidden?
    Do you have code that will do that?

  9. #9
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231

    Re: Field Properties by code - Someone Please Help

    you do. use the code you used trying to hide the column. change the line that says ("ColumnHidden")=True to ("ColWidth")=0
    once that has run, check your table and you won't be able to see the column anymore.

    Originally posted by Parki
    Do you have code that will do that?

  10. #10
    Join Date
    Dec 2002
    Posts
    42
    OK I worked it out!

    This is the Help File code rearanged:

    Private Sub Text4_Click()


    Dim dbs As DAO.Database
    Dim fld As DAO.Field
    Dim prp As DAO.Property


    Set dbs = CurrentDb

    ' Set field property.

    Set fld = dbs.TableDefs!tblLabTestAnalysis.Fields!two

    Set prp = fld.CreateProperty("ColumnHidden", dbBoolean, True)
    fld.Properties.Append prp

    fld.Properties("ColumnHidden") = True

    Set prp = Nothing
    Set fld = Nothing
    Set dbs = Nothing

    DoCmd.OpenTable ("tblLabTestAnalysis")

    End Sub

    (I changed Lab Test Analysis to TblLabTestAnalysis)

    NOTE: There is a mistake in the help file.
    Set prp = fld.CreateProperty("ColumnHidden", dbLong, True)
    fld.Properties.Append prp

    Should be:

    Set prp = fld.CreateProperty("ColumnHidden", dbBoolean, True)
    fld.Properties.Append prp

    ie DbLong should be DBBoolean

Posting Permissions

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