Need Help with Case statement with conditional formatting

M

MochaTrpl

My current learn as you go code is just a simple if then statement.
I
am think the best thing is to change to a Case statement since there
are 30 sets of the code as shown below which exceeds the limits of
the
Proceedure size. My thought is that it would be best to call the
Case
statement above the next then just "add the differance" yet I am not
sure how to go about this. I GoSub a reset before ease Case. Can I
add a second GoSub to run the previous Case or am I going about this
wrong. This is for a form to display selected fields.

Another option if possible would be to shorten the statements which
are nothing but conditional formattings. Can I simplify things by
creating a range... cbolyrtype3 through cbolyrtype30.enable ??? is
this possible? As you can well see, my "code" is simple but crude
but I have now exceeded my capabilities.



Private Sub cbolyrs_BeforeUpdate(Cancel As Integer)


If (cboLyrs = 1) Then
GoSub LyrReset
chkBGA.Visible = True
chkBGA.Enabled = True
ILTW.Visible = False
ILAG.Visible = False
ILTW.Enabled = False
ILAG.Enabled = False
ILTW.Value = ""
ILAG.Value = ""
lbl1line.Visible = True
cbolyr1cu.Visible = True
cbolyr1cu.Enabled = True
cbolyr2cu.Visible = True
cbolyr2cu.Enabled = False
cbolyr1type.Visible = True
cbolyr1type.Enabled = True
cbolyr2type.Visible = True
cbolyr2type.Enabled = False
cbolyr2type.Value = "BLANK"
cbolyr3type = Null
cbolyr4type = Null
cbolyr5type = Null
cbolyr6type = Null
cbolyr7type = Null
cbolyr8type = Null
cbolyr9type = Null
cbolyr10type = Null
cbolyr11type = Null
cbolyr12type = Null
cbolyr13type = Null
cbolyr14type = Null
cbolyr15type = Null
cbolyr16type = Null
cbolyr17type = Null
cbolyr18type = Null
cbolyr19type = Null
cbolyr20type = Null
cbolyr21type = Null
cbolyr22type = Null
cbolyr23type = Null
cbolyr24type = Null
cbolyr25type = Null
cbolyr26type = Null
cbolyr27type = Null
cbolyr28type = Null
cbolyr29type = Null
cbolyr30type = Null
cbolyr2cu.Value = 0
cbolyr3cu = Null
cbolyr4cu = Null
cbolyr5cu = Null
cbolyr6cu = Null
cbolyr7cu = Null
cbolyr8cu = Null
cbolyr9cu = Null
cbolyr10cu = Null
cbolyr11cu = Null
cbolyr12cu = Null
cbolyr13cu = Null
cbolyr14cu = Null
cbolyr15cu = Null
cbolyr16cu = Null
cbolyr17cu = Null
cbolyr18cu = Null
cbolyr19cu = Null
cbolyr20cu = Null
cbolyr21cu = Null
cbolyr22cu = Null
cbolyr23cu = Null
cbolyr24cu = Null
cbolyr25cu = Null
cbolyr26cu = Null
cbolyr27cu = Null
cbolyr28cu = Null
cbolyr29cu = Null
cbolyr30cu = Null
CboMatl12.Visible = True
CboMatl12.Enabled = True


