Nulls & variables

D

Del

I'm using Access 2000. I want to check several controls for null. If it
were a single control I would use this code:

If IsNull(Me.MyControl1) then
Do my stuff
End If

But I have several controls so I tried the following code without success:

Do
intLoop1 = intLoop1 + 1
strControl = "me.MyControl" & intLoop1

If IsNull(strLotControl) Then
MsgBox "Empty"
End If
Loop Until intLoop1 = 8

I imagine there are better ways to do this and I'm open to improvement, but
I would also like to know how to use a variable here.
 
T

Tom van Stiphout

On Tue, 30 Oct 2007 15:48:00 -0700, Del

You are very close. Use the Controls collection:
for intLoop1 = 1 to 8
strControl = "MyControl" & intLoop1
if IsNull(Me.Controls(strControl)) then
msgbox strControl & " is Empty"
end if
next intLoop1

-Tom.
 
J

J_Goddard via AccessMonster.com

What do you mean when you say "without success"?

Check your code; you are putting the control name into strControl, but the if
isnull() statement checks strLotControl.

Use: strControl = "MyControl" & intLoop1

and the if statement should be

If IsNull(me(strControl)) Then
MsgBox "Empty"
End If

This code assumes your controls are named MyControl1, MyControl2,...

John
 
M

Marshall Barton

Del said:
I'm using Access 2000. I want to check several controls for null. If it
were a single control I would use this code:

If IsNull(Me.MyControl1) then
Do my stuff
End If

But I have several controls so I tried the following code without success:

Do
intLoop1 = intLoop1 + 1
strControl = "me.MyControl" & intLoop1

If IsNull(strLotControl) Then
MsgBox "Empty"
End If
Loop Until intLoop1 = 8

I imagine there are better ways to do this and I'm open to improvement, but
I would also like to know how to use a variable here.
Click to expand...


I think your code should be more like:

For intLoop1 = 1 To 8
strControl = "MyControl" & intLoop1
If IsNull(Me(strLotControl)) Then
MsgBox strLotControl & " is empty"
End If
Next intLoop1
 
D

Del

Thanks. Your code works well.

I'm self-taught in VBA and I'm always looking for ways to improve. I
noticeds that the other replies didn't use "If IsNull(Me.Controls(MyControl))
Then", but "If IsNull(Me(MyControl)) Then". Both seem to work. What is the
advantage of having "Me.Controls" in the code instead of just "Me"?
 
T

Tom van Stiphout

On Wed, 31 Oct 2007 10:04:01 -0700, Del

No advantage. My code just explicitly references the Controls
collection, while Me(MyControl) does the same implicitly.

In DotNet there are no implicit/default collections or properties. You
might as well get used to it.

-Tom.
 
Top