|
|
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
|
|
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
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"chazhead" <chazhead[ at ]gmail.com> wrote in message
news:0e0004e6-29ee-4d37-840a-fccb30c022c0[ at ]40g2000prx.googlegroups.com...
[Quoted Text] > 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
|
|
Thanks for the fast response Graham, I'll go play and report back :)
|
|
On 12 Dec, 12:40, chazhead <chazh...[ at ]gmail.com> wrote:
[Quoted Text] > 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.
|
|
"chazhead" <chazhead[ at ]gmail.com> wrote in message
news:2d262e56-748b-417d-b899-00563cedd206[ at ]v39g2000pro.googlegroups.com...
[Quoted Text] > On 12 Dec, 12:40, chazhead <chazh...[ at ]gmail.com> wrote:
>> 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.
That's very odd.
Graham's code certainly looks as though it should be checking all 12 boxes
before it displays a message.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
|
|
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.
|
|
>
[Quoted Text] > 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.
|
|
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>
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"chazhead" <chazhead[ at ]gmail.com> wrote in message
news:2d262e56-748b-417d-b899-00563cedd206[ at ]v39g2000pro.googlegroups.com...
[Quoted Text] > On 12 Dec, 12:40, chazhead <chazh...[ at ]gmail.com> wrote:
>> 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.
|
|
|