Procedure too Large

B

Blackwidow

Registered User.
Local time
Today, 18:48
Joined
Apr 30, 2003
Messages
149
Please can someone help me after keying in all the programming to get the buttons to become visible as I need them, it is now coming up with an error that the procedure is too large! Is there anyway at all of simplifying my code. basically on selection from the combo box three text boxes and a label becomes visible and all the rest are hidden. I know the code works i just have too many subjects!

Tearing my hair out here

'Show English and English Literature only
If ([Subject] = "EN") Then
lblEn.Visible = True
txtenc.Visible = True
txtent.Visible = True
txtene.Visible = True

LBLENL.Visible = True
txtENLC.Visible = True
TXTENLT.Visible = True
TXTENLE.Visible = True

LBLASD.Visible = False
TXTASDC.Visible = False
TXTASDT.Visible = False
TXTASDE.Visible = False

LBLAMP.Visible = False
TXTAMPC.Visible = False
TXTAMPT.Visible = False
TXTAMPE.Visible = False

lblamr.Visible = False
txtamrc.Visible = False
txtamrt.Visible = False
txtamre.Visible = False

lblAr.Visible = False
txtarc.Visible = False
txtart.Visible = False
txtArE.Visible = False

LBLBTHS.Visible = False
TXTBTHSC.Visible = False
TXTBTHST.Visible = False
TXTBTHSE.Visible = False

lblbs.Visible = False
txtbsc.Visible = False
txtbst.Visible = False
txtbse.Visible = False

lblcd.Visible = False
txtcdc.Visible = False
txtcdt.Visible = False
txtcde.Visible = False

lblDA.Visible = False
txtDAC.Visible = False
txtDAT.Visible = False
txtDAE.Visible = False

LBLAA.Visible = False
TXTAAC.Visible = False
TXTAAT.Visible = False
TXTAAE.Visible = False

lbldr.Visible = False
TXTDRC.Visible = False
txtdrt.Visible = False
txtdre.Visible = False

lblea.Visible = False
txteac.Visible = False
txteat.Visible = False
txteae.Visible = False

lblfd.Visible = False
txtfdc.Visible = False
txtfdt.Visible = False
txtfde.Visible = False

LblFr.Visible = False
TxtFrc.Visible = False
Txtfrt.Visible = False
txtfre.Visible = False

lblgg.Visible = False
txtggc.Visible = False
txtggt.Visible = False
txtgge.Visible = False

lblgh.Visible = False
txtghc.Visible = False
txtght.Visible = False
txtghe.Visible = False

lblGI.Visible = False
TxtGIC.Visible = False
TxtGIT.Visible = False
TxtGIE.Visible = False

LBLGR.Visible = False
TXTGRC.Visible = False
TXTGRT.Visible = False
TXTGRE.Visible = False

lblhi.Visible = False
txthic.Visible = False
txthit.Visible = False
txthie.Visible = False

LBLIT.Visible = False
TXTITC.Visible = False
TXTITT.Visible = False
TXTITE.Visible = False

lblLw.Visible = False
TxtLWC.Visible = False
TxtLWT.Visible = False
TxtLWE.Visible = False

lblma.Visible = False
txtmac.Visible = False
txtmat.Visible = False
txtmae.Visible = False

LBLMD.Visible = False
TXTMDC.Visible = False
TXTMDT.Visible = False
TXTMDE.Visible = False

lblmu.Visible = False
txtmuc.Visible = False
txtmut.Visible = False
txtmue.Visible = False

lblPE.Visible = False
TxtPEC.Visible = False
TxtPET.Visible = False
TxtPEE.Visible = False

lblsc.Visible = False
txtscc.Visible = False
txtsct.Visible = False
txtsce.Visible = False

LBLSP.Visible = False
TXTSPC.Visible = False
TXTSPT.Visible = False
TXTSPE.Visible = False

LBLTE.Visible = False
TXTTEC.Visible = False
TXTTET.Visible = False
TXTTEE.Visible = False

'Show ASD only
If ([Subject] = "ASD") Then
lblEn.Visible = False
txtenc.Visible = False
txtent.Visible = False
txtene.Visible = False

LBLENL.Visible = False
txtENLC.Visible = False
TXTENLT.Visible = False
TXTENLE.Visible = False

LBLASD.Visible = True
TXTASDC.Visible = True
TXTASDT.Visible = True
TXTASDE.Visible = True

LBLAMP.Visible = False
TXTAMPC.Visible = False
TXTAMPT.Visible = False
TXTAMPE.Visible = False

lblamr.Visible = False
txtamrc.Visible = False
txtamrt.Visible = False
txtamre.Visible = False

lblAr.Visible = False
txtarc.Visible = False
txtart.Visible = False
txtArE.Visible = False

LBLBTHS.Visible = False
TXTBTHSC.Visible = False
TXTBTHST.Visible = False
TXTBTHSE.Visible = False

