Command Button Clears Text Field But Get "Can't Find the Macro" Error

[Maurita]

Hi, hope someone can help me with a problem I can't seem to solve. I
have a form with a command button which adds a new record on a form.
On the form, I also have two unbound text boxes that I need to clear
when I click on the command button to add a new record. When I click
on the command button, the fields clear, but I get an error that
states that access can't find the macro; but I've not created any
macros. Along with the code that Access put in for the add new record
button, I also used the following code:

Dim ctl As Control
On Error Resume Next
For Each ctl In Me.Controls
With ctl
.Value = Null
End With
Next

I appreciate any help in this matter. I am at a loss as to what I'm
doing wrong.

Thank you.

Maurita Searcy

 

[Allen Browne]

Comment out the error handler in the 2nd line, i.e.:
' On Error Resume Next

Hopefully you will now be able to see what's going wrong.

You should not need to assign Null if you are going to a new record. If the
new record starts with some values, clear the Default Value property of the
text box, or the Default Value property of the field in the table.

The attempt to assign Null will fail if the field's Required property is set
(in table design.)

If you are trying to clear a partially entered (but unsaved) new record,
undo the control or the form:
Me.Undo

 

[Linq Adams via AccessMonster.com]

This type of error frequently comes when something has been entered in a
property of a control that shouldn't be there. Sometimes it's as simple as an
entry that's been partially deleted; a period can be left behind and be hard
to see. When Access can't evaluate whatever this is, it assumes that it's
the name of a macro, and when it can't locate the macro, gives this error
message.

In Design View, select the command button then goto Properties - Events.
There should be nothing in any property box except the On Click Property,
which should say

[Event Procedure]

Make sure that that's all that's in that one, and nothing is in any other
property.

Another possibility is that the error message is wrong! Access isn't known
for the accuracy of its error messages. Code like this

Dim ctl As Control
On Error Resume Next
For Each ctl In Me.Controls
With ctl
.Value = Null
End With
Next

will cause an error if you have controls on the form that don't have a
property (in this case Value) that you're trying to assign.to them.

In the code above, you're trying to assign a Value of Null to all controls,
including your command button, and command buttons don't have Values!

To avoid this, you have to modify your code to be applied ONLY to controls
that have the Property in question. So your code would need to be something
like this

Dim ctl As Control
On Error Resume Next
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
With ctl
.Value = Null
End With
End If
Next

The code will now only assign a Value of Null to textboxes.

There may be other things going on, but you need to check for the stray
characters in the Property Box, as outlined above, and you need to modify
your code as above.

The one thing you have to remember when you get an error message that halts
code execution is that you could have ***multiple*** errors! The first error
stops execution, and so you don't see the subsequent error(s) until the first
error is corrected.

Unfortunately, I'm heading to the hospiltal shortly, and won't be on-line for
a number of days, but someone else will help you if you have further problems.


Good luck!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com