Loop through each field in a record

[gmazza via AccessMonster.com]

Hi there,
I have a table with many columns. I need to go through each column to see if
its null or not before I do a certain action.
How can I do this through a loop?

Thanks!

 

[Dirk Goldgar]

Hi there,
I have a table with many columns. I need to go through each column to see
if
its null or not before I do a certain action.
How can I do this through a loop?

Questions for you:

Are you talking about controls on a form, or fields in a recordset, or
(combining these) fields in the form's recordset?

Would the record have been modified but not yet saved, or would you be doing
this only with saved records?

Assuming you're doing this on a form, are you interested only in bound
controls?

 

[gmazza via AccessMonster.com]

Thanks for your reply Dirk!
fields in a recordset is all I want to cycle through.
only saved records.
How can I exit the loop instantly once I find a null field?

 

[Dirk Goldgar]

Thanks for your reply Dirk!
fields in a recordset is all I want to cycle through.
only saved records.
How can I exit the loop instantly once I find a null field?

Here's an example:

'------ start of example code ------
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim blnFoundNullField As Boolean

Set rs = ... ' something appropriate

For Each fld In rs.Fields

If IsNull(fld.Value) Then
blnFoundNullField = True
Exit For
End If

Next fld

Set fld = Nothing
rs.Close ' ONLY DO THIS IF YOU OPENED THE RECORDSET
Set rs = Nothing

If blnFoundNullField Then
MsgBox "Found a Null field!"
Else
' Do something
End If
'------ end of example code ------

 

[gmazza via AccessMonster.com]

Thats exactly what I wanted Dirk! One last thing if you can.
When I find a not null value from the recordset, I want to assign the value
to a label on my current form.
Every not null value I want to assign to a label in my form.
Once I hit a null I want out, then make the rest of the labels on the form
invisible.

I am going to have 10 labels on the form, named Lable1 - Label10
The table will not have more than 10 not null values for the specific field I
am checking for, but it may have less.
So I need to assign the first Not Null value from the table to Label1 on the
form, and keep going until the table's value is Null, then, if we only got up
to Label6, I need to make Label's 7-10 invisible.

Is that possible?
I can provide more detail if needed. Thanks!!

 

[Dirk Goldgar]

Thats exactly what I wanted Dirk! One last thing if you can.
When I find a not null value from the recordset, I want to assign the
value
to a label on my current form.
Every not null value I want to assign to a label in my form.
Once I hit a null I want out, then make the rest of the labels on the form
invisible.

I am going to have 10 labels on the form, named Lable1 - Label10
The table will not have more than 10 not null values for the specific
field I
am checking for, but it may have less.
So I need to assign the first Not Null value from the table to Label1 on
the
form, and keep going until the table's value is Null, then, if we only got
up
to Label6, I need to make Label's 7-10 invisible.

Is that possible?

Quite simple, if I understand you properly. You can take advantage of the
fact that controls can be indexed by name in the form's Controls collection.
Something like this:

'------ start of example code ------
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim blnFoundNullField As Boolean
Dim intCount As Integer

Set rs = ... ' something appropriate

For Each fld In rs.Fields

If IsNull(fld.Value) Then
blnFoundNullField = True
Exit For
Else
intCount = intCount + 1
With Me.Controls("Label" & intCount)
.Caption = fld.Value
.Visible = True
End With
End If

Next fld

' Clean up objects.
Set fld = Nothing
rs.Close ' ONLY DO THIS IF YOU OPENED THE RECORDSET
Set rs = Nothing

' Hide unused labels.
While intCount < 10
intCount = intCount + 1
Me.Controls("Label" & intCount).Visible = False
Wend

If blnFoundNullField Then
MsgBox "Found a Null field!"
Else
' Do something
End If
'------ end of example code ------

 

[gmazza via AccessMonster.com]

Excellent! I will try this out, looks like what I need.
I thank you for your help, it is appreciated.
P.S. Is there anywhere we can donate to these forums, they have been a huge
help to me and I don't understand how you guys/girls get paid for your time?
Thanks again,
Gino

Thats exactly what I wanted Dirk! One last thing if you can.
When I find a not null value from the recordset, I want to assign the

[quoted text clipped - 15 lines]

Is that possible?

Quite simple, if I understand you properly. You can take advantage of the
fact that controls can be indexed by name in the form's Controls collection.
Something like this:

'------ start of example code ------
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim blnFoundNullField As Boolean
Dim intCount As Integer

Set rs = ... ' something appropriate

For Each fld In rs.Fields

If IsNull(fld.Value) Then
blnFoundNullField = True
Exit For
Else
intCount = intCount + 1
With Me.Controls("Label" & intCount)
.Caption = fld.Value
.Visible = True
End With
End If

Next fld

' Clean up objects.
Set fld = Nothing
rs.Close ' ONLY DO THIS IF YOU OPENED THE RECORDSET
Set rs = Nothing

' Hide unused labels.
While intCount < 10
intCount = intCount + 1
Me.Controls("Label" & intCount).Visible = False
Wend

If blnFoundNullField Then
MsgBox "Found a Null field!"
Else
' Do something
End If
'------ end of example code ------

 

[Dirk Goldgar]

Excellent! I will try this out, looks like what I need.
I thank you for your help, it is appreciated.

You're welcome.

P.S. Is there anywhere we can donate to these forums, they have been a
huge
help to me and I don't understand how you guys/girls get paid for your
time?

We don't -- except for the satisfaction of helping people and the fun of
problem-solving. Just "pay it forward" by helping others when you can.