Public Sub ClearForm(frm As Form) 3

[kjv1611]

I found this code on a website for VBA coding in Access. I am getting the following error message:
Compile error:
User-defined type not defined
When I run this code: (I changed the font of the section which the VBA compiler is highlighting when giving this error message:



Public Sub ClearForm(frm As Form) 'Pass a form name to it

Dim sMask As String

For Each Control In frm.Controls
If TypeOf Control Is TextBox Or TypeOf Control Is ComboBox Then
Control.Text = "" 'Clear text
End If
If TypeOf Control Is MaskEdBox Then
With Control
sMask = .Mask 'Save the existing mask
.Mask = "" 'Clear mask
.Text = "" 'Clear text
.Mask = sMask 'Reset mask
End With
End If
If TypeOf Control Is DTPicker Then
Control.Date = Date 'Set to current date
End If
Next Control

End Sub

Any help here?

 

[kjv1611]

By the way, I forgot to edit this text to show the comments well (code comments): So here goes without the comments:

Public Sub ClearForm(frm As Form)
Dim sMask As String

For Each Control In frm.Controls
If TypeOf Control Is TextBox Or TypeOf Control Is ComboBox Then
Control.Text = ""
End If
If TypeOf Control Is MaskEdBox Then
With Control
sMask = .Mask
.Mask = ""
.Text = ""
.Mask = sMask
End With
End If
If TypeOf Control Is DTPicker Then
Control.Date = Date
End If
Next Control

End Sub

 

[Draknor39]

MaskEdBox isn't a standard MS Access control. A quick search on google showed that at least one version of this was created in vb.net and is available for download here.

http://www.codeproject.com/vb/net/maskedbox_control.asp

For reference here is a list of the standard Access Controls that you can use with your code without having to reference any additional libraries.

acBoundObjectFrame Bound object frame
acCheckBox Check box
acComboBox Combo box
acCommandButton Command button
acCustomControl ActiveX (custom) control
acImage Image
acLabel Label
acLine Line
acListBox List box
acObjectFrame Unbound object frame or chart
acOptionButton Option button
acOptionGroup Option group
acPage Page
acPageBreak Page break
acRectangle Rectangle
acSubform Subform/subreport
acTabCtl Tab
acTextBox Text box
acToggleButton Toggle button


So if you wanted to clear all the Text Boxes on your form you could do this

Dim ctl As Control
For Each ctl In Me.Controls
If (ctl.ControlType = acTextBox) Then
ctl.Value = Null
End If
Next ctl

Note you can reference any property that control type has this way. I often use this loop to lock or unlock every field on my form based on certain criteria.

Hope this helps!

 

[kjv1611]

Thanks, that may be very helpful indeed. As a matter of fact, I think I'd prefer that method, as it seems to be much simpler, shorter, and to the point than the other.

 

[kjv1611]

Okay. I tried using your code in place of the main code in the previous procedure. When I did, I had to replace Me.Controls with frm (because frm passes the Form name when I call it in the click event of my "clear form" button. One thing I am now running into is the code seems to be able to run, but it cannot set those with required values to null. Is there a short code you know of to change those from required to not required that will work along with the code used here?

 

[RoyVidar]

Clearing the controls in a bound form having a current record, deletes the values in each control, if you need to delete the current record, try for instance:

[tt]currentdb.execute "delete * from yourtable where id = " & _
frm!txtID, dbFailOnError
Me.Requery[/tt]

Or is it "clearing the form to enter a new record" you're after?

[tt]docmd.gotorecord,,acnewrec[/tt]

or perhaps:

[tt]frm.dataentry = true[/tt]

The required property applies to fields and not controls, so that's probably not the way to go.

Roy-Vidar

 

[kjv1611]

Well, all I want it to do is to clear the form (when the information is not to be entered into the table (such as if they want to clear it out and start all over.)) Would it be possible just to link it to the Esc key, since pressing that will clear the form? If you think that'd work, could you show me what code would be necessary for that? It sounds simple, but I've not done that sort of code before that I can remember.

 

[RoyVidar]

Hi again!

Code for ESC is Me.Undo (of here frm.Undo) - but if the record is alredy saved (no pencil in the recordselector;-)), neither ESC or undo would work. You'd need to delete the record, for instance with the code mentioned above.

So, create a "cancel" button and test for new record, if so issue undo, else perform delete, it might look something like this:

[tt]if me.newrecord then
me.undo
else
' perform the delete
end if[/tt]

Roy-Vidar

 

[kjv1611]

Thanks. I do believe that will accomplish the desired results on my form.

 

[jimlee]

Thanks Draknor... i've been clearing my forms the long way (field by field). I knew there must have been an easier way to do it, extremely helpful...have a star.

jimlee