We help IT Professionals succeed at work.

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

edrz01
edrz01 asked
on
200 Views
Last Modified: 2017-05-05
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
Comment
Watch Question

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT

Commented:
perhaps u can provide us the sample data?

pls also note that the order sequence is seems important in ur case here.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Use a loop and the RecordsetClone:

Function SelectLabel()

    Dim rs As DAO.Recordset
    Dim Item As Integer
    Dim Color As Long

    Set rs = Me.RecordsetClone

    If rs.RecordCount = 4 Then
        For Item = 1 To 4
            If IsNull([Shipping_Name]) And IsNull([Address]) Then
                Color = vbRed
            Else
                Color = vbGreen
            End If
            Me("bx_" & CStr(Item).BackColor = Color
            rs.MoveNext
        Next       
    End If

    Set rs = Nothing

End Function 

Open in new window

/gustav

Author

Commented:
Gustav, your example works somewhat... it does count 4 records, however the IsNull is reported for all four records. I have test data is record position 3. Records 1,2 and 4 are empty. Although the for Next loop works, I'm not sure why it cannot see record 3 has the fields filled. Found that the record pointer is stuck on the first record and is not advancing to the other three.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Gustav... that was perfect. I kept looking at the corrected line and did not think of adding the rs or .value... now it is perfect. Thank you very much.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Great! Then please mark as answered.

/gustav

Author

Commented:
Points set and thank you very much again!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.