Datasheet as subform: retain or deny rearranging of columns.

[Helen]

I have several forms with subforms that are data sheets.


In all, I can rearrange the columns to where I want them for the current purpose - some days want one order, some another, as data sheets are very wide - drag columns interested in to the left.

Problem is…. in some forms, the rearrangement is retained upon closing so I don't have to shift them again. In others, it resets the locations to where they were beforehand.

I've compared the properties of the subform from the main form, and of the subform itself, using the ALL column, and everything appears to be identical but one retains the changed order, the other does not.

I just can't find how to SET it to be one way or the other.

Of course, the help within Access is next to useless.

[rpeare]

Are you prompted to save the form upon exit at any time? Usually if you change the design you will be. If you are you can suppress that save message, save the form by default, and therefore save any changes you've made.

Can you reliably duplicate the error. i.e. does it always happen on a specific form or forms and not on others?

[Helen]

Thank you for replying!

No, no prompt to save changes, just close.

The rearranged columns are always retained on the forms it retains them, and never on those it doesn't. That's what prompted me to examine the properties of both, thinking that a property is set somehow, though I don't recall setting any properties in relation to it.

[rpeare]

I can not duplicate your error.

The easiest thing I can think to try is to recreate your forms/subforms that are not saving your changes (assuming that all the properties of each form and subform you currently have identical but behaving differently) and see if you can eliminate the behavior.

The second thing to try is to check your relationships and make sure you're using a unique identifier as the link between your form and subform

Lastly you can try to upload a test database that has some bogus data in it (access 2003 or prior please) and we can take a look

[Helen]

Thanks for the idea. Recreating all the forms is not realistic, but it prompted what I've done to investigate.

I remade one data sheet subform, so it had no coding behind it. It seemed that the problem was solved, UNTIL I added the coding. So… I commented out the code, and sequentially UNcommented line by line.
At the stage below, rearranged columns (and changes to column width) ARE retained upon closing:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

    Dim dtDefoltTD, dtDefoltNT As Date 'TD is TargetDate, NT is NextTarget
    Dim lngRecNum As Long
    Dim dtFirst, dtCurr As Date

    DoCmd.SetWarnings False
    'need to ensure weights are right due to calorie usage calculations
    DoCmd.OpenQuery "TargetsCalcWeightsQ"

    If DateAdd("d", 7, DMax("TDate", "TargetsEXT")) > Int(Now()) Then
        dtDefoltTD = DateAdd("d", 7, DMax("TDate", "TargetsEXT"))
    Else
       dtDefoltTD = Int(Now())
    End If

    dtDefoltNT = DateAdd("d", 7, dtDefoltTD)
'    TDate.DefaultValue = "#" & Format(dtDefoltTD, "mm\/dd\/yyyy") & "#"
'    NextTDate.DefaultValue = "#" & Format(dtDefoltNT, "mm\/dd\/yyyy") & "#"
'    lngRecNum = DCount("Tdate", "TargetsEXT")

'    'safety procedure to set every record's NextTDate to be equal to the next TDate.
'    If lngRecNum > 1 Then
'        dtFirst = Me.TDate
'        DoCmd.GoToRecord acActiveDataObject, , acLast
'        dtCurr = Me.TDate
'
'        Do Until dtCurr = dtFirst
'            DoCmd.GoToRecord acActiveDataObject, , acPrevious
'
'            If Me.NextTDate <> dtCurr Then
'                Me.AllowEdits = True
'                Me.NextTDate = dtCurr
'                DoCmd.Save acDefault
'            End If
'
'            dtCurr = Me.TDate
'        Loop

'    End If

Exit_Form_Open:
    DoCmd.SetWarnings True
    Exit Sub

Err_Form_Open:
    MsgBox "Form_TargetsEXT subform, Form_Open, Error number: " & Err.Number & vbNewLine & Err.Description
    Resume Exit_Form_Open
    
End Sub

As soon as the next line (also given below) is UNcommented, any rearrangement or change to widths of columns is NOT retained upon closing.

'    TDate.DefaultValue = "#" & Format(dtDefoltTD, "mm\/dd\/yyyy") & "#"

Any more great ideas?

[Access_Blaster]

Hi Helen,

I would open a new database and Import everything into it, and see if the problem goes away.

Richard

[rpeare]

Is there any way you can upload a sample database with garbage data in it to play around with (access 2003 or below please). If you remove the default value before you close the form it may help but that's the only thing I can think to try without something to work with.

[Helen]

I would open a new database and Import everything into it, and see if the problem goes away.

Thanks for the idea Richard. Tried it - no change, same thing happening.

[Helen]

Investigations continue.
It seems I CANNOT rearrange the columns or change widths when anything like the following appears in the code, even if it the line isn't executed - it just appears IN the code.

Me.FieldName.Locked = True/False (either one has the same effect)
Me.AllowEdits = True/False
Me.AllowDeletions = True/False
Me.RecordLocks = True/False
FieldName.DefaultValue = ….

Next I'm going to try to remove this code and add code to the OnDirty, OnDelete, & BeforeInsert events to achieve the same result without changing any properties - which seems to be the problem.

Yup - solved by removing any of the above types of changes, addressed what I needed to get done using the above mentioned events.