Address fields in selected area for fill/copy down function

[Hasse]

Hey,

I'm trying to improve a friend's function which add's Excell's "Copy/fill-down" functionality to Access (a great idea by the way). When you have an area selected, it copies the values in the first row to all selected cells below. It should work both in tables/queries and datasheet forms.

Now I need to know how to get the name of the ...th field in a datasheet, both in a table and a form. How do I achieve this?

I can retrieve the columns involved using the .SelLeft and .SelWidth properties. But I miss the link to the corresponding fields (and their names and values).

Thanks for any suggestions,
Hasse, Belgium

 

[RickSpr]

For a form, the SelLeft property will equal the ColumnOrder property of the control in the leftmost selected column. SelLeft + 1 will equal ColumnOrder of the second column, etc. Note that not all controls will have a ColumnOrder property, so you need to handle errors.

Here's a function that should help you:

    Public Function ControlInColumn(Form As Form, Column As Integer) As Control
        Dim ctl As Control, i As Integer

        On Error Resume Next
        For Each ctl In Form
            i = ctl.ColumnOrder
            If Column = i Then
                Set ControlInColumn = ctl
                Exit Function
            End If
        Next ctl
        Set ControlInColumn = Nothing
    End Function

It's a bit more difficult for table and query datasheets. You have to look at the Field objects in the Fields collection of the TableDef or QueryDef. The Field objects also have a ColumnOrder property, but there are three differences from form controls:
1. You have to access the property as a member of the field's Properties collection, and
2. Columns that haven't ever been moved will have 0 as their ColumnOrder value. For these, their position in the Fields collection is equivalent to their column position.
3. The effective column positions you obtain from the preceding rules are accurate as of the time the table or query was last saved. However, if the user has reordered the columns in an open datasheet, and has not saved the design, then the columns don't match the calculated positions. That's a serious problem, because you can't access the open datasheet in code, so you can't test the real column order.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein