Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Question Unanswered: Form/Subform and Query question

    First:
    If I have form x which contains subform y and the subform contains texbox z, how do I point to that value from a module script. What I want to do is something like:
    something in form(x).value = subform(y).textbox(z).value

    Second:
    I have a query which which sums the amount owed and amount paid of a table and groups by debtorID. What I want is the difference between the amount owed and the amount paid. Is it possible to get that from a single query?
    ex.
    Amount Owed Amount Paid
    debtor1 $100 $5
    debtor2 $1000 $1000
    debtor1 $50 $0
    debtor2 $10 $5

    so the result of my query is:

    Amount Owed Amount Paid
    debtor1 $150 $5
    debtor2 $1010 $1005

    Data I want is Amount Owed - Amount Paid, like this:

    Amount Owed
    debtor1 $145
    debtor2 $5

    Thanks in advance for your help!

  2. #2
    Join Date
    Dec 2003
    Location
    Belgium
    Posts
    42
    you give the name of the form and textbox as an arugment of the procedure in the module.

    ex: Module.procedure(formName, TextboxName, ValueOfTextboxname)

    Maybe u could use code to get the active form in the module:
    Dim frmCurrentForm As Form
    Set frmCurrentForm = Screen.ActiveForm

    frmCurrentForm.Textbox = ...

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Form/Subform and Query question

    Originally posted by Boozer
    First:
    If I have form x which contains subform y and the subform contains texbox z, how do I point to that value from a module script. What I want to do is something like:
    something in form(x).value = subform(y).textbox(z).value

    Second:
    I have a query which which sums the amount owed and amount paid of a table and groups by debtorID. What I want is the difference between the amount owed and the amount paid. Is it possible to get that from a single query?
    ex.
    Amount Owed Amount Paid
    debtor1 $100 $5
    debtor2 $1000 $1000
    debtor1 $50 $0
    debtor2 $10 $5

    so the result of my query is:

    Amount Owed Amount Paid
    debtor1 $150 $5
    debtor2 $1010 $1005

    Data I want is Amount Owed - Amount Paid, like this:

    Amount Owed
    debtor1 $145
    debtor2 $5

    Thanks in advance for your help!

    "from a module script"

    Are you talking about VB code in a module?

    When you want to refer to something on a subform you refer to the subform as a control on the main form and then use the form property of this control to get to the fields on the subform. Forms!frmMain.NameOfTheSubformOnTheMainForm.Form.S ubformField.

    2nd part

    As long as the results that you want are part of the aggregate data or you are grouping by it you should be able to get what you want. If I understand you correctly, you want the difference of two sums and it is grouped by another field. Yes you should be able to get this in one query.

    Hope this helps

    Gregg

Posting Permissions

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