asked on

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

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

Ryan Chong

perhaps u can provide us the sample data?

pls also note that the order sequence is seems important in ur case here.

Gustav Brock

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

edrz01

ASKER

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.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

edrz01

ASKER

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.

Gustav Brock

Great! Then please mark as answered.

/gustav

edrz01

ASKER

Points set and thank you very much again!