Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    unbound text box Source (2002 )

    I have a data entry form based off of an invoice table, ie the record source is the invoice table. The invoice table contains the invoice number, contract number, PO number and the invoice amount as the primary working fields. There is a separate contract table that has the contract and contract amount and a separate contract-po number table that has the PO amount. The structure is the contract is associated with a vendor, I can have multiple POs for a contract and I can have multiple invoices for a PO, so three tables.

    I am trying to display the contract balance and PO balance on unbound text fields and can't seem to do it without turning the entire form into a display records only form. I've done several searches and can't seem to find anything that resolves my problem.

    My process on this form is: I am entering invoice data against a PO and I want to know when the PO balance is approaching zero without having to open another form. I want to do the same with the contract. So with each data entry, I want to display an updated set of balances in an unbound text box.

    Any suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: unbound text box Source (2002 )

    If an invoice belongs to a PO and a PO belongs to a contract, I don't understand why you have a contract number in the invoice table. It would seem more logical to link the invoice table to the PO table on PO number, and the PO table to the contract table on contract number.

    You can probably use calculated text boxes with an expression using DSum, but since I'm confused by the table structure, I can't give specific advice at the moment.

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: unbound text box Source (2002 )

    Huge screenshot reduced in size by HansV - please don't post images larger than 640 x 480.

    Here is a *.jpg of the relationships. I originally carried the key of the parent table down to the invoice table, since the child table has to inherit the parent's key for referential integrity. Susequently, I had a vendor who changed their system and used some invoice numbers twice, so I had to add an autonumber field. Now I do not require the contract number in the invoice table, but would still require the PO as a foreign key. I just haven't updated this yet.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: unbound text box Source (2002 )

    See if text boxes with the following control sources do what you want:

    =DSum("InvoiceAmount","Invoice","ContractNumber=" & [ContractNumber] & " And PONumber = " & [PONumber])

    =DLookup("PurchaseOrderAmount","ContactPurchaseOrd er","ContractNumber=" & [ContractNumber] & " And PurchaseOrderNumber = " & [PONumber])

    The first should add all invoice amounts for the current purchase order, and the second should return the purchase order amount.

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: unbound text box Source (2002 )

    Great idea, I had never needed this function or used it before. I couldn't get it to work in the control source of the text box where I want to display the information. However it works fine on the change event for the PO combobox. Actually, I am going to turn it into its own function with a series of other calculations to call. The way I've structured the form is that one selects a vendor, which then populates a combo box with the open POs and when a PO is selected the associated contract is displayed. One caveate for others-don't forget to save the record to update the balance, especially if you are passing the PO info on to the next record.

    After looking further into the function, what I really like about this is I can query from tables and queries not involved in the source for the form.

    Here is the final vb code to get the PO balance remaining:

    'setup where clause from contractpurchaseorder table
    POAmount = "PurchaseOrderNumber = " & "'" & Me![Combo52] & "'"

    'setup where clause from form source (Invoice table)
    temp = "ContractNumber= " & "'" & Me![Text56] & "'" & " and PONumber = " & "'" & Me![Combo52] & "'"

    'subtract total invoices from PO amount
    POBal = DSum("PurchaseOrderAmount", "ContractPurchaseOrder", POAmount) - DSum("InvoiceAmount", "Invoice", temp)

    Me![Text69] = POBal

Posting Permissions

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