troubleshooting Question

Create a VBA Loop in Access to check records and create and action

Avatar of edrz01
edrz01Flag for United States of America asked on
Microsoft AccessVBA
7 Comments1 Solution213 ViewsLast Modified:
I have created an Access for to replicate 4 shipping labels. There is a table called tbl_Labels4, which is the Data Source for the form.
I would like the form to check the table for the four records.

The default BackColor for the Label Box (bx_1 etc) is vbRed. When the loop checks the records and records 1 & 2 are blank. And Record 3 has a Shipping Name and Address, then the BackColor should be changed to vbGreen. Hence if Record 4 is blank, then the BackColor will be vbRed.

I’ve tried a few loops but have failed in my attempts. Can anyone assist.

Here is the original IF Then statement I used in a Function and had Form_Current call the below code:
Function SelectLabel()
DoCmd.GoToRecord , , acFirst
    If IsNull([Shipping_Name]) = True And IsNull([Address]) = True Then
            bx_1.BackColor = vbRed
    Else
            bx_1.BackColor = vbGreen
    End If
DoCmd.GoToRecord , , acNext
    If IsNull([Shipping_Name]) = True And IsNull([Address]) = True Then
            bx_2.BackColor = vbRed
    Else
            bx_2.BackColor = vbGreen
    End If
DoCmd.GoToRecord , , acNext
    If IsNull([Shipping_Name]) = True And IsNull([Address]) = True Then
            bx_3.BackColor = vbRed
    Else
            bx_3.BackColor = vbGreen
    End If
DoCmd.GoToRecord , , acNext
    If IsNull([Shipping_Name]) = True And IsNull([Address]) = True Then
            bx_4.BackColor = vbRed
    Else
            bx_4.BackColor = vbGreen
    End If
End Function
ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 7 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 7 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004