do loop order help (1 Viewer)

[eee333]

I'm new to do loops in access, and I have some code that almost does what I'd like it to do, but it needs some tweaking. I'm not sure if what I'm envisioning is possible though (or if maybe a do loop isn't the right way to do it?).
In the event form before update, I want it to look through everything tagged TLeft. If those tagged controls are blank, I want it to change the background color. Then, once it's gone through all of those controls, ideally I want it to tell the user which controls are blank in a messagebox--but I don't think that part is possible.

Currently with the code however, it gives them the messagebox each time it finds a blank. What I can't figure out is, is there a way to do that message outside of the loop but only in the case that one of the tagged controls is blank? So, don't tell the user every time, just once they've finished the loop, if any were blank?

     For Each ctl In Me.Controls
        If ctl.Tag = "TLeft" And IsNull(ctl) = True Then
            ctl.BackColor = RGB(255, 194, 14)
            MsgBox "you must fill in all fields"
            Cancel = True
        End If
    Next ctl

 

[plog]

Before the loop create a string variable, then in the loop replace your MSgBox line with a line that adds the current item to the string. When the loop ends, MsgBox that string.

Extra Credit: only call the MsgBox if the string has data in it.

 

[eee333]

so like

Dim Donut as String
For Each ctl In Me.Controls
        If ctl.Tag = "TLeft" And IsNull(ctl) = True Then
            ctl.BackColor = RGB(255, 194, 14)
            [ADDING CURRENT CONTROL TO STRING]
        End If
    Next ctl
If IsNull(Donut)=False Then Msgbox("Missing values for fields " & Donut): Cancel=True
End Sub

?

If that's the case, three questions
1. how...do I add the current control to the string?
2. Is there to add them in a nice format, so rather than just being like q2aq2b it's formatted as q2a, q2b
3. how do I reset Donut each time this happens so when the user fills in all of the missing fields Donut doesn't still read as blank?
?

Thanks!

 

[plog]

1 & 2. Something like:

Donut = ", " & Donut & 'This One Failed'

but with data from the loop to identify which one

3. Reset? You don't, everytime it encounters that code it creates a variable called donut with no data in it.

 

[eee333]

1 & 2. Something like:

Donut = ", " & Donut & 'This One Failed'

but with data from the loop to identify which one

That's what I'm wondering, how do I do that?
I currently have it set to

If ctl.Tag = "TLeft" And IsNull(ctl) = True Then ctl.BackColor = RGB(255, 194, 14): Donut= (", " & ctl.Name & " This one failed")
...
If IsNull(Donut) = False Then
        MsgBox ("Oh no" & Donut)
        Cancel = True

BUT, that is only including the results from one of the loops. How do I make it so each iteration tacks it on, not just replaces it?
Also for the reset, I was having an issue with the values being incorrect, but that must have been due to a different coding error, not that it was somehow retaining the old values.

 

[plog]

Look at this code closely. I recycle whats in Donut by setting it to Donut:

Donut = Donut & ', This One Failed'

That says take the value of donut and then put the value of donut in it plus this new value. That's how you keep adding stuff to Donut.

If your loop hits that code 3 times it Donut will look like this:

, This One Failed, This One Failed, This One Failed

 

[eee333]

Oh wow, reading comprehension failure. Thanks, yes, that works perfectly!