1

I have a bunch of forms in MS Access 2010. All of them are bound forms but I tricked them to only save once I click btnSave.

The main problem is that I have quite a lot of code that is the same for every form in some events such as form_beforeUpdate() or form_afterUpdate() or form_Load(). An example:

Private Sub btnSave_Click()
 'Check first that all fields have been filled
 If CheckAllFields = True Then
     Exit Sub
 End If
 TempSaveRecord = True
 Me.Dirty = False 'this will make an attempt of form_Update()
 Form_Load 'After Saving we restart the data
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If TempSaveRecord = False Then
   Me.Undo
   Exit Sub
 End If
End Sub

CheckAllFields is just a routine that checks whether all the required fields are not null:

Private Function CheckAllFields() As Boolean
  Dim Ctrl As Control
  CheckAllFields = False

  'Check for unfilled fields
  For Each Ctrl In Me.Controls
     If Ctrl.Tag = "required" And IsNull(Ctrl) Then
                MsgBox "The field " & Ctrl.Name & " has not been filled."
        CheckAllFields = True
        Exit For
     End If
  Next Ctrl
End Function

I would like to do all of this in just one Module, but I can't find a way to get the actual instance of the form in that moment. Any help would be greatly appreciate it.

Thanks

EricPb
  • 560
  • 1
  • 8
  • 21

2 Answers2

1

I do this all the time.

Here is one example:

'in a main module
Public Sub mFormLoad(p_form As Form)
    'do stuff with p_form
end sub

Then, in the actual form itself:

Private Sub Form_Load()
    mFormLoad Me
End Sub

For your example, change your function to be both public and add the argument for the form:

public Function CheckAllFields(p_form as form) As Boolean
  Dim Ctrl As Control
  CheckAllFields = False

  'Check for unfilled fields
  For Each Ctrl In p_form.Controls
     If Ctrl.Tag = "required" And IsNull(Ctrl) Then
                MsgBox "The field " & Ctrl.Name & " has not been filled."
        CheckAllFields = True
        Exit For
     End If
  Next Ctrl
End Function

and then change the line you call it to be:

If CheckAllFields(me) = True Then
enderland
  • 13,825
  • 17
  • 98
  • 152
  • Great! Thank you @enderland it works perfect, I should have asked this before, now I have 20 forms to update. – EricPb Apr 03 '14 at 21:20
  • @EricPb I know what you mean, I had similar problems and one day just broke down and spent a.... while updating all of them to have something like this. Trust me, you will be thankful (especially since I have callbacks for most open/close/current events which are identical on many forms). – enderland Apr 03 '14 at 21:21
0

To get the active form you could use the following code.

Screen.ActiveForm.Name

Then you can create a new object and set it to the ActiveForm.

Public obj_Form As Access.Form
Set obj_Form = Forms!MyActiveForm
Mike
  • 144
  • 4