Resetting default values on forms

[Gordon]

I have a number of forms where I use the generic code below (courtesy
of Allen Browne):

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox 'Reset text boxes to
blank.
If Not IsNull(ctl.Value) Then
If Left(ctl.ControlSource, 1) = "=" Then
'do nothing
Else
ctl.Value = Null
End If
End If
Case acComboBox, acOptionGroup, acCheckBox 'Reset to
default value.
If Nz(ctl.DefaultValue, "") <> vbNullString Then
ctl.Value = ctl.DefaultValue
ElseIf Not IsNull(ctl.Value) Then
ctl.Value = Null
End If
Case acListBox 'Unselect everything in listboxes.
Call ClearList(ctl)
Case acLabel, acCommandButton, acOptionButton, acTabCtl,
acPage, acRectangle, acLine, acImage, acBoundObjectFrame, acSubform,
acObjectFrame, acPageBreak, acCustomControl
'Do nothing
Case Else
Debug.Print ctl.Name & " not handled"
End Select
Next

However I have to put the code in every form in which it is used. I
tried converting it to a public function by changing the first line
to:
For Each ctl In Forms(strFormName).Controls
and preceding that with
Set strFormName = Screen.ActiveForm

....but I keep getting a "Data type mismatch" error. Is it not
possible to to do this?


Gordon

 

[Allen Browne]

In a standard module, declare your function like this:
Function WhateverYouWant(frm As Form)

Put the code in the function, and replace:
Me
with:
frm

Then call it in any form with:
Call WhateverYouWant(Me)

 

[Gordon]

In a standard module, declare your function like this:
    Function WhateverYouWant(frm As Form)

Put the code in the function, and replace:
    Me
with:
    frm

Then call it in any form with:
    Call WhateverYouWant(Me)

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.










- Show quoted text -

Works a treat. Thanks Allen.

Gordon