ElseIf (cboLyrs = 2) Then
GoSub LyrReset
chkBGA.Visible = True
chkBGA.Enabled = True
ILTW.Visible = False
ILAG.Visible = False
ILTW.Enabled = False
ILAG.Enabled = False
ILTW.Value = ""
ILAG.Value = ""
lbl1line.Visible = True
lbl2line.Visible = True
cbolyr1cu.Visible = True
cbolyr2cu.Visible = True
cbolyr1cu.Enabled = True
cbolyr2cu.Enabled = True
cbolyr1type.Visible = True
cbolyr2type.Visible = True
cbolyr1type.Enabled = True
cbolyr2type.Enabled = True
cbolyr3type = Null
cbolyr4type = Null
cbolyr5type = Null
cbolyr6type = Null
cbolyr7type = Null
cbolyr8type = Null
cbolyr9type = Null
cbolyr10type = Null
cbolyr11type = Null
cbolyr12type = Null
cbolyr13type = Null
cbolyr14type = Null
cbolyr15type = Null
cbolyr16type = Null
cbolyr17type = Null
cbolyr18type = Null
cbolyr19type = Null
cbolyr20type = Null
cbolyr21type = Null
cbolyr22type = Null
cbolyr23type = Null
cbolyr24type = Null
cbolyr25type = Null
cbolyr26type = Null
cbolyr27type = Null
cbolyr28type = Null
cbolyr29type = Null
cbolyr30type = Null
cbolyr3cu = Null
cbolyr4cu = Null
cbolyr5cu = Null
cbolyr6cu = Null
cbolyr7cu = Null
cbolyr8cu = Null
cbolyr9cu = Null
cbolyr10cu = Null
cbolyr11cu = Null
cbolyr12cu = Null
cbolyr13cu = Null
cbolyr14cu = Null
cbolyr15cu = Null
cbolyr16cu = Null
cbolyr17cu = Null
cbolyr18cu = Null
cbolyr19cu = Null
cbolyr20cu = Null
cbolyr21cu = Null
cbolyr22cu = Null
cbolyr23cu = Null
cbolyr24cu = Null
cbolyr25cu = Null
cbolyr26cu = Null
cbolyr27cu = Null
cbolyr28cu = Null
cbolyr29cu = Null
cbolyr30cu = Null
CboMatl12.Visible = True
CboMatl12.Enabled = True
CboMatl23 = Null
CboMatl34 = Null
CboMatl45 = Null
CboMatl56 = Null
CboMatl67 = Null
CboMatl78 = Null
CboMatl89 = Null
CboMatl910 = Null
CboMatl1011 = Null
CboMatl1112 = Null
CboMatl1213 = Null
CboMatl1314 = Null
CboMatl1415 = Null
CboMatl1516 = Null
CboMatl1617 = Null
CboMatl1718 = Null
CboMatl1819 = Null
CboMatl1920 = Null
CboMatl2021 = Null
CboMatl2122 = Null
CboMatl2223 = Null
CboMatl2324 = Null
CboMatl2425 = Null
CboMatl2526 = Null
CboMatl2627 = Null
CboMatl2728 = Null
CboMatl2829 = Null
CboMatl2930 = Null
LyrReset:


