Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19

    Angry Unanswered: My Variable frustration levels

    Hello,

    I'm writing a contract-generation form in Access 2003 for a roofing
    client, and I'm having difficulties working with variables.

    I have 70-something combo boxes that I need to work with, that will set
    different options. Each of these particular options will have a price
    associated with them, all set and pulled from a master index.
    As the options are selected from the various drop-down boxes, I need to
    temporarily store the dollar value associated with that particular option
    in a variable that I can call from all the various sub-routines in that
    form's code.

    I don't want to create a field to store this data, just a temporary
    variable which will be cleared upon form or record load.

    I've tried using DIM layercostlabor, PUBLIC layercostlabor, and have had
    no results whatsoever, like there was no variable. I tried to put "Public
    Layercostlabor" and "DIM layercostlabor" at the very top, above Options
    Compare Database, but when I do, and I load the form, I get the following error:

    "The expression On Load you entered as the event property setting
    produced the following error: Member already exists in an object module
    from which this object module derives."

    I have created temporary text boxes to watch my calculations, and I
    cannot get any of them to change. Ideally, this whole process would be
    something as simple as:

    "DIM layercostlabor" (and the rest of them) at form load.

    and then upon selection of an option:

    "layercostlabor = Combo798.Column(6)" to store the $$ value in a variable
    "textbox42.Value = layercostlabor" to show me verification that the
    proper $$ value was in that variable, and then it would run the other
    calculations on any other variables with values, and spit out a total on
    the fly as each option is changed.

    I'm pulling my hair out and I just know there's gotta be a way!

    Thank you for your time,
    Brian

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Dim varname as DataType

    That should do, when placed in the General Declarations section of your form.

    The variable varname would be read/write across all event procedures in that form.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19
    I just gave that a try, and it gives me the same error message I listed above.

    "The expression On Load you entered as the event property setting
    produced the following error: Member already exists in an object module
    from which this object module derives."

    ???

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the vaariable declaration goes in the forms header block
    I suspect you are trying to stuff values into the variable before the form is loaded.. you need tobe lear in your mind the sequence that events fire off in a form load.. I forget which but there is another event that is more appropriate than on load.
    it may be better in the foms on current event.. so that you know you are putting the correct value when the record changes

    mind you I have my doubts about using a form scope varaible for this task. I would have expected a function to calculate the job cost baased on the price * qty for the 68 (68!) combos. without going into the details that design sounds flakey to me.
    I woudl expect that for this sort of costing project you would have
    some fixed cost (eg delivery and collection of plant & equipment, contingency)
    some variable cost (ie qty * price) eg 100m2 roof @ £35.50
    some contingency could be proportional ( eg x% of the cost so far)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You have the variable declared twice?

    In the code window, put in MSGBOX VariableName.

    Then right click on your variable name and select Definition. This will help you track down where you have declared it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19
    I definitely do not have it declared twice.

    I went back and removed the single DIM layercostlabor declaration under General Declarations, and the one error I've been experiencing goes away. When I place Dim layercostlabor in the subroutine for copying all of the variables into the temporary text boxes (these will go away before the client gets the finished product - they are only for my reference to ensure things are calculating properly), it knows that there is a variable called layercostlabor, but if I put it outside of that routine, it cannot find it, and I get a 'Microsoft Office Access cannot find the field 'layercostlabor' referred to in your expression.' error.

    Here is what I'm trying to work with:

    Whenever any option that effects price gets changed, I have each combo box set up for an On Update event, which I want to then take the price from the query and stick it in a variable, and then call the sub VariableRefresh.

    The first thing in VariableRefresh that I want to happen is to call the sub VariableLabels, which puts all the values of my variables into text boxes. Once I can accomplish that, I can start making calculations with them, but I need to verify they work, first. The only time I don't get an error saying it can't find the field 'layercostlabor' (or any of my other variables) is when I declare the variable in the VariableLabels sub. The problem there, is, declaring the variable there wipes out the info, so I get a seemingly blank variable. So I'd like to declare all variables in their corresponding OnUpdate sub of each combo box, or all at once upon form load, but then I can't access them in my later subroutines.

    t1.value thru t62.value are my temporary text boxes.

    Private Sub VariableRefresh()

    Call VariableLabels
    Call PitchedCalculations

    End Sub

    Private Sub VariableLabels()

    T1.Value = layercostlabor
    t2.Value = pitchcostlabor
    t3.Value = storiescostlabor
    t4.Value = accesscostlabor
    t5.Value = ridgebundles
    t6.Value = ridgeprice
    t7.Value = underlaymentprice
    t8.Value = starterbundles
    t9.Value = starterprice
    t10.Value = starter2bundles
    t11.Value = starter2price
    t12.Value = icewaterprice
    t13.Value = otherrollprice
    t14.Value = drippieces
    t15.Value = dripprice
    t16.Value = rakepieces
    t17.Value = rakeprice
    t18.Value = valleypieces
    t19.Value = valleyprice
    t20.Value = stepbundles
    t21.Value = stepprice
    t22.Value = chimneypieces
    t23.Value = chimneyprice
    t24.Value = otherflashingpieces
    t25.Value = otherflashingprice
    t26.Value = paintprice
    t27.Value = plywoodprice
    t28.Value = plywoodprice2
    t29.Value = anchorprice
    t30.Value = ventprice
    t31.Value = ridgeventbundles
    t32.Value = ridgeventprice
    t33.Value = otherventprice
    t34.Value = oneinpipejackprice
    t35.Value = twoinpipejackprice
    t36.Value = threeinpipejackprice
    t37.Value = fourinpipejackprice
    t38.Value = skylight1price
    t39.Value = skylight2price
    t40.Value = skylight3price
    t41.Value = stylebundles
    t42.Value = styleprice
    t43.Value = stylebundles2
    t44.Value = styleprice2
    t45.Value = stylebundles3
    t46.Value = styleprice3
    t47.Value = stylebundles4
    t48.Value = styleprice4
    t49.Value = stylebundles5
    t50.Value = styleprice5
    t51.Value = allmaterialsprice
    t52.Value = alllaborprice
    t53.Value = contractAprice
    t54.Value = contractBprice
    t55.Value = contractCprice
    t56.Value = contractDprice
    t57.Value = contractEprice
    t58.Value = WarrantyAprice
    t59.Value = WarrantyBprice
    t60.Value = WarrantyCprice
    t61.Value = WarrantyDprice
    t62.Value = WarrantyEprice

    End Sub

    Thank you for your time,
    Brian

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Then you must have a sub-procedure with the same name as an object in your form.

    Let me find the link for you...

    Here: http://support.microsoft.com/kb/279124
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry that look like dodgy design to me

    what happens if some bright spark decides to add say ContractF
    or stylebundle6.. this approach needs a rewrite if that event ever occurs
    Im not surprised you are having problems

    I done this sort of thing before creating a entity similar to an invoice with each item individually identified


    tblQuotation
    'contains details pertinent to the quote (eg custoemr ID, job id, date, verificsation/validation data

    tblQuotationItems
    quoteID }
    itemid } PK
    Qty
    Price

    tblquoteitems
    itemid
    islumpsum 'identifies if this a single lump sum per use of item OR a unit price
    price
    sortseq 'determines what order these are presented to the user

    the the calculation can be done in SQL rather than VBA or form manipualtion.. this hair tearing you are going through right now goes away.

    your customer is happy becuase they have the ultimately extendable system they need
    you are happy because they are not going to come back and chase you becuase this doesn't work, or because they have just decided to switch to a new quotation system they need another 128 item.....
    the end user is happy becuase they get a piece of paper which only has the elements of the quote that are relevant.. theres no inclusion of irrelevant / empty sections
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •