Setting Control values to a value like Null 3

[sanan]

Hi there
I have a form with bunch of TxtBoxes, comboBoxes, Buttons, and a subform, I would like to set the value of all the TxtBoxes and comboBoxes in my Form (Not the subform) to Null.
I know one way to do it is by calling each one of them like;
Me.InvoiceID100.Value = Null
But since there are too many of them, I would like to use some thing like this;
Private Sub Command8_Click()
Dim i As Integer
For i = 0 To Me.Controls.Count - 1
Me.Controls(i).value = null
Next i
End Sub

But it is not working, could you tell me why?
Also If I want to exclude a Control by name of “salesID”, How can I do it?

Best regards
Sanan

 

[lewds]

HI..

Dim ctls As Controls
Dim ctl As Control
Set ctls = Me.Controls
For Each ctl In ctls
If ctl.ControlType = acTextBox AND ctl.Name <> "salesID" Then
ctl.Value = Null
End If
Next
Set ctls = Nothing

You were attempting to set the Controls Collection to Null.

 

[Zion7]

Just for the record sanan & lewds, sanan's code was correct, but was referencing All the controls, not just the ones that have a VALUE property.

Lewds, maybe that is what you meant? Your code obviously is fine, I'm just saying, for sanan's sake, all sanan needed was a control type clause, like yours...

Dim i As Integer
For i = 0 To Me.Controls.Count - 1
If ctl.ControlType = acTextBox AND ctl.Name <> "salesID" Then

Me.Controls(i).value = null
End If
Next i
End Sub


again, lewds your code is fine, sanan's was technically correct, just referencing TOO many controls.
(just so Sanan knows).

either way, good code lewds!

 

[sanan]

Hi lewds, Zion7
Thanks so much for your comments.
To my Surprise, I tried both techniques, But no success as of yet.
Here are the codes;

Private Sub Command156_Click()
Dim i As Integer
For i = 0 To Me.Controls.Count - 1
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox And ctl.Name <> "salesID" Then
Me.Controls(i).Value = Null
End If
Next i
End Sub
I just added “Or ctl.ControlType = acComboBox” to the codes, since I also have ComboBoxes, which I want to set to Null.
I tried the exact coding from lewds’s technique too, But no success.

Best regards
Sanan

 

[Zion7]

Sorry Sanan, a little rash, on my part...

Private Sub Command156_Click()

Dim i As Integer
For i = 0 To Me.Controls.Count - 1
If (Me.Controls(i).ControlType = acTextBox Or Me.Controls(i).ControlType = acComboBox) _
And Me.Controls(i).Name <> "salesID" Then
Me.Controls(i).Value = Null
End If
Next i

End Sub

Good Luck!

 

[Zion7]

Just for the record, Lewds works fine also (as is)?

Dim ctls As Controls, ctl As Control
Set ctls = Me.Controls
For Each ctl In ctls
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox) And ctl.Name <> "salesID" Then
ctl.Value = Null
End If
Next
Set ctls = Nothing

Just curious?

 

[RoyVidar]

What is the defenition of "no success"? Any errormessages?

The code looks fine, some things to perhaps look out for is
1 - some of the controls may be calculated -> can't be set to Null (see the faq linked below)
2 - if the controls are bound to fields in the underlying recordsource, you'll get a message relating to primary key violation
3 - if the form is bound, and the link child/master thingies of the subform control is set, you'll get messages relating to that

You might find something of use here faq702-5010

Roy-Vidar

 

[sanan]

Hi lewds, Zion7
Finally lewds’s technique worked for me.
But I have another question on it; I have another version of it, which is as follow;

Dim ctls As Controls
Dim ctl As Control
Set ctls = Me.Controls
For Each ctl In ctls
If ctl.ControlType = acTextBox Or ctl.contolType = acComboBox And IsNull(ctl.Value) Then
ctl.Value = "*"
End If
Next
Set ctls = Nothing

As you can see I am trying to find all the txtBoxes, and ComboBoxes Which have a Null Value, and then I would like to put a value of “*” for them.
I just have a hard time to locate the Controls with value Null;
IsNull(ctl.Value) Then