chkBGA.Visible = False
chkBGA.Enabled = False
lbl1line.Visible = False
lbl2line.Visible = False
lbl3line.Visible = False
lbl4line.Visible = False
lbl5line.Visible = False
lbl6line.Visible = False
lbl7line.Visible = False
lbl8line.Visible = False
lbl9line.Visible = False
lbl10line.Visible = False
lbl11line.Visible = False
lbl12line.Visible = False
lbl13line.Visible = False
lbl14line.Visible = False
lbl16line.Visible = False
lbl17line.Visible = False
lbl18line.Visible = False
lbl19line.Visible = False
lbl20line.Visible = False
lbl21line.Visible = False
lbl22line.Visible = False
lbl23line.Visible = False
lbl24line.Visible = False
lbl25line.Visible = False
lbl26line.Visible = False
lbl27line.Visible = False
lbl28line.Visible = False
lbl29line.Visible = False
lbl30line.Visible = False
'lbl0lyr.Visible = False
'lbl1lyr.Visible = False
'lbl2lyr.Visible = False
'lbl3lyr.Visible = False
'lbl4lyr.Visible = False
'lbl5lyr.Visible = False
'lbl6lyr.Visible = False
'lbl7lyr.Visible = False
'lbl8lyr.Visible = False
'lbl9lyr.Visible = False
'lbl10lyr.Visible = False
'lbl11lyr.Visible = False
'lbl12lyr.Visible = False
'lbl13lyr.Visible = False
'lbl14lyr.Visible = False
'lbl15lyr.Visible = False
'lbl16lyr.Visible = False
'lbl17lyr.Visible = False
'lbl18lyr.Visible = False
'lbl19lyr.Visible = False
'lbl20lyr.Visible = False
cbolyr1cu.Visible = False
cbolyr2cu.Visible = False
cbolyr3cu.Visible = False
cbolyr4cu.Visible = False
cbolyr5cu.Visible = False
cbolyr6cu.Visible = False
cbolyr7cu.Visible = False
cbolyr8cu.Visible = False
cbolyr9cu.Visible = False
cbolyr10cu.Visible = False
cbolyr11cu.Visible = False
cbolyr12cu.Visible = False
cbolyr13cu.Visible = False
cbolyr14cu.Visible = False
cbolyr15cu.Visible = False
cbolyr16cu.Visible = False
cbolyr17cu.Visible = False
cbolyr18cu.Visible = False
cbolyr19cu.Visible = False
cbolyr20cu.Visible = False
cbolyr21cu.Visible = False
cbolyr22cu.Visible = False
cbolyr23cu.Visible = False
cbolyr24cu.Visible = False
cbolyr25cu.Visible = False
cbolyr26cu.Visible = False
cbolyr27cu.Visible = False
cbolyr28cu.Visible = False
cbolyr29cu.Visible = False
cbolyr30cu.Visible = False
cbolyr1cu.Enabled = False
cbolyr2cu.Enabled = False
cbolyr3cu.Enabled = False
cbolyr4cu.Enabled = False
cbolyr5cu.Enabled = False
cbolyr6cu.Enabled = False
cbolyr7cu.Enabled = False
cbolyr8cu.Enabled = False
cbolyr9cu.Enabled = False
cbolyr10cu.Enabled = False
cbolyr11cu.Enabled = False
cbolyr12cu.Enabled = False
cbolyr13cu.Enabled = False
cbolyr14cu.Enabled = False
cbolyr15cu.Enabled = False
cbolyr16cu.Enabled = False
cbolyr17cu.Enabled = False
cbolyr18cu.Enabled = False
cbolyr19cu.Enabled = False
cbolyr20cu.Enabled = False
cbolyr21cu.Enabled = False
cbolyr22cu.Enabled = False
cbolyr23cu.Enabled = False
cbolyr24cu.Enabled = False
cbolyr25cu.Enabled = False
cbolyr26cu.Enabled = False
cbolyr27cu.Enabled = False
cbolyr28cu.Enabled = False
cbolyr29cu.Enabled = False
cbolyr30cu.Enabled = False
cbolyr1type.Visible = False
cbolyr2type.Visible = False
cbolyr3type.Visible = False
cbolyr4type.Visible = False
cbolyr5type.Visible = False
cbolyr6type.Visible = False
cbolyr7type.Visible = False
cbolyr8type.Visible = False
cbolyr9type.Visible = False
cbolyr10type.Visible = False
cbolyr11type.Visible = False
cbolyr12type.Visible = False
cbolyr13type.Visible = False
cbolyr14type.Visible = False
cbolyr15type.Visible = False
cbolyr16type.Visible = False
cbolyr17type.Visible = False
cbolyr18type.Visible = False
cbolyr19type.Visible = False
cbolyr20type.Visible = False
cbolyr21type.Visible = False
cbolyr22type.Visible = False
cbolyr23type.Visible = False
cbolyr24type.Visible = False
cbolyr25type.Visible = False
cbolyr26type.Visible = False
cbolyr27type.Visible = False
cbolyr28type.Visible = False
cbolyr29type.Visible = False
cbolyr30type.Visible = False
cbolyr1type.Enabled = False
cbolyr2type.Enabled = False
cbolyr3type.Enabled = False
cbolyr4type.Enabled = False
cbolyr5type.Enabled = False
cbolyr6type.Enabled = False
cbolyr7type.Enabled = False
cbolyr8type.Enabled = False
cbolyr9type.Enabled = False
cbolyr10type.Enabled = False
cbolyr11type.Enabled = False
cbolyr12type.Enabled = False
cbolyr13type.Enabled = False
cbolyr14type.Enabled = False
cbolyr15type.Enabled = False
cbolyr16type.Enabled = False
cbolyr17type.Enabled = False
cbolyr18type.Enabled = False
cbolyr19type.Enabled = False
cbolyr20type.Enabled = False
cbolyr21type.Enabled = False
cbolyr22type.Enabled = False
cbolyr23type.Enabled = False
cbolyr24type.Enabled = False
cbolyr25type.Enabled = False
cbolyr26type.Enabled = False
cbolyr27type.Enabled = False
cbolyr28type.Enabled = False
cbolyr29type.Enabled = False
cbolyr30type.Enabled = False
CboMatl12.Visible = False
CboMatl12.Enabled = False
CboMatl23.Visible = False
CboMatl23.Enabled = False
CboMatl34.Visible = False
CboMatl34.Enabled = False
CboMatl45.Visible = False
CboMatl45.Enabled = False
CboMatl56.Visible = False
CboMatl56.Enabled = False
CboMatl67.Visible = False
CboMatl67.Enabled = False
CboMatl78.Visible = False
CboMatl78.Enabled = False
CboMatl89.Visible = False
CboMatl89.Enabled = False
CboMatl910.Visible = False
CboMatl910.Enabled = False
CboMatl1011.Visible = False
CboMatl1011.Enabled = False
CboMatl1112.Visible = False
CboMatl1112.Enabled = False
CboMatl1213.Visible = False
CboMatl1213.Enabled = False
CboMatl1314.Visible = False
CboMatl1314.Enabled = False
CboMatl1415.Visible = False
CboMatl1415.Enabled = False
CboMatl1516.Visible = False
CboMatl1516.Enabled = False
CboMatl1617.Visible = False
CboMatl1617.Enabled = False
CboMatl1718.Visible = False
CboMatl1718.Enabled = False
CboMatl1819.Visible = False
CboMatl1819.Enabled = False
CboMatl1920.Visible = False
CboMatl1920.Enabled = False
CboMatl2021.Visible = False
CboMatl2021.Enabled = False
CboMatl2122.Visible = False
CboMatl2122.Enabled = False
CboMatl2223.Visible = False
CboMatl2223.Enabled = False
CboMatl2324.Visible = False
CboMatl2324.Enabled = False
CboMatl2425.Visible = False
CboMatl2425.Enabled = False
CboMatl2526.Visible = False
CboMatl2526.Enabled = False
CboMatl2627.Visible = False
CboMatl2627.Enabled = False
CboMatl2728.Visible = False
CboMatl2728.Enabled = False
CboMatl2829.Visible = False
CboMatl2829.Enabled = False
CboMatl2930.Visible = False
CboMatl2930.Enabled = False
Return
End If
End Sub
 
