How to get instance of actual form from MS Access in a Module (VBA)

Question

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

enderland · Accepted Answer · 2014-04-03 21:15:47Z

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

answered Apr 3, 2014 at 21:15

enderland

14.3k18 gold badges105 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EricPb Over a year ago

Great! Thank you @enderland it works perfect, I should have asked this before, now I have 20 forms to update.

enderland Over a year ago

@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).

Mike · Accepted Answer · 2014-04-03 21:17:38Z

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

answered Apr 3, 2014 at 21:17

Mike

1444 bronze badges

Collectives™ on Stack Overflow

How to get instance of actual form from MS Access in a Module (VBA)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related