Reset text box to empty state

[barit]

I have a form with a series of unbound text boxes that are used to set the query parameters for a report. Once the user chooses the criteria from the various boxes, the desired report opens in preview mode.

I want to add a command button to my form called "Clear" that would be used to reset the unbound text boxes to an empty state when clicked.

Suggestions on how I can accomplish this would be greatly appreciated.

Thanks

 

[barit]

Sorry.... I mistated my problem. The form uses combo boxes not text boxes to select the criteria for the report.

However, I would still like to have a command button that clears the combo box selection when clicked.

Any suggestions, please.

Thanks

 

[ineuw]

This is very easy. Create an On Click event and in the procedure, add for each textbox and/or combo box the following line. This is good when clearing is desired to be selective.

With Me
!TextBox="" (or !TextBox=Null)
!ComboBox="" (or !ComboBox=Null)
End With

Consequently, if you wish to clear all fields in the form, you can cycle through each object by using the following code. You need to disregard errors in case the Value property doesn't apply.

Private Sub CommandButton_Click()
On Error Resume next

Dim objControl as Control

For Each objControl in Me.Controls

With objControl
.Value = Null (or = "", as required)
End With

Next objControl

End Sub

 

[Aerin]

To correct the message above, before clearing the control you need to check its type. If it is text box or combo box then clear, if not just move to the next control.
'----------------------------

Private Sub CommandButton_Click()
On Error Resume next

Dim objControl as Control

For Each objControl in Me.Controls

With objControl
if .ControlType = acComboBox then' or acTextbox for text box
.Value = Null (or = "", as required)
end if
End With

Next objControl

End Sub
'---------------------------------

 

[barit]

Thanks so much! Your solution works great

 

[ineuw]

Checking for the control type is unnecessary and meaningless.

 

[PHV]

Checking for the control type is unnecessary and meaningless

And what about trying to play with a .value property of a label ?


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ineuw]

Labels don't have .Value property and the On Error Resume Next statement at the beginning takes care of any controls that are not relevant. Please read my first posting carefully.

 

[barit]

Thanks to all for responding. I really apprecaite the different suggestions and solutions. It makes life much easier.

I went with the following code behind a command button called "Clear all Criteria" located at the bottom of the form

Dim ctl As Control
Dim Response As Integer

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl = Null
End Select

In addition, I used the following in a small command button beside each text and combo box.

With Me
!TextBox="" (or !TextBox=Null)
!ComboBox="" (or !ComboBox=Null)
End With


The above solutions provide the end user with maximum flexibility to either choose clearing all or some criteria.

Anyway... Thanks again for the suggestions

 

[ineuw]

This public procedure can be called by any form to clear and reset controls to their default values, be it text, date, currency etc. This could be a textbox, combo box, list box, whatever. The exceptions are for my use and feel free to omit, add or change. I should mention that I only use unbound forms and if I want to reset some controls to a specific default value, I do it in code behind the form after running this procedure.

Public Function fnResetControls(ByVal objLForm As Form)
On Error Resume Next

Dim objControls As Controls, objControl As Control, lngLoop As Long

With objLForm

If objLForm.Properties("PopUp") = False Then DoCmd.Maximize: DoCmd.MoveSize 0, 0

Set objControls = .Controls
For Each objControl In objControls

With objControl

If .Locked = True Then GoTo SkipEnabled
.Enabled = True

SkipEnabled:

Select Case .ControlType

Case Is = acTextBox

.ForeColor = 0

If Left(.ControlSource, 1) = "=" Then .Requery: GoTo NextControl

Select Case objControl.Format

Case Is = ""
objControl.Value = "": GoTo NextControl

Case Is = "Long date"
objControl.Value = Null: GoTo NextControl

Case Is = ">"
objControl.Value = "": GoTo NextControl

Case Is = "General Number"
objControl.Value = 0: GoTo NextControl

Case Is = "Currency"
objControl.Value = 0: GoTo NextControl

Case Is = "$#,##0.00;($#,##0.00)"
objControl.Value = 0: GoTo NextControl

Case Is = "Standard"
objControl.Value = 0: GoTo NextControl

Case Is = "Percent"
objControl.Value = 0: GoTo NextControl

Case Else
objControl.Value = Null: GoTo NextControl

End Select 'End of .Format

Case Is = acSubform
objControl.Requery: GoTo NextControl

Case Is = acComboBox
objControl.Value = "": GoTo NextControl

Case Is = acListBox

Dim varItem As Variant

With objControl
For Each varItem In objControl.ItemsSelected
objControl.Value = Null
Next varItem
End With

Case Is = acOptionGroup
objControl.Value = False: GoTo NextControl

Case Is = acCheckBox
objControl.Value = False: GoTo NextControl

Case Is = acOptionButton
objControl.Value = False: GoTo NextControl

Case Else
GoTo NextControl

End Select '~ End of .ControlType

End With

NextControl:

Next objControl

End With

End Function

 

[Aerin]

Hi ineuw,

Could you tell me why the unbound forms are more preferable for you? It is not first time when I see the solution like you use and can't understand why it is better to use unbound forms if your server and client applications created in MS Access?

Regards,
Aerin

 

[ineuw]

Unbound forms give greater flexibility for the programmer and the user. In both cases, it's easier to evaluate the data before saving it. I can branch off to numerous public functions to evaluate and calculate prior to saving, without blocking other users' access to the record. With bound forms, I end up doing all data evaluation in the form's BeforeUpdate event and sometimes, that makes for a very long procedure to manage. Unbound also give much greater flexibility in form design and I use pop up forms everywhere except where the number of fields requires a full screen.