B

BruceM via AccessMonster.com

Are all of those controls bound to fields? If so, your database design is
almost surely incorrect. It may help to know something of the database's
purpose and structure.

I have a few observations. Any code relying on the value of a field or
control needs to run *after* the field has been updated. You would need code
in the control's After Update event, and in the form's Current event so the
controls are appropriately visible or not depending on the value of cboLyrs
when you first arrive at a record.

Some controls are the same (for instance, not visible) no matter the value of
cboLyrs. There is no point to coding something that is not going to change.

GoSub is of limited usefulness in any case, and none at all here. Also, you
seem to be setting the Visible property of labels and so forth that are not
included in the long list in Select Case. For instance, lbl1line.Visible is
False in LyrReset, but is never set to True.

In general you could do something like this:

Me.Control1.Visible = (cboLyrs = 1)

or:

Dim blnVis as Boolean

blnVis = (cboLyrs = 1)

Me.Control1.Visible = blnVis
etc.

If you need to hide or unhide a large number of controls you may do well to
make a box the same color as the form background and covering a number of
controls, and hide or unhide that. However, you should study some relational
database design principles before proceeding, as you will be forever fighting
with the project in its current state. Here a list of links I borrowed from
John Vinson. IMO Crystal's tutorial is as good a place as any to start.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
M

Mr. B

Because you have a group of controls that need to be changed at the same
time, you can create a user defined funciton that will handle visible or not
visible and simply call the function providing the desired paramenter. For
example, to work with your "cbolyrtype3 through cbolyrtype30" controls, you
could use a function like this:

Function SetCboCondition(Status As Boolean)
Dim cntr
For cntr = 3 To 30
Me.Controls("cbolyrtype" & cntr).Enabled = Status
Next cntr
End Function

Then you would call the function from the after update event of any other
control that affects these controls, by placing this line of cde in the event.

'this line would set each of the controls "Enabled" property to false
SetCboCondition False

to set the same controls "Enabled" property to true you would use:
SetCboCondition true

If you needed to manage the Enabled and the Visible properties, your
function would be modified to something like this:

Function SetCboCondition(Status As Boolean)
Dim cntr
For cntr = 3 To 30
With Me.Controls("cbolyrtype" & cntr)
.Enabled = Status
.Visible = Status
End With
Next cntr
End Function

When you call this function both the enable property and the visible
property would be set to false or true based on the value you pass to the
function when you call it.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change a great number of words in word files, with a macro as short as possible. 5
Select Text to End of Footnote 9
inlineShape.OLEFormat.Activate() command fails for excel file 0
Word macro that can call cells out of Excel for a find and replace 0
VBA Export to PDF 0
Making a macro only work on certain files 2
VSTO C# add in Outlook.Appointment error 0
Loop through pages and insert header image 0

Top