lblbs.Visible = False
txtbsc.Visible = False
txtbst.Visible = False
txtbse.Visible = False

lblcd.Visible = False
txtcdc.Visible = False
txtcdt.Visible = False
txtcde.Visible = False

lblDA.Visible = False
txtDAC.Visible = False
txtDAT.Visible = False
txtDAE.Visible = False

LBLAA.Visible = False
TXTAAC.Visible = False
TXTAAT.Visible = False
TXTAAE.Visible = False

lbldr.Visible = False
TXTDRC.Visible = False
txtdrt.Visible = False
txtdre.Visible = False

lblea.Visible = False
txteac.Visible = False
txteat.Visible = False
txteae.Visible = False

lblfd.Visible = False
txtfdc.Visible = False
txtfdt.Visible = False
txtfde.Visible = False

LblFr.Visible = False
TxtFrc.Visible = False
Txtfrt.Visible = False
txtfre.Visible = False

lblgg.Visible = False
txtggc.Visible = False
txtggt.Visible = False
txtgge.Visible = False

lblgh.Visible = False
txtghc.Visible = False
txtght.Visible = False
txtghe.Visible = False

lblGI.Visible = False
TxtGIC.Visible = False
TxtGIT.Visible = False
TxtGIE.Visible = False

LBLGR.Visible = False
TXTGRC.Visible = False
TXTGRT.Visible = False
TXTGRE.Visible = False

lblhi.Visible = False
txthic.Visible = False
txthit.Visible = False
txthie.Visible = False

LBLIT.Visible = False
TXTITC.Visible = False
TXTITT.Visible = False
TXTITE.Visible = False

lblLw.Visible = False
TxtLWC.Visible = False
TxtLWT.Visible = False
TxtLWE.Visible = False

lblma.Visible = False
txtmac.Visible = False
txtmat.Visible = False
txtmae.Visible = False

LBLMD.Visible = False
TXTMDC.Visible = False
TXTMDT.Visible = False
TXTMDE.Visible = False

lblmu.Visible = False
txtmuc.Visible = False
txtmut.Visible = False
txtmue.Visible = False

lblPE.Visible = False
TxtPEC.Visible = False
TxtPET.Visible = False
TxtPEE.Visible = False

lblsc.Visible = False
txtscc.Visible = False
txtsct.Visible = False
txtsce.Visible = False

LBLSP.Visible = False
TXTSPC.Visible = False
TXTSPT.Visible = False
TXTSPE.Visible = False

LBLTE.Visible = False
TXTTEC.Visible = False
TXTTET.Visible = False
TXTTEE.Visible = False

End If

End If

End Sub

(Arrghhh its too big to paste as well! have narrowed it down to two subjects is there anyway this can be simplified?)
 
Never come across that before!
Given that it works ok with 1 sub set, I would create a table to store the fields and save the true/false values for each Field/language in there. This will give you the added advantage of being able to add a Language by adding another data set to the table.

Shout if you want more help to do this

HTH

Peter
 
There are loads of approaches to this, although it seems as though you're trying to have two completely different screens in the one place, when you might as well have two forms, or tab pages or something like that.

Anyway, are there only two cases? In each case, is the set of controls visible the exact opposite of the other case?

Are you aware that labels will be shown/hidden automatically when attached to a control which is shown/hidden?

If you have many cases, let's say five, I'd consider a system where you put a letter in each of the controls' tag property if it is meant to show up if that letter is the case. So lblX.tag = "BCE" is visible for cases B, C, and E, but not A and D. Then you use a for each loop on the controls and if the letter appears in the tag, then show it, otherwise hide it.

I can talk you through something like that if it is the situation.

Sam.
 
I have the subjects set up in a table called tblSubjects, with the subject and and Autonumber field... not sure I understand how i to save this information in the table though?
 
There are 33 subjects and each will only be visible when the subject is selected from the dropdown box, I cant have a tabform for it because there are too many Subjects and it is already on a tabform
 
sammy that seems like the simplest way of fixing the problem, but i've never used it before can you explain how I do it?

So I put a letter in each of the Tag properties, then how do I loop it?
 
(This is a response to your second post, if you want an idea about the tagging methid, ask, but these are probably better.)

OK, well I'm still not crazy about the way you're doing this, but if you must do it like this then you could do this:

lblEn.Visible = ([Subject] = "EN")
txtenc.Visible = ([Subject] = "EN")
txtent.Visible = ([Subject] = "EN")
txtene.Visible = ([Subject] = "EN")

LBLENL.Visible = ([Subject] = "EN")
txtENLC.Visible = ([Subject] = "EN")
TXTENLT.Visible = ([Subject] = "EN")
TXTENLE.Visible = ([Subject] = "EN")

LBLASD.Visible = ([Subject] = "ASD")
TXTASDC.Visible = ([Subject] = "ASD")
TXTASDT.Visible = ([Subject] = "ASD")
TXTASDE.Visible = ([Subject] = "ASD")

