clear fields button not working

[fainterm]

I have a form with several unbound text boxes that users input data for search parameters. Below i have a button that when clicked, it removes all of the text from the unbound boxes. For some reason, now when i click the clear button it is throwing up: Run-time error '2448': you can't assign a value to this object. When i Debug, It is stopping on ctl.Value = Null here is the code:

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False


End Sub

I believe that this started happening after i changed the data type of the first field from Text to Number, as this button is working in a previous version of the database with the same code. Any ideas?

[June7]

They are unbound controls, field data type should be irrelevant.

Have you given controls names different from fields included in the form RecordSource? Although that shouldn't really matter because the code specifies control type.

I am at a loss.

Have you step debugged? Exactly which control is triggering the error?

[fainterm]

It's actually the very first control that is triggering the error. This is the same control that i changed the datatype on. The ctl.Value = Null is actually the next step that should execute. It's odd because if i have no data in that field, but do in any of the others it will work. However as soon as i put text in the first control it throws up the error.

[ItsMe]

... This is the same control that i changed the datatype on. .

How did you go about doing this? Did you adjust the Format properties? Is this an unbound control?

[fainterm]

I modified the datatype on the table from text to number. Then on my form i modified the code from:

If Not IsNull(Me.txtSearchMRN) Then
strWhere = strWhere & "([MRN] = """ & Me.txtSearchMRN & """) AND "
End If

to

strWhere = strWhere & "([MRN] = " & Me.txtSearchMRN & ") AND "

Yes these are all unbound controls. As i said, if i have no data in any of the controls, or if i have data in any of the other fields I don't get the error and all fields are cleared. I'm only getting this error if i have any characters in the first control

[ItsMe]

Interesting. What happens if you remove strWhere from Recordsource/Rowsource properties and then try to set control = Null

[fainterm]

So code would look like this:

If Not IsNull(Me.txtSearchMRN) Then
Control = Null & "([MRN] = " & Me.txtSearchMRN & ") AND "
End If

Is that what you're suggesting?

[June7]

Are you positive the field type was changed to number?

If you want to provide db for analysis, follow instructions at bottom of my post.

[ItsMe]

Considering
'Purpose: Clear all the search boxes in the Form Header, and show all records again.

I was thinking
strWhere = ""
Then do what you need to in order to show all records again, then run your code to reset the form's control values

[fainterm]

hmm hang on a sec, i think i may have found that the Clear control is dependent on the search button being clicked first. It seems that if i enter text and hit search, after the query is executed the clear button works as designed with no 2448 error. However if i just input data into the controls without firing off the query it gives me that error.....not exactly how i want it to work, ideally the users could clear the fields at any time.

[June7]

That is odd. I have a similar setup and the clear button can be clicked at any time without error.

[fainterm]

Are you positive the field type was changed to number?

If you want to provide db for analysis, follow instructions at bottom of my post.

I would love to be able to, unfortunately my DB contains protected health information in it so i can't. I think for now i'm just going to tell the users that the clear button is dependant on at least 1 search having been done until i can get this thing debugged.

[ItsMe]

.. until i can get this thing debugged...

Maybe you can post all of the code that shows where and how

You declare the variables
Assign the variables their values
Use the search function
Clear the values of the unbound controls

Be sure to use the "Go Advanced" option to enclose your code in the special "Code" brackets to preserve formatting.

[ipisors]

"ctl.value=Null" would really be an unusual way to do it.

I would suggest this instead:

ctl.Value=""
or
ctl=vbNull

Also, are you sure you want to do this for ALL textboxes and comboboxes on your form? Could be you need some more differentiation ...

[rpeare]

Ummmm can I point out something here. If you have a LABEL you can not set a value of it. So for instance if you have an unbound control but you detached the label from the control you will encounter this issue.

Have you tried using your debug.print ctl.name in your code to see what field it's actually choking on?

Your code should be ignoring everything but text boxes and checkboxes but maybe there's something else in there that you need to account for.

Even if your actual database has protected information you can create a copy of it and put in garbage data just to simulate the real problem. Pretty much everyone that posts databases uses false data to protect their business.