Need Help Modifying Code Snippet

[Sharkbyte]

In searching through the archives, I came upon a code snippet, which I find
very useful. It is from Arvin Meyer, and essentially resets a form.

For Each ctl In Me.Controls
With ctl
If .ControlType = acTextBox Then
.Enabled = True
.Value = ""
End If
End With
Next

What I want to do is load this into a shared function, so I only need call
the function, rather than adding the several lines of code, each time I wish
to use it.

Can someone help me with this? Access keeps giving me problems with the
"Me." keyword. But I can't see how to replace either the "Me." or
"Me.Controls", and have it work as a function.

TIA.

Sharkbyte

 

[Arvin Meyer [MVP]]

Try to add something like this in a Public Function saved in a standard
module:

Public Function EnableTextBox (frm As Form)
Dim ctl As Control

For Each ctl In frm.Controls
With ctl
If .ControlType = acTextBox Then
.Enabled = True
.Value = ""
End If
End With
Next

End Sub

So now just call the code from a form function:

Call EnableTextBox(Me)

 

[Sharkbyte]

Thanks, Arvin.

Another question...If I need to call it from one form, but apply it to
another? I tried entering the form name, but get an error:

Call EnableTextBox("frmMainMenu")

Any suggestions?

Sharkbyte

 

[Arvin Meyer [MVP]]

Try:

Call EnableTextBox(Forms!frmMainMenu)

BTW, I see an error. I should have used "End Function" instead of "End Sub"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Sharkbyte]

Thanks, again. And I caught the error.

Try:

Call EnableTextBox(Forms!frmMainMenu)

BTW, I see an error. I should have used "End Function" instead of "End Sub"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com





.

 

[Arvin Meyer [MVP]]

Those are the kinds of errors that occur when typing code directly into the
news posting instead of the IDE window. They also occur on occasion when I
copy and paste code from a previous posting.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Sharkbyte]

Two more functional questions...

1) Is there a way to run this code, and then go back and apply any default
values? The "reset" sets the field to "" so any default values are lost.
Right now I am loading them back in with code.

2) Is there a way to skip a field? I have one screen where the
ControlSource is calculated off the value of another field. The code errors
unless I set the ControlSource to "" before running it, and then reset the
ControlSource.

Thanks yet again.

Sharkbyte

 

[Arvin Meyer [MVP]]

Two more functional questions...

1) Is there a way to run this code, and then go back and apply any
default
values? The "reset" sets the field to "" so any default values are lost.
Right now I am loading them back in with code.

Sure, instead of:

..Value = ""

use:

..Value = .DefaultValue

2) Is there a way to skip a field? I have one screen where the
ControlSource is calculated off the value of another field. The code
errors
unless I set the ControlSource to "" before running it, and then reset the
ControlSource.

Try using the Tag property. Add a tag such as 2 to the textbox you do not
want to skip, and then change the code like:

With ctl
If .ControlType = acTextBox Then
If .Tag = 2 Then
.Enabled = True
.Value = .DefaultValue
End If
End If
End With

 

[Sharkbyte]

Arvin:

The .DefaultValue switch causes the field to ignore the Format of the field.
So I went back to loading defaults with code. Which is fine, it's just a
matter of needing to know as I use the code.

Using the .Tag property worked great, for skipping controls.

Thanks.

 

[David W. Fenton]

Public Function EnableTextBox (frm As Form)
Dim ctl As Control

For Each ctl In frm.Controls
With ctl
If .ControlType = acTextBox Then
.Enabled = True
.Value = ""
End If
End With
Next

Why set to a zero-length string? If the controls are bound to fields
that disallow ZLS (as they should be), you'll get a Null anyway, so
why not directly set them to Null?

 

[David W. Fenton]

Using the .Tag property worked great, for skipping controls.

You might want to consider using custom collections for this if this
is code you're calling in the OnCurrent event of your form or from a
command button (if it's only being called once when the form opens,
i.e., in the OnLoad event, setting up the custom collections won't
give any benefit). I discuss the issue and how to do it in this
recent StackOverflow post, with voluminous code examples:

http://tinyurl.com/y9ddaws =>
http://stackoverflow.com/questions/1917981/how-to-use-controls-collec
tion-in-access-2003-and-vba/1932103#1932103

I use custom collections for this purpose for forms where there is
conditional layout that fires in the OnCurrent event, and for
query-by-form interfaces where I have a "clear criteria" button.

 

[Arvin Meyer [MVP]]

Why set to a zero-length string? If the controls are bound to fields
that disallow ZLS (as they should be), you'll get a Null anyway, so
why not directly set them to Null?

Because that's exactly what he originally wanted? I simply helped him with
his code as he requested.

 

[Sharkbyte]

In this case, the fields are unbound. So Arvin was providing the information
I was looking for.

 

[David W. Fenton]

Because that's exactly what he originally wanted? I simply helped
him with his code as he requested.

But he attributed the code he was using to *you*, right? So he was
copying something you had written (and seeming not understanding the
code very well).

 

[David W. Fenton]

In this case, the fields are unbound. So Arvin was providing the
information I was looking for.

Do you need to distinguish zero-length string from Null? If not, why
not use Null? Unbound textboxes initialize as Null, so it seems to
me that it would easier to use Null for re-initializing, because
then you'd never be in danger of having ZLS in some controls and
Null in others (and thus need to test for both).

 

[Sharkbyte]

David:

Given your explanation, below, your previous comments make much more sense.
And I thank you for them.

However, in the case of my current app, I believe I may be better served by
the .DefaulValue switch. Unless you can provide a change to accomodate both
the NULL, yet still load any defaults.

One other question:

because then you'd never be in danger of having ZLS in some controls and
Null in others (and thus need to test for both).

In this case, wouldn't you still need to test for ZLS, to account for any
users who might enter one?

Thanks, again.

Sharkbyte

 

[David W. Fenton]

Given your explanation, below, your previous comments make much
more sense. And I thank you for them.

However, in the case of my current app, I believe I may be better
served by the .DefaulValue switch. Unless you can provide a
change to accomodate both the NULL, yet still load any defaults.

I agree that the default value is a more robust and versatile
approach. I would make sure that controls I wanted reset to Null had
Null explicitly set as the default value, since if there is no
default value, the .DefaultValue property returns a ZLS, and if you
set the control to that, you'd have the same ZLS problem.

I have only recently begun using default values in this kind of
context (it's not like I build these kinds of forms every day), and
hadn't really through any of this, so this discussion has proven
quite helpful to me.

One other question:


In this case, wouldn't you still need to test for ZLS, to account
for any users who might enter one?

I don't believe it's possible to do so. When I enter data into an
unbound control and then delete it, the control returns Null.

Can you come up with a way to enter a ZLS into an unbound text box,
short of using the Immediate Window and assigning it directly? Maybe
I'm missing something...

 

[Arvin Meyer [MVP]]

I think it came from a completely different bit of code based upon:

http://www.datastrat.com/Code/LockIt.txt