Loop through Forms Controls excluding SubForm (1 Viewer)

[DrXia92]

Hi everyone,

Happy to write my first post in the forum (after introduction)

I have an audit trail macro that loops through controls in a form and writes a change log table (I borrowed the code from techrepublic) However, while the macro works perfectly with forms without subforms I get type mismatch error in the only form that includes 2 subforms. I believe that if I am able to stop the macro from looping through the subform controls the error could go away.

At the moment the code is the following

For Each ctl In frm.Controls [...]

Is there a way to change this to be For each control in form excluding subforms?

Btw, the macro is called in the following way

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call AuditTrail(Me, Me.launch)

End Sub

Thanks!

 

[Gasman]

Ignore the subform control?
Each control has a Type property.?

https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa224135(v=office.11)

HTH

 

[CJ_London]

Is there a way to change this to be For each control in form excluding subforms?

yes - each control has a type - see this link https://docs.microsoft.com/en-us/office/vba/api/access.accontroltype

so your code would be

for each ctl in me.controls
    if ctl.controltype<>acsubform then
        ...do something
    end if
next ctl

 

[DrXia92]

Thank you both for the quick replies! I did not realize that subforms had their own control type This helped me realize the issue was not with the subform but with one Combo Box control that had multiple values allowed.

Thank you again!