Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    36

    Unanswered: automation object

    hi,

    i made a query in access to make the sum of a column of a subform. The query works fine. Now i'm trying to execute the query when i press a button and display the value (sum of the column) in a textbox on the main form.

    macro -->
    Action: SetValue, item:[Forms]![frm_costs1]![totalall], expression: [qry_sub_total]![SumOfp5]

    when i press the button i get this error message:
    The object doesn't contain the automation object 'qry_sub_total'. You tried to run a visual basic procedure to set a property or method of an object. however, the component doesnt make the property available for automation operations and blablabla

    all help welcome, thx!
    ... man man man ...

  2. #2
    Join Date
    Dec 2003
    Posts
    36

    Lightbulb

    plz anyone
    ... man man man ...

  3. #3
    Join Date
    Nov 2003
    Posts
    40
    Okay, I am not exactly sure what is wrong with your code, but I can kind of give you away around it. It probably isn't the absolute best way to do it, but it should work.

    You obviously have a text box that has no control source and you are trying to give it one. What if you added a text box to your form that looks exactly like the first one except you make the control source the query field and you set the visibility property to be false when the form is loaded.
    Then when you hit the button that is suppose to display the subtotal in that text box, instead it will just hide the empty text box (set the visibility property to false) and unhide the text box with the value in it.

    It isn't exactly what you are looking for probably, but it might solve your problem.
    You could also read the value out of the query and then set the value of the text box to that value instead of actually setting the control for the text box.

    I hope I have helped you a little!

  4. #4
    Join Date
    Dec 2003
    Posts
    36
    thx for your reaction, but it won't work, I even added a field in my table and tested it on that one, still have the same error
    ... man man man ...

  5. #5
    Join Date
    Nov 2003
    Posts
    40
    Okay then there is something else is wrong.
    Okay, lets back track. You have a subform and form and a query. The query is storing the sum of the values in the subform. The subform is linked to a table. On the main form you want to display the sum of the values in the column on the subform.
    OKAY. That is what I gathered.

    NOW, You don't really need the query I don't think, as long as the subform is linked to table information.

    In the text box use:

    = Nz(DSum("FIELDNAME", "TABLENAME"), 0)

    The fieldname should be the name of the field that you are summin gup and the tablename should be the original table that is linked to the subform.

    If that doesn't help you, or I am not getting all the information from ya, then let me know, but it should be a good start to the right solution.

  6. #6
    Join Date
    Dec 2003
    Posts
    36
    hi,

    thx for your help already adrienne.
    The solution you gave me works ( but not completely)

    What Works:
    The Total appears in the textbox

    What's wrong:
    Its the total of the whole column in that table. But i only want the total where [tbl_costsub]![link]=[key] --> link on subform = key on main form.

    Is this possible?

    many thx
    ... man man man ...

  7. #7
    Join Date
    Dec 2003
    Posts
    36
    I sorted it out. Used a query instead of table in the DSum function.
    Thx for your help! much appreciated!
    ... man man man ...

  8. #8
    Join Date
    Dec 2003
    Posts
    36

    Angry

    One last problem ...
    When im on the form (subform) and i add an extra record in the subform the total (textbox on the mainform) should be automatically adapted.
    Example: 2 values in subform 5 + 2 --> total on mainform = 7
    now when i want to add a line in subform --> 5 + 2 + 3 ( i added by example 3) --> total on mainform should become 10.
    To do this i made a macro -> Requery Control = totalall ( = total field on main form). When i use this macro by example by clicking on a button in the main form it works fine. But when i use this macro in the subform by example on lost focus of a field is says: "THERE IS NO FIELD NAMED TOTALALL IN THE CURRENT RECORD"

    Pleaze help me out someone then my work is done too, thank you!
    ... man man man ...

  9. #9
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by daff
    One last problem ...
    When im on the form (subform) and i add an extra record in the subform the total (textbox on the mainform) should be automatically adapted.
    Example: 2 values in subform 5 + 2 --> total on mainform = 7
    now when i want to add a line in subform --> 5 + 2 + 3 ( i added by example 3) --> total on mainform should become 10.
    To do this i made a macro -> Requery Control = totalall ( = total field on main form). When i use this macro by example by clicking on a button in the main form it works fine. But when i use this macro in the subform by example on lost focus of a field is says: "THERE IS NO FIELD NAMED TOTALALL IN THE CURRENT RECORD"

    Pleaze help me out someone then my work is done too, thank you!
    Let me see here...

    I'd do something different... Seems easy enough...

    On the form design for the subform, I'd add an textBox that totals the field you want to total...

    For example... Say I want to total the Order Quantity field... (on the subform) ....Let's call it "txtOrderTotal"

    Assuming the text box for individual Order Quantity is named "txtOrderQty"... I'd insert "=Sum(txtOrderQty) into "txtOrderTotal" control source in the form footer... (Making the text box invisible and the height to a tiny amount so that the Form Footer isn't going to take up a big amount of space when it shows on the main form... )

    Then... on the Main Form... I put "=[sfrmOrders].Form!txtOrderTotal" into the text box control source that I want to display the total...

    I just tested it and it updates automatically after the record is added on my test form...

    HTH

  10. #10
    Join Date
    Dec 2003
    Posts
    36
    thx ,

    easy solution and works great!
    ... man man man ...

Posting Permissions

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