I tried “IsNull(me.controls)” Then or “IsNull(me.control)” Then

But no success.

Also Zion, On my own technique, I think I have a Object naming Problem “ctl.ControlType”, I think we have Dim “ctl” to some thing.

Best Regards
Sanan

 

[lewds]

Hi..

Instead of the Null for Value, Try playing around with Text being ""



Private Sub Command0_Click()

Dim ctls As Controls
Dim ctl As Control
Set ctls = Me.Controls
For Each ctl In ctls
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox) And ctl.Name <> "salesID" Then
ctl.SetFocus
ctl.Text = ""
End If
Next
Set ctls = Nothing

End Sub



Private Sub Command1_Click()
Dim ctls As Controls
Dim ctl As Control
Set ctls = Me.Controls
For Each ctl In ctls
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox) And ctl.Name <> "salesID" Then
ctl.SetFocus
If ctl.Text = "" Then ctl.Text = "*"
End If
Next
Set ctls = Nothing
End Sub

Just as a thought... Are you attempting to just clear a form for new data entry?

 

[RoyVidar]

It might be the combo or text controls is not Null, but contain "". What happens if the control is a text control in your test?

Try:
[tt]? True or False and False
? (True or False) and False[/tt]

in the immediate pane (ctrl+g)

In your case, you will probably assign "*" regardless of whether it is Null or not (your criterion is whether it is a text control OR whether it is a combobox which is null) - but (may I say, as usual) you have not told us anything about what the problem is. WOULD YOU PLEASE TRY TO DO SO IN THE FUTURE, INCLUDE ERRORMESSAGES, WHAT HAPPENS WHAT DOES NOT HAPPEN, WHAT IS SUPPOSED TO HAPPEN? AND - YES - I AM SHOUTING!) Do take the time to read faq181-2886, perhaps especially #14.

lewds have probably figured at least one problem out (perhaps all?), notice the brackets (also lewds are asking some of the same questions as I commented on).

One of the reasons for
1 - asking questions
2 - requiring information

is that it makes it easier to answer. When being as secretive as you seem to be, having us guess what the problem is, it makes it hard to be helpful. And some of the grumpier ones (i e yours truly), sometimes get [insert fitting term here].

We still haven't the foggiest whether you're trying to do this on bound controls (which won't work - at least not without somewhat more errortesting...) or unbound controls...

Here's another test, which will reliabely thest for Null or "" for either combos or text controls, and another way of setting it up (incidently rather close to some of the samples in the faq I posted)

[tt]dim ctl as control
for each ctl in me.controls
select case ctl.controltype
case actextbox, accombobox
if ctl.name <> "salesid" then
if trim$(ctl.value & "") = "" then
ctl.value="*"
end if
end if
end select
next ctl
set ctl=nothing[/tt]

- typed not tested...

Roy-Vidar

 

[Zion7]

just for the record, is this not a valid syntax for the IsNull().


If IsNull(ctl.Name) Then...

not dismissing all other suggestions, just assuming, it's still an option?

And also Sanan, look closely at how I referenced the control type. Not ctl.ControlType, but Me.Controls(i).ControlType . (Assuming you didn't). Again just so you know, your code is very viable. Worked for me fine.

Good Luck either way.

 

[PHV]

Zion7, If IsNull(ctl.Name) Then... is a valid syntax for testing Null only.
For testing Null, Empty and Blank (all in one):
If Trim(ctl.Name & "") = "" Then...

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

 

[Zion7]

PHV, thank-you very much.

I didn't expect that IsNull(ctl.Name) covered ALL angles, I just wanted to clarify the syntax.
But all the same, As I should've payed more attention to Roy's post, it never occured to me, that there was one expression, Trim(ctl.Name & "") = "" , that could cover all
Empty, Null & Blank entries, in one fell swoop,(seems very obvious now).

Thanks again!

 

[sanan]

Hi RoyVidar
It worked at first try.
A star for you, But I know it is not enough for you.

Also Thanks to Zion7, and PHV

Best Regards
Sanan