Determine if control on form is autonumber

[mdav2]

Is it possible to determine if the control source for a field on a form is an autonumber (or any non updatable field type)?

Basically I am looping through all the controls on a form and want to avoid changing fields that are non updatable - usually the primary key fields which are auto number.

I could look up the control cource for a control to determine the field name. However, the underlying table may be difficult to find in the forms recordsource if it is more than a single table. If the record source is made up of a series of tables it would be impossible to determine the actual table the field cam from.

I suppose there are other sneaker ways around this. I could ensure all read only fileds are locked and check for locked = true to avoid updates. Any ideas would be apreciated.

Thanks,

Mark Davies
Warwickshire County Council

 

[mdav2]

I've been looking at this some more and think that it could be handler with an error handler. Just trap error 2448 should do it.

These must be a better way to perform a "cancel changes" process without having to use the undo command (which also has to be error trapped if there is nothing to undo).

It would be nice if access had some proper table buffering so you could rollback and commit like in other dataabses.

Mark Davies
Warwickshire County Council

 

[maxhugen]

Mark, you can Commit etc, see Workspace methods.

As for the autonumber field, good design would suggest that you don't display these to a user in the first place - they should only be used 'internally' as a unique record identifier.

Max Hugen
Australia

 

[mdav2]

Thanks for the response Max. I had a look at the workspace (begintrans etc) but these require you to set up ADO connections to data. I am working from Access with access forms and fields. The fields are bound to the tables on forms so I can benefit from record locking. Plus I don't have the time to change it all to run from unbound recordsets.

I've created some code now that deals with this now (attached below). Not the most ideal but seems to work. gets around the problem that the user could save the data using the built in command from the menu or shortcut (SHIFT-ENTER). It doesn't deal with canceling on add but works well when editing (assumes you run the OLDVALUES before the CANCELCODE)

' Declare global variables
Dim ctlFormControls     As Control
Dim aOldValuesArray     As Variant
Dim intControlCounter   As Integer
Dim intArrayCounter     As Integer

sub cancelcode

' Loop through each value in the array
For nLoopCounter = 0 To Me.Controls.Count

    'loop through the countrols and set the value when control name matches the name in the array
    For Each ctlFormControls In Me.Controls

        ' If the control name in the array matches that in the control then..
        If aOldValuesArray(nLoopCounter, 0) = ctlFormControls.Name Then

            ' Only interested in resetting value if the control type is an "editable" control
            Select Case ctlFormControls.ControlType

                Case acTextBox, acComboBox, acCheckBox, acListBox, acOptionGroup, acOptionButton

                    ' Only 5 types of check needed
                    '
                    '   Old Value   |  Current Value |  Result
                    '   ===========================================================
                    ' 1)  Null      |  Null          |  No change
                    ' 2)  Null      |  new value     |  Value set back to NULL
                    ' 3)  value 1   |  Null          |  Value set back to "value 1"
                    ' 4)  value 1   |  value 1       |  No change
                    ' 5)  value 1   |  value 2       |  Value set back to "value 1"
                    '   ===========================================================
                    '
                    ' If field is autonumber then it can't contain blanks and will not have changed (4)
                    ' If you try to compare a NULL with anything the result is a NULL
                    ' Data can be of different types so can't change the data from NULL (EG NULL in number field control would not accept a string)


                    ' (1,2) If the old value is NULL then need to set the OLD value back to NULL
                    If IsNull(aOldValuesArray(nLoopCounter, 1)) Then
                        ctlFormControls.Value = Null
                        ' Exit the for each control in controls loop once the array and control name are matched
                        Exit For
                    End If

                    ' (3) Original value was not null new value was null - set back to original value
                    If Not IsNull(ctlFormControls.Value) And IsNull(ctlFormControls.Value <> aOldValuesArray(nLoopCounter, 1)) Then
                        ctlFormControls.Value = aOldValuesArray(nLoopCounter, 1)
                        ' Exit the for each control in controls loop once the array and control name are matched
                        Exit For
                    End If

                    ' (5) if current value <> old value then reset back to old value
                    If ctlFormControls.Value <> aOldValuesArray(nLoopCounter, 1) Then
                        ctlFormControls.Value = aOldValuesArray(nLoopCounter, 1)
                        ' Exit the for each control in controls loop once the array and control name are matched
                        Exit For
                    End If
        
            End Select
            

            
        End If
    
    Next ctlFormControls

Next nLoopCounter

end sub


Private Sub OldValues()

    ' *********************************************************************************************
    ' This function remembers all the old values to put them back if a user presses cancel
    ' Will remember ALL controls, some won't have a value to put back though!  EG LINES, LABELS ect
    ' *********************************************************************************************
    
    ' Redimension array for fieldnames and old values
    ReDim aOldValuesArray(Me.Controls.Count, 1)
       
    ' loop through the controls again and assign the name and value to the array
    intArrayCounter = 0
    
    For Each ctlFormControls In Me.Controls
        
        Select Case ctlFormControls.ControlType
        Case acTextBox, acComboBox, acCheckBox, acListBox, acOptionGroup, acOptionButton
            If IsNull(ctlFormControls.Value) = False Then
                aOldValuesArray(intArrayCounter, 0) = ctlFormControls.ControlName
                aOldValuesArray(intArrayCounter, 1) = ctlFormControls.Value
            Else
                aOldValuesArray(intArrayCounter, 0) = ctlFormControls.ControlName
                aOldValuesArray(intArrayCounter, 1) = Null
            End If
            intArrayCounter = intArrayCounter + 1
        Case Else
            ' not a valid control so ignore
        End Select
    
    Next ctlFormControls
    
End Sub

Mark Davies
Warwickshire County Council

 

[KenReay]

Hi

Could you not have achieved same result with:

if me.dirty then
docmd.runcommand acCmdUndo
end if



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[mdav2]

Yes I could, my reasons for not using it are:

1) Have to have an error handler to run when the command isn't available to trap the error (which means changing any generic error handler in place)

2) I am trying to control add, edit, save, cancel through buttons along with allowedits\allowaddtions. User can still bypass these buttons with shortcut keys (EG SHIFT & ENTER to save, CTRL & Z to undo). Would have to either disable keystrokes, disable full menus, amend error logging (or combination of the 3) to force them into using command buttons.

I think the other area where this is useful is when you have some other input on subforms (where undo gets complicated or doesn't work effectively)

Mark Davies
Warwickshire County Council