Adding a new column to a subform

[ncsthbell]

I have a form which includes a subform. I have modified the design of the subform to include a new column called 'Diff'. When I open up the form and the subform is loaded, the new column I added is not in the location that I put it when I added it in design mode to the subform. Is there some ordering for columns that I am missing?

[puppydogbuddy]

I have a form which includes a subform. I have modified the design of the subform to include a new column called 'Diff'. When I open up the form and the subform is loaded, the new column I added is not in the location that I put it when I added it in design mode to the subform. Is there some ordering for columns that I am missing?

Yes. When you add fields to a form or subform, Access defaults the tab order to be the order in which the fields were added, except when the order has been changed by using the change tab order dialog box. To change tab order:
1. Open the form in Design View.
2. Click on View on the Menu Bar and select Tab Order. The Tab
Order dialog box will appear.
3. Click on the Auto Order button if you want to reorder the entry
sequence going left to right across each row of fields, then top to
bottom, and/or
4. Drag any control to a new location by clicking the button at the left
of its name and dropping it where ever you want it to be in the
sequence.
5. Click on OK to implement the changes you made.

[ncsthbell]

I have already tried this, it changes the order that you can tab through the columns on the (sub)form but not the order the columns are displayed on the (sub)form.

[puppydogbuddy]

I have already tried this, it changes the order that you can tab through the columns on the (sub)form but not the order the columns are displayed on the (sub)form.

Right you are! The only thing I can think of is that Display Order of columns in the subform is being impacted by the display order of the columns in the subform's underlying query. Compare the order of the columns when the query is run by itself vs your subform.

[ncsthbell]

The data source for my subform is a Table, not a query. The only way I can think to do this is delete the subform and rebuild which is a lot of work and there is a lot of code behind the form..... PLUS, I have many more to do the same thing to! Any other ideas?

[ncsthbell]

I also tried ordering the columns in the table to match my display of columns on the subform and that did not work either.

[puppydogbuddy]

I also tried ordering the columns in the table to match my display of columns on the subform and that did not work either.

I will look into it further and get back to you, but here is what I have found so far about re-ordering the view of columns :

To re-order columns, select a column by clicking in the Column Selector, the column will highlight, click and hold down the mouse button within the column selector, and drag the small rectangle which appears, dropping it into the column to the left of which you wish the selected column to appear.
To freeze columns, so that they always display, select them using the column selector and choose Freeze Columns from the Format menu.

[puppydogbuddy]

Ok, I found a good source of information for you in the MS Access on-line help. If you go to the Help Index and type in View Order and hit enter, you will get a list of help topics....the relevant ones you need to look at are ColumnOrder Property and Changing the layout of a subform.

In summary these properties only apply to the datasheet view only and can not be set or changed in design view or in Form View. You can use VBA to change the datasheet view (see below), but not Form view. However, if you've ever used the form wizard, the wizard will create the form base on the order of the columns in the underlying table datasheet view at the time the the form or subform was created.

I think worse case scenario, you can programatically change the column order of the datasheet through VBA. If you are using a datasheet view, you will be ok at this point. If you are using a form view, you have the additional step of using the form wizard to recreate your subform in the desired view order If you use AutoFormat to standardize your settings (discussed below), it should be easier..

Standardise your Forms and Reports with AutoFormat

Design a form/report that uses your standard fonts, colors, borders, background settings, and control properties.

In Design view, from the Menubar select Format, AutoFormat... , and then in the AutoFormat Dialog click Customize...

Under Customize options in the Customize AutoFormat dialog box, select Create A New AutoFormat Based On The Form/Report and click OK. Enter a name for the standard style in the New Style Name dialog, and click OK. Click Close.

To apply the custom format to a form/report, open the form/report in Design view, select Format, AutoFormat... , then select the new format from the Form/Report AutoFormats list, and click OK.

VBA Code to Change ColumnOrder Property Example

The following example displays the ProductName and QuantityPerUnit fields in the first two columns in Datasheet view of the Products form.

Forms!Products!ProductName.ColumnOrder = 1
Forms!Products!QuantityPerUnit.ColumnOrder = 2The next example displays the ProductName and QuantityPerUnit fields in the first two columns of the Products table in Datasheet view. To set the ColumnOrder property, the example uses the SetFieldProperty procedure. If this procedure is run while the table is open, changes will not be displayed until is closed and reopened.

Dim dbs As Database, tdfProducts As TableDef
Set dbs = CurrentDb
Set tdfProducts = dbs!Products
SetFieldProperty tdfProducts!ProductName, "ColumnOrder", dbLong, 2
SetFieldProperty tdfProducts!QuantityPerUnit, "ColumnOrder", dbLong, 3
 
Sub SetFieldProperty(fldField As Field, strPropertyName As String, _
        intPropertyType As Integer, varPropertyValue As Variant)
    ' Set field property without producing nonrecoverable run-time error.
    Const conErrPropertyNotFound = 3270
    Dim prpProperty As Property
    On Error Resume Next                ' Don't trap errors.
    fldField.Properties(strPropertyName) = varPropertyValue
    If Err <> 0 Then                    ' Error occurred when value set.
        If Err <> conErrPropertyNotFound Then
            On Error GoTo 0
            MsgBox "Couldn't set property '" & strPropertyName _
                & "' on field '" & fldField.name & "'", 48, "SetFieldProperty"
        Else
            On Error GoTo 0
            Set prpProperty = fldField.CreateProperty(strPropertyName, _
                intPropertyType, varPropertyValue)
            fldField.Properties.Append prpProperty
        End If
    End If
End Sub