Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2007
    Posts
    40

    Unanswered: Displaying all columns from an unknown table in form datasheet view

    How do you cause a form in datasheet view to display all columns in a table? I have a table where I don't know what the columns are going to be at design time. I have a list of all possibilities for the columns and can create a form with all of these on, but then if any of them are missing, "#Name?" is simply displayed in the field space.

    So I want one or other of the following:

    1. A piece of code which sets the form to display all columns in the table.
    2. A piece of code which hides those columns where "#Name?" would be displayed

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    If you go with all possible fields being defined, then you can set the #Name? fields to .ColumnWidth = 0 with this code:
    Code:
    Sub FieldNames() Dim rs As Recordset Dim fld As Field Set rs = CurrentDb.OpenRecordset("TableName") For Each fld In rs.Fields Me.Fields(fld.name).ColumnWidth = -2 Next rs.Close set rs = Nothing End Sub
    Within a Datasheet view of a form, the width of columns is controlled by the ColumnWidth property. By setting this property to -2, it is the same as double clicking on the border between two column headings. This double click sets the column width to the widest width of the data in the column. The property Width ONLY applies to the width of a column when in Form view, not Datasheet view.

    You will also need to be sure that DAO is included in the Reference list within the VBA references. (Tools/References...)

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Another method would be to have a bunch of fields already defined with no ControlSource. Then, as you loop through the fields in the table, just set Fld001.ControlSource to the first field name found in the loop, Fld002.ControlSource to the second field name found in the loop, etc.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can create controls at runtime if you can be bothered:
    Code:
    Application.CreateControl .....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2007
    Posts
    40
    Quote Originally Posted by GolferGuy
    If you go with all possible fields being defined, then you can set the #Name? fields to .ColumnWidth = 0 with this code:
    Code:
    Sub FieldNames() Dim rs As Recordset Dim fld As Field Set rs = CurrentDb.OpenRecordset("TableName") For Each fld In rs.Fields Me.Fields(fld.name).ColumnWidth = -2 Next rs.Close set rs = Nothing End Sub
    Within a Datasheet view of a form, the width of columns is controlled by the ColumnWidth property. By setting this property to -2, it is the same as double clicking on the border between two column headings. This double click sets the column width to the widest width of the data in the column. The property Width ONLY applies to the width of a column when in Form view, not Datasheet view.

    You will also need to be sure that DAO is included in the Reference list within the VBA references. (Tools/References...)
    I presume that you mean "Datasheet view, not Form view"? In any case, what is the Me.Fields property? I presume you are writing this procedure within a form module, so "Me" is a reference to a form. However then Me.Fields does not seem to exist, both in the Help and in the popup list which appears when you type. Where is it?

    Also, I will be using ADO rather than DAO to create this application, but your code looks easy enough to port.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kidburla
    Where is it?
    You are right - he meant Me.Controls.

    Nice one about the -2 for columnwidth - new one to me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Sorry about the Me.Fields. I was in a hurry but wanted to help because I knew the -2 would be a real big help.

    I stumbled across the -2 when building a report that was going to need tight columns, but would need to be dynamic. I was looking to count characters in each field, then found the -2. The procedure I use opens the query in a form's datasheet view, sizes all the columns, writes the sizes of the columns to a holding area, then open the report, sizes it's columns based on the form's datasheet column width, then prints the report. Customer was VERY happy!

    Here is the same code in ADO:
    Code:
    Sub FieldNames()
        Dim fld As ADODB.Field
        Dim cnnDB As New ADODB.Connection
        Dim rs As ADODB.Recordset
     
        Set cnnDB = CurrentProject.Connection
        Set rs = New ADODB.Recordset
        rs.Open "DateText", cnnDB, , , adCmdTable
        ' Enumerate the Fields collection of the strtable
        For Each fld In rs.Fields
            Debug.Print "Field Name: " & fld.Name & "Data Type: " & fld.Type
        Next fld
     
        rs.Close
        Set rs = Nothing
        Set cnnDB = Nothing
    End Sub

  8. #8
    Join Date
    Jun 2007
    Posts
    40
    Quote Originally Posted by GolferGuy
    Sorry about the Me.Fields. I was in a hurry but wanted to help because I knew the -2 would be a real big help.

    I stumbled across the -2 when building a report that was going to need tight columns, but would need to be dynamic. I was looking to count characters in each field, then found the -2. The procedure I use opens the query in a form's datasheet view, sizes all the columns, writes the sizes of the columns to a holding area, then open the report, sizes it's columns based on the form's datasheet column width, then prints the report. Customer was VERY happy!

    Here is the same code in ADO:
    Code:
    Sub FieldNames()
        Dim fld As ADODB.Field
        Dim cnnDB As New ADODB.Connection
        Dim rs As ADODB.Recordset
     
        Set cnnDB = CurrentProject.Connection
        Set rs = New ADODB.Recordset
        rs.Open "DateText", cnnDB, , , adCmdTable
        ' Enumerate the Fields collection of the strtable
        For Each fld In rs.Fields
            Debug.Print "Field Name: " & fld.Name & "Data Type: " & fld.Type
        Next fld
     
        rs.Close
        Set rs = Nothing
        Set cnnDB = Nothing
    End Sub
    Yes, that is strange about the ColumnWidth. In my version of Access (2003), it is not listed in the help box for ColumnWidth as a valid value, but in the example attached to the help entry, the "ColumnWidth = -2" thing appears.

    Now, for my purposes, your idea is a very good one. However, my problem would be that I would want to set all the other columns to ColumnWidth 0. Now, one way to do this would be:

    Code:
    For Each ctl In Me.Controls
     ctl.ColumnWidth = 0
    Next ctl
    For Each fld In rst.Fields
     Me.Controls(fld.Name).ColumnWidth = -2
    Next fld
    similarly to your code. However, a much better way of doing this would be to do something like

    Code:
    For Each ctl In Me.Controls
     If (ctl In rst.Fields) Then
      ctl.ColumnWidth = -2
     Else
      ctl.ColumnWidth = 0
     End If
    Next ctl
    However, this obviously wouldn't work as rst.Fields is not comprised of controls. I hope you can see what I am trying to do though. Is there something similar to this that would work? (i.e. is there any way to find out if a control's name is in the list of field names for a recordset?)

  9. #9
    Join Date
    Jun 2007
    Posts
    40
    Additionally, and more importantly, there are labels on the form as well as the text boxes themselves. So the loop

    Code:
    For Each ctl In Me.Controls
      ctl.ColumnWidth = 0
    Next ctl
    fails when it reaches a label (which does not have a ColumnWidth property). How can I get it to loop through just the text boxes, or test whether a control is a text box before setting the property?

    EDIT: Incidentally, the purpose of the labels is to choose the column headings. For example, I have a column called xLevel, the reason for the x is that LEVEL is a Jet SQL reserved word (even though it doesn't do anything) and so I have to use xLevel. However, I want the column head to still display Level, so I have to use labels tied to the controls.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to use control properties. Check them out in help. In particular you want the name and controltype properties.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2007
    Posts
    40
    Quote Originally Posted by pootle flump
    You need to use control properties. Check them out in help. In particular you want the name and controltype properties.
    Okay I've done that now, I have checked whether ctl.ControlType = acTextBox.

    In addition I have implemented the strategy of setting all the ColumnWidths to 0 and then unsetting the relevant ones. I also found that setting ColumnWidth to 0 hides the column, so when unsetting the ColumnWidth from 0 I had to also set the ColumnHidden property to false.

    It still would be useful if I could find some way to test whether a control's name is in the list of fields for a recordset, though?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kidburla
    It still would be useful if I could find some way to test whether a control's name is in the list of fields for a recordset, though?
    Two options off the top of my head. Loop through or try to reference the field. If there is an error then it does not exist. The latter option will offend the purists
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2007
    Posts
    40
    Quote Originally Posted by pootle flump
    Two options off the top of my head. Loop through or try to reference the field. If there is an error then it does not exist. The latter option will offend the purists
    Okay, that's a sound idea. However, I am looping through the controls in a "For Each" loop, as follows:

    Code:
    For Each (ctl in Me.Controls)
      ' ...
    Next ctl
    If an error is encountered while testing one of the controls, how do I jump back into the for loop at the right place to continue testing the controls? Is there some equivalent to C++'s "try" block, for example?

    Here is my code ("test" is a Variant):

    Code:
    For Each ctl In Me.Controls
        If (ctl.ControlType = acTextBox) Then
            On Error GoTo ErrorHandler
                test = rst(ctl.Name)
            On Error GoTo 0
            ctl.ColumnHidden = False
            ctl.ColumnWidth = -2
    Looping:
            On Error GoTo 0
        End If
    Next ctl
    rst.Close
    ErrorHandler:
        ctl.ColumnHidden = True
        Resume Looping
    The problem is that it seems that ctl is not valid when outside the actual loop, and so I get an "Object not set" error.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kidburla
    Is there some equivalent to C++'s "try" block, for example?
    No.

    To return to the next line use Resume Next (clears the error, goes to the line after the one that triggered it).

    You will either need to set a control variable that has scope outside the loop to reference the control or handle everything within the loop. Easiest way to do that is with on error resume next. You will lose a lot of cred with the C++ crowd though. Don't forget to put proper error handling back in after the loop.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    The "TRY" block error hander or similar is in .NET but not VBA. You need resume next ie. something like
    Code:
    For Each ctl In Me.Controls
        If (ctl.ControlType = acTextBox) Then
            On Error Resume Next
                test = rst(ctl.Name)
                if Err.Number=0 Then
                   ctl.ColumnHidden = False
                   ctl.ColumnWidth = -2
                Else
                   ctl.ColumnHidden = False 
                End If
            Err.Clear
            On Error GoTo 0
        End If
    Next ctl
    rst.Close

    MTB

Posting Permissions

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