Conditional Formatting continuous form using VBA

[Chad]

Hello, I have a continuous form that has 31 Text boxes in a row and they are
named Text1, Text2, Text3 and so on and when the form is opened the 31 Text
boxes are now 372 (31 horizontal x 12 vertical). I tried to use conditional
formatting but it only lets you use 3 formulas. What I’m trying to do is I
have 17 different letters ( X, P, V, XH, H, EA, UA, ET, UT, LE, DL, ML, FL,
PL, J, F, PC ) and each letter has its own color and if I type the letter “Pâ€
in one of the 372 text boxes I would like that box to turn blue or if I type
a letter “V†in one of the text boxes I would like it to turn green and so on
for the 17 different letters and if I type nothing then the color stays
white. How can I accomplish this?

Thanks,
Chad

 

[Allen Browne]

You cannot do that in a continuous form.

You could do it in a form that literally has 372 text boxes (i.e. a grid of
31 across and 12 high), using the AfterUpdate event of each one to change
the BackColor (as well as whatever event you use to load the form
initially.)

This would also let you hide the inappropriate boxes when the form loads
(such as Feb 29 - 31.)

Presumably you are actually storing the data in a normalized table, where
the values are not stored in 31 actual columns x 12 records per year.

 

[Chad]

Allen, First i would like to say its a pleasure speaking with you! Your posts
and site have helped me greatly... Yes I do belive my tables are normalized
and it only stores the Letter, Name ID and date in the table and only when
something is entered in the text boxes. I am having so much trouble with this
database due to not knowing enough VBA and being new to access. What im
trying to do is make a DB that keeps track of employees attendance and be
able to see every month and day on the form. I searched all over the net for
an example but with no luck. What can I do to make this work? Thanks!

 

[Peter Hibbs]

Chad,

A Flex Grid Control will probably do what you want but you will need
to write some VBA code. Have a look at -

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex Grid Demo.mdb'

for some examples.

Peter Hibbs.

 

[Aaron]

Hello, I have a continuous form that has 31 Text boxes in a row and they
are
named Text1, Text2, Text3 and so on and when the form is opened the 31
Text
boxes are now 372 (31 horizontal x 12 vertical). I tried to use
conditional
formatting but it only lets you use 3 formulas. What I’m trying to do is
I
have 17 different letters ( X, P, V, XH, H, EA, UA, ET, UT, LE, DL, ML,
FL,
PL, J, F, PC ) and each letter has its own color and if I type the letter
“Pâ€
in one of the 372 text boxes I would like that box to turn blue or if I
type
a letter “V†in one of the text boxes I would like it to turn green and
so on
for the 17 different letters and if I type nothing then the color stays
white. How can I accomplish this?

Thanks,
Chad

test!sorry~!nice to meet u!@

 

[Chad]

Peter, The link you gave gives page errors please post again if you
could...Thanks!
Here is what page error it gives:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

/Otherdownload.asp, line 32

 

[Douglas J. Steele]

Try again. It worked fine for me just now.

 

[Peter Hibbs]

Chad,

Try http://www.rogersaccesslibrary.com/index.htm and click on Other
Developers Libraries, you should find it there under my name.

Peter Hibbs.

 

[Chad]

Peter, I looked it over and wow I wouldnt know where to begin! Im new to VBA
but can work my way around access... Any I deas how I could start this? Im
trying to make a database that shows the year and for each day I would enter
a letter like the "P" or "V" for that employee ect. I have everything working
except for the canging of colors. Your thoughts?

Thanks,
Chad

 

[Peter Hibbs]

Chad,

It does not sound too difficult but it is hard to be specific with the
amount of information you have posted. If you are interested I would
be happy to write some flex grid code for you (free of charge of
course). If you contact me at the address in the documentation for the
flex grid and say what version of Access you are using I'll see what I
can do. If possible, it might be easier to send me a copy of your
database (zipped) with some more information on what exactly you are
trying to do.

Peter Hibbs.