Solved Disable edit but allow filter

[theinviter]

Dear Guys
I have a form that has a combox to filter the record, but on load i want to disable deletion , addition and edit.

so when i did the below code it disable every every thing even filter.
just wanna able to filter the form.

Me.Form.AllowAdditions = False
Me.Form.AllowDeletions = False
Me.Form.AllowEdits = False

 

[arnelgp]

set those Properties on Design View of your form.

 

[theDBguy]

but on load i want to disable deletion , addition and edit.

What about after load, are those allowed? The AllowEdits=False is what's breaking the filter. A workaround is to lock specific control instead of the whole form.

 

[theinviter]

set those Properties on Design View of your form.

i tried but same issue

 

[bastanu]

Leave those properties =True and change the form's recordset type to Snapshot.

Cheers,

 

[pbaldy]

Another option is to set allow edits to yes in the got focus event of the combo, and back to no in its lost focus event.

 

[Pat Hartman]

Set the AllowAdditions and AllowDeltete properties to no in the form's design view. Leave the AllowEdits as Yes.

Then in the on Dirty Event, trap the attempt to edit, give the user an error message and Undo the edit

Private Sub Form_Dirty(Cancel As Integer)
    MsgBox "on dirty", vbOKOnly
    MsgBox "Changes to data are not allowed.", vbOKOnly
    Cancel = True
    Me.Undo
End Sub

Remove the first msgbox after you test the code. You will see that the on dirty event does not fire for unbound controls. But, if the user modifies a bound control, it fires and you can PREVENT the update by cancelling it and using Me.Undo to back out whatever the user typed.

It is always important to understand what form events are intended to be used to control. This is how you use the dirty event while letting your unbound combos and search boxes continue to function.

 

[bastanu]

Wouldn't that (Me.Undo) add to the processing load? I have been using the snapshot recordset type method for many years to allow unbound combos to search and\or filter forms with great success; and to make the form editable again you simple change the recordset type to dynaset.

Cheers,

 

[Pat Hartman]

Snapshots cause more overhead since Access brings the entire recordset into memory instead of working with a keyset.

 

[bastanu]

True, but they are considered to be usually faster than dynasets:

Microsoft Access: Snapshot and Dynaset recordset queries - what the heck are they?

Technical fixes, tips and findings -- all based on my personal experience. Mostly macOS and iOS.

tech.kateva.org

Dynasets vs. Snapshots in Microsoft Access

Snapshot or Dynaset - which one enhances the performance?

I have a query that retrieves 1000 records and user can add a new record in this form. I want to enhance the performance because the query already needs 5 seconds. I have read many article about

stackoverflow.com

 

[Pat Hartman]

You shouldn't be binding forms to large enough recordsets to make a difference. Forms should retrieve the data they need rather than all the data that might ever be needed.

 

[theinviter]

Set the AllowAdditions and AllowDeltete properties to no in the form's design view. Leave the AllowEdits as Yes.

Then in the on Dirty Event, trap the attempt to edit, give the user an error message and Undo the edit

Private Sub Form_Dirty(Cancel As Integer)
    MsgBox "on dirty", vbOKOnly
    MsgBox "Changes to data are not allowed.", vbOKOnly
    Cancel = True
    Me.Undo
End Sub

Remove the first msgbox after you test the code. You will see that the on dirty event does not fire for unbound controls. But, if the user modifies a bound control, it fires and you can PREVENT the update by cancelling it and using Me.Undo to back out whatever the user typed.

It is always important to understand what form events are intended to be used to control. This is how you use the dirty event while letting your unbound combos and search boxes continue to function.

work succefully. thanks

True, but they are considered to be usually faster than dynasets:

Microsoft Access: Snapshot and Dynaset recordset queries - what the heck are they?

Technical fixes, tips and findings -- all based on my personal experience. Mostly macOS and iOS.

tech.kateva.org

Dynasets vs. Snapshots in Microsoft Access

Snapshot or Dynaset - which one enhances the performance?

I have a query that retrieves 1000 records and user can add a new record in this form. I want to enhance the performance because the query already needs 5 seconds. I have read many article about

stackoverflow.com

i tried but did not work