You only need to visit each set of controls once then.

I'd do it differently, but then I never work with bound forms so I couldn't be sure my advice would help. I'll guess at it though. (Actually the below only lets you have one set open per subject, so unless you make Literature a different subject to English this won't really help.)

Create another text box somewhere and call it txtSam. Leave it visible all the time. I am going to assume that txtASDC is bound to a field ASDC, and txtENC is bound to ENC and so on.

At the bottom of your code add this line:
me.txtSam.ControlSource = me.Subject & "C"
(Or wherever you're getting [Subject] from.)

txtSam should always have the same data in it as the first textbox you made visible (with the C in the name). Make two others similar to this and then you'll only need three on the whole form. You might have to experiment with refreshing the form after changing the ControlSource properties though.

You can do the same with the labels by changing one label's caption property.

Sam.
 
If you create a table called 'tblSubjectFields' with two fields, 'TxtField' and 'txtLang' and populate this with just the fileds you want to be visible.

In your form select all of the fields that you want to be able to Hide/Show and set their tag property to 'hide' (no quotes)

On the after update event of the cbo Subjects have this code. Depending on the version of Access you are using you may have to set a reference to DAO.

Code: 
Private Sub subject_AfterUpdate()
Dim rst As DAO.Recordset
Dim ctl As Control
Dim strSql As String

strSql = "SELECT tblSubjectFields.* FROM tblSubjectFields WHERE (((tblSubjectFields.txtLang)='" & Me.subject & "'))"
Set rst = CurrentDb.OpenRecordset(strSql)
' set all control marked with 'hide' in their tag to invisible
For Each ctl In Controls
    Debug.Print ctl.Name
    If ctl.Tag = "hide" Then ctl.Visible = False
Next ctl
'Show the fields listed in tblSubjectFields
Do While Not rst.EOF
    Me(rst!TxtField).Visible = True
    rst.MoveNext
Loop
Set rst = Nothing
End Sub

Hope that makes sense :)

Peter
 
I think it does just got home going to give it a whirl now! will report back happy or cry lol
 
'If you create a table called 'tblSubjectFields' with two fields, 'TxtField' and 'txtLang' and populate this with just the fileds you want to be visible.'

bit confused here.. created the table set the subject afterupdate to that and put the tags on all the field..

what I am unsure about it whether I should have anything in the txtfield and txtlang... am I supposed to put the subjects in under TxtLang and the fields I want visible for each subject in the txtField? there will be three fields for each subject...
 
Oh my god didnt expect that!

It works it really really works wow, I mean thank you your a complete gem and saved my neck from the chop!
 
Its not working :( I thought it was tested it with English, but that seems to be the only subject working :( Any ideas?
 
:)

Glad you have it going now.

You might want to search the forum on "normalisation" as any table that will have most of its fields empty in every records can probably be imporved on.

Peter
 
ooops spoke too soon!

in what way is it not working? is it hidding all the fields OK?

the 'txtLang' field should be the same codes as used in the combo box.

Peter
 
english works I dont understand its just all the others that dont show up they stay hidden will clear it out,zip it and send it.. i've checked all the field names match and everything i just cant work out whats wrong :( 8pm at night and I am still stuck on the same problem! i've only been working on it since 10am i thought it would be easy!
 

Attachments

Sorry I can't open the DB at home as I only have 2K here and it must be newer as it wont let me in. I will try in the AM at work
Meanwhile another approach on reading again your problem.

Use the tag and loop method to clear all fields, then just show the ones you want, using your original method but you only need to do the four fields you need to make show.

peter
 
Saved it in 2000 dont worry about looking at it tonight if you are busy it is past 9 at night.

Thanks for all your help and would be great if you could look at it for me tomorrow.
 

Attachments

:eek:

erm.... can i just say your code works fine and its my own stupidity probably through tiredness that caused the problem...

having said that wonder how many people are as baffled as I was when they download the copy.

Twice in two days I have made a catastrophic error caused purely by my own blinkered vision.

I think it I am well overdue some sleep instead of sitting at my computer for 17 hours a day!
 
I do know that I am not going about things in the way that I should however When your cover three of your bosses jobs at the same time as your own you find you run out of hours in the day. I am doing the best I can under the circumstances at the moment. But its on my list of things to do as soon as I can
 
You may need to rethink your approach and simplify it all ...
This looks realy heavy :O(
 
You must log in or register to reply here.

Similar threads

H
Procedure too large
Replies
7
Views
805
pbaldy
pbaldy
A
Procedure Too Large! Suggestions Please
Replies
7
Views
1,031
MarkK
MarkK
G
Procedure too large
Replies
1
Views
542
Beetle
B
G
Continuous form with unbound combo drilldown, conditionally hidden controls, and more
Replies
1
Views
4,158
grzzlo
G
W
Procedure too large--help!! VB Novice
Replies
5
Views
2,175
Rabbitoh
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom