Check value of multiple text boxes

[chazhead]

Hi all

I have a sub form that contains 12 text boxes, named funnily enough
Jan, Feb, Mar....ect.
Each of these text boxes is controled by value from a query.

Now when a user updates the relevant part of the main form the subform
refreshes showing the revised data.

I use conditional formating to set the colours of the textbox
depending on the value, which works great, but, what I would like to
do is after any refresh is have a message box pop up listing any
textboxes that fallen within the criteria I have specified.

e.g after update, the months of Jan, April & Nov have values below 8,
I want a msg box to appear listing those 3 months.

I have looked at using the following:

If Me.Jan < 8 Then
Msgbox"Jan is less than 8"
Elseif me.Feb <8 Then
Msgbox"Feb is less than 8"

etc etc

but there must be a MUCH better way of doing it and having it popup
for every month.


Any guidance much appreciated as always.


Chaz

 

[Graham Mandeno]

Hi Chaz

You can translate an integer with a value (n) from 1 to 12 into the 3-letter
abbreviation of the corresponding month like this:
Format( DateSerial( 0, n, 1), "mmm" )

So, if you have 12 textboxes named "Jan", Feb" etc, you can iterate through
them like this:

Dim n as Integer, mmm as String, sMsg as String
For n = 1 to 12
mmm = Format( DateSerial( 0, n, 1), "mmm" )
If Me(mmm) < 8 then
sMsg = mmm & " is less than 8" & vbNewLine
End If
Next n
If Len(sMsg) <> 0 then MsgBox sMsg

 

[chazhead]

Thanks for the fast response Graham, I'll go play and report back

 

[chazhead]

Thanks for the fast response Graham, I'll go play and report back

OK, that is so close, just when I have more then one month with a
value of lees than 8 the msgbox returns only one month.

So, lets say initially all months are greater than 8, I change Jan to
less than 8 and the msgbox appears correctly, I then change march to
less than 8 and the msgbox appears with only march as less than 8, and
what I was hoping for was for the msgbox to show both jan & march
within the one msgbox. Sorry if I did not explain that in earlier
post.

Any ideas please & thank you.

 

[Douglas J. Steele]

OK, that is so close, just when I have more then one month with a
value of lees than 8 the msgbox returns only one month.

So, lets say initially all months are greater than 8, I change Jan to
less than 8 and the msgbox appears correctly, I then change march to
less than 8 and the msgbox appears with only march as less than 8, and
what I was hoping for was for the msgbox to show both jan & march
within the one msgbox. Sorry if I did not explain that in earlier
post.

That's very odd.

Graham's code certainly looks as though it should be checking all 12 boxes
before it displays a message.

 

[chazhead]

Ah hah...solved.

Ended up using:

Dim n As Integer, mmm As String, sMsg As String
For n = 1 To 12
mmm = Format(DateSerial(0, n, 1), "mmm")
If Me(mmm) < 8 Then
sMsg = sMsg & mmm & ","
End If
Next n
If Len(sMsg) <> 0 Then MsgBox sMsg & "are <8." & vbNewLine

Many thanks all.

 

[Mike Painter]

If Me.Jan < 8 Then
Msgbox"Jan is less than 8"
Elseif me.Feb <8 Then
Msgbox"Feb is less than 8"

This will stop at the first true value since the condition has been
satisfied.
Else is done only when the condition is not true

If Me!Jan < 8 Then
Msgbox"Jan is less than 8"
end if
If me!Feb <8 Then
Msgbox"Feb is less than 8"
end if
....
If me!Dec <8 Then
Msgbox"Dec is less than 8"
end if

This may pop up a bunch of boxes and you could build a string and present it
in one box at the end.

It could also be reduced to a loop (An exercise left to the reader) which
might take longer than cutting and pasting the If statements.

 

[Graham Mandeno]

Sorry - this line:

sMsg = mmm & " is less than 8" & vbNewLine

should read:

sMsg = sMsg & mmm & " is less than 8" & vbNewLine

That's what comes of answering questions after 1 am <g>