preventing unintentional & accidental changes.

  • Thread starter Thread starter manish
  • Start date Start date
M

manish

Hi there,
I have many forms on which i work. Very often i have to
filter the records. But very often absent mindedly i just
enter the filter criteria direct into the form field
instead of the right click menu. this sometimes causes my
data to change which i come to know after the printing of
reports. I wanted to know that is there any way i can
prevent accidental/unintentional changes. What will be the
best method.
Please consider the fact that i have to regularly
update/change the existing data besides new data entry.
What will be the best method to approach.
My form has a subform linked by Master & child fields.
advice please!!
manish
 
If you would like confirmation before committing changes, use the
BeforeUpdate event procedure of the *form*:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
If MsgBox("Save Changes?", vbOkCancel) <> vbOk Then
Cancel = True
Me.Undo
End If
End If
End Sub

What we do is to place unbound controls in the Form Header section for the
controls the user is likely to want to filter on, along with command buttons
to apply filter and remove filter. The controls are a different colour, and
in a different location, so the distinction is really clear for the user.
 
Allen,
Thanks for the lovely code. It really solved my purpose.
at least there won't be an accidental change immediately.
Also-if u could provide me the detail code for the button
to be used for filter - it would be very kind of u.
Thanks for ur support anyway.
warm regards
manish
 
The basic idea is to look at each of the unbound controls in the form
header, and build up a string to use as the form's filter from any that are
not blank.

Text fields need extra quotes as the delimiter. Date fields need # as the
delimiter. This example show one of each:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboFilterCatID) Then
strWhere = strWhere & "([CatID] = " & Me.cboFilterCatID & ") AND "
End If
If Not IsNull(Me.txtFilterSignText) Then
strWhere = strWhere & "([SignText] Like ""*" & Me.txtFilterSignText
& "*"") AND "
End If
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "[EnteredOn] >= " & Format(Me.txtStartDate,
"\#mm\/dd\/yyyy\#") & ") AND "
End If
'etc for other boxes as needed.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0& Then
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
Else
Me.FilterOn = False
MsgBox "No criteria entered.", vbInformation, "Filter removed"
End If
End Sub


For the button that removes the filter, we just set its On Click property
to:
=ClearFilterAndHeader([Form])
and it calls the following function which resides in a general module where
it can be called from any form:

Public Function ClearFilterAndHeader(frm As Form)
'Purpose: Remove the filter, and clear all the unbound contorls in the
form header.
Dim ctl As Control 'Each control in the form header.

'Save if necessary.
If HasProperty(frm, "Dirty") Then
If frm.Dirty Then
frm.Dirty = False
End If
End If

'Remove the filter.
frm.FilterOn = False
frm.Filter = vbNullString

'Clear all the unbound controls in the form header.
For Each ctl In frm.Section(acHeader).Controls
If HasProperty(ctl, "ControlSource") Then
If Len(Nz(ctl.ControlSource, vbNullString)) = 0& Then
If Not IsNull(ctl.Value) Then
ctl.Value = Null
End If
End If
End If
Next

Set ctl = Nothing
End Function
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim vardummy As Variant
On Error Resume Next
vardummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
 
Back
Top