VBA Module Size

[paulbasi]

I'm writing a SUB PROCEDURE that has many IF THEN ELSE staements. As a matter of fact, there are too many and I am getting errors. Is there a simple solution?

Here is a quick run down on what I am attempting:

When I open the form, it hides certain fields and displays others ...

The section below is where I am experiencing my problem.
First I decide what fields need to be visible and what they should be labeled.
These are the most common labels but depending on the vendor number, they can change once again.
Then, depending on the Vendor Number, a different calculation must be formed. This could mean a different discount, a different markup, different currency, additional charges ... and so on.
By now it should be obvious that this is definately going to go over the 64K limit that is allowed.
Can you give me some direction? Any help would be appreciated. If you cannot, please let me know and I will seek help elsewhere ...

Private Sub NON_Stock_Corp_Click()
PriceTypeLabel = "Name of Corporate Vendor"
Me!NON_Stock_Corp.Caption = "CALCULATE"
Me!CurrencyExchange.Caption = "Currency Exchange"
Me!StockItemCorp.Visible = False
Me!StockItemJOBBER.Visible = False
Me!NON_Stock_JOBBER.Visible = False
Me!NON_Stock_OEM.Visible = False
Me!NothingElse.Visible = False
Me!Split.Visible = False
Me!VendorCodes.Visible = True
Me!Cost1Label.Visible = True
Me!Cost1.Visible = True
Me!PriceType.Visible = True
Me!Vendor.Visible = True
Cost1Label = "Jobber"
Core1Label = "Core Charge"
If Vendor = "7234" Then
Cost2Label = "Current Exchange Rate"
PriceType = "Company A"
Cost1Label = "Cost in Canadian $"
Notes = "Airlift will give you our cost in Canadian Funds" + vbCrLf + "Verify if from Canadian warehouse or US"
Cost = Cost1
Consumer = Cost1 * 1.9
Dealer = (Consumer - Cost) * 0.57 + Cost
Core = Core1: End
End If
If Vendor = "8583A" Then
Me!Core1Label.Visible = True
Me!Core1.Visible = True
PriceType = "Company B"
Cost1Label = "EAP Cost"
Notes = "Use this number for Cooling Parts only. For all else. please use 8583B"
Consumer = (Cost1 * 2) + Core1
Dealer = (((Consumer - Core1) - Cost1) * 0.57) + (Core1 + Cost1)
Cost = Cost1 + Core1
Core = Core1: End
End If

.... and the list goes on.
Thank you in advance.

 

[PantoKing]

Having a quick look it seems that a select case statement may be better.

e.g.

Select Case vendor

Case "7234"

Cost2Label = "Current Exchange Rate"
PriceType = "Company A"
Cost1Label = "Cost in Canadian $"
Notes = "Airlift will give you our cost in Canadian Funds" + vbCrLf + "Verify if from Canadian warehouse or US"
Cost = Cost1
Consumer = Cost1 * 1.9
Dealer = (Consumer - Cost) * 0.57 + Cost
Core = Core1: End

Case "8583A"
Me!Core1Label.Visible = True
Me!Core1.Visible = True
PriceType = "Company B"
Cost1Label = "EAP Cost"
Notes = "Use this number for Cooling Parts only. For all else. please use 8583B"
Consumer = (Cost1 * 2) + Core1
Dealer = (((Consumer - Core1) - Cost1) * 0.57) + (Core1 + Cost1)
Cost = Cost1 + Core1
Core = Core1: End

etc...


End Select

HTH,

Steve.

Make things as simple as possible — but no simpler.

 

[paulbasi]

Thank you very much for the quick reply Steve.
Will this still not cause the same size error? I can tell that this will definately reduce the size by a few lines however, one would assume that limits would still be in place.

Thanks again

 

[JeremyNYC]

Paul,

In eight years of programming with Access I've never hit any limit for the size of a module. Don't worry about that.

On the other hand, it sounds like a lot of this could be taken care of by putting some of your calculations in a table, that is, by putting your formulae into a table, so that you can get to them in the same way each time, without having to code for each different vendor, each different currency, etc.

Also, if you're changing the visible (or some other) property for lots of controls on a form, it's often easier to put the same word in the Tag property of each control to be affected and loop through the form's control collection, acting on all the ones with that word in the tag.

Jeremy

PS: I'm not sure where the 64K number comes from, but assuming it's correct, you would need to have 47.5 times as much code as you posted to reach that limit.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[RickSpr]

To elaborate on Jeremy's proposal:

Create a FormParams table with Vendor as its key. Add a text column for each thing that may need to be changed--each label caption, formula, Visible property, etc. For each vendor, create a row in the table. For each column in that row, provide the appropriate caption, formula, etc. for that vendor.

In your form's Current event procedure (or wherever it is that the Vendor field may change), open a recordset on the FormParams table and read the vendor's record. For captions and text box values, you can just copy the text to the text box's property. For a formula or Visible property, use the Eval() function like this:
[controlname] = Eval(rst![FormParams column name])

The Eval() function takes a VBA expression within a string, and in effect, compiles and executes it to obtain the function return value.

Here's a sample of what your code would look like:

    With rst
        Core1Label.Visible = Eval(!Core1LabelVisible)
        Core1.Visible = Eval(Core1Visible)
        PriceType = !PriceType
        Cost1Label = !Cost1Label
        Notes = !Notes
        Consumer = Eval(!Consumer)
        Dealer = Eval(!Dealer)
        Cost = Eval(!Cost)
    End With

Here are the FormParams column values for vendor 8583A:
Vendor: "8583A"
Core1LabelVisible: "True"
Core1Visible: "True"
PriceType: "Company B"
Cost1Label: "EAP Cost"
Notes: "Use this number for Cooling Parts only. For all else. please use 8583B"
Consumer: "(Cost1 * 2) + Core1"
Dealer: "(((Consumer - Core1) - Cost1) * 0.57) + (Core1 + Cost1)"
Cost: "Cost1 + Core1"

I realize that different vendors have different things that have to change, so maybe you'll leave some columns of FormParams blank and use an IsNull() test to decide whether to set them. Or, you could simply enter the default values into the table.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein