Results 1 to 2 of 2

Thread: Calculations

  1. #1
    Join Date
    Jun 2002

    Unanswered: Calculations

    I have a tables called InventoryReceived, InventoryAllocated, and InventoryTransactions. I am having trouble creating a form that will query both these tables and report back totals into the correct fields in the form.
    In the InventoryReceived table there is a field for TransactionID, TransactionDate, QtyReceived, OnHandLocation, PartName, EnteredBy, and TransactionDescription.
    In the InventoryAllocated there is a field for TransactionID, TransactionDate, QtyAllocated, LocationAllocatedFrom, PartName EnteredBy, and TransactionDescription.
    In the InventoryTransactions table, there is a field for TransactionID, PartName, OnHandLocation, UnitsInStock, UnitsAllocated, AvailableUnits, UnitsOnOrder, TransactionDate, and EnteredBy.

    I have the following Forms:
    Allocated Inventory with fields to enter the information for equipment allocated out (EnteredBy, TransactionDate, PartName, QtyAllocated, LocationAllocatedFrom, TransactionDescription) This form places information into the InventoryAllocated table.
    Inventory Received with fields to enter the information for equipment received by location (EnteredBy, TransactionDate, PartName, QtyReceived, OnHandLocation, TransactionDescription).
    InventoryTotals with fields OnHandLocation, PartName, UnitsInStock, UnitsAllocated, Available Units, UnitsOnOrder).

    What I am trying to do is to have the InventoryTotals form populate itself with the Units in stock total (sum received of the part name), units allocated total (sum allocated of the part name), the available units total (units in stock minus units allocated), and the units on order (sum on order of the part name). The control would need to be the On Hand Location.

    Example: The InventoryTotals form is opened. The user enters the On Hand Location they would like to see the inventory totals for into the On Hand Location box and presses enter. The form automatically brings back the totals for each part name for that location.

  2. #2
    Join Date
    May 2003
    Provided Answers: 5
    Ok - try something like this

    Behind a command button place the following code (remember to use the names of your objects where applicable

    dim x
    x = dsum("[myfield]","tabledate","[mycriteria] = forms!formName!frmFieldname")

    mytextbox = x

    use this pseudo code for each total giving a different variable for each sum

    such as dim x,y,z - will tell access to create a variable for the three domain functions that you are about to run

Posting Permissions

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