Get sum of all records

[Demetious]

On my form, I have a inactive window that I want to display the sum of a particular part number qty. My records are as follows.

Mnf Part................Transaction Qty
Part 1...................transaction amount
Part 2.................. transaction amount
Part 1.................. transaction amount

My form name is Component Details. I only want to show the sum of the current record viewed. Thanks for any help.

 

[vbaInet]

I don't follow. What do you mean by "the sum of the current record viewed"? Give me a scenario or show some data (like you've done) but with some values and your desired outcome.

 

[Demetious]

I don't follow. What do you mean by "the sum of the current record viewed"? Give me a scenario or show some data (like you've done) but with some values and your desired outcome.

I have three tables. Inventory, Transactions, Transaction Types.
My goal is to have a form that views details on the inventory as well as allowing the form to add/delete inventory as needed. I don't know what the hell I'm doing so I am asking a bit at a time to understand so please bear with me.

On my form I have a inactive window that shows what the stock level is. My first objective is to get this window to display the sum of my transactions for the current record I am viewing. If I got back to part 1 on the inventory, it will display the sum of field "In Stock" for part 1.

Once I get that figured out, I will then move on to add/delete from the stock amount. I hope this makes sense. Thanks

 

[vbaInet]

You need a subform to display details of the inventory and it will allow you add multiple records plus delete them too.

To get the sum of the Transactions field, place a textbox onto the footer section and type =Sum([Transaction])

 

[Demetious]

My form is already created. It already displays all of the inventory properties with the exception of the stock levels. Here is my form.

http://c.imagehost.org/0844/accessform.jpg

The "In Stock" box needs to contain the value from another table (Transactions). Each record in the transactions table contains three fields.

Mfr Part #
Old Qty
Transaction Qty

How can I add Old Qty to Transaction Qty only for the part number I am viewing on my form and display it on the form? Whew....I think thats right. Thanks for your understanding.

 

[vbaInet]

Look at the DLookup() function. Here are some links:

http://www.techonthenet.com/access/functions/domain/dlookup.php
http://www.mvps.org/access/general/gen0018.htm
http://allenbrowne.com/casu-07.html

 

[Demetious]

=DLookUp("In Stock","Inventory",[Manufacturer Part Number=CurrentRecord])

I think I'm getting closer now but now I get #NAME? error.
I tried it without the Criterea as well and that give me #Error

 

[vbaInet]

=DLookUp("[In Stock]","Inventory","[Manufacturer Part Number]=" & [FieldName])

 

[Demetious]

=DLookUp("[In Stock]","Inventory","[Manufacturer Part Number]=" & [FieldName])

I don't think I'm understanding how the criteria works.

DLookUp("[In Stock]","Inventory")
This should look up all values in the [In Stock] field which is contained in the "Inventory" table correct? If so, that is correct.

Now I want my criteria to list only the [In Stock] value of my current record. If I were to enter the FieldName as you stated above, my expression would look like this.

]=DLookUp("[In Stock]","Inventory","[Manufacturer Part Number]=" & [Manufacturer Part Number])

Which would cause a loop I think. Thank you for your patience.
On another note, is there a place to find some good video tutorials on inventory databases? I have looked in the samples and such.

 

[vbaInet]

It wouldn't cause a circular reference. The one on the left is a string, the other is pointing to a field name in your form's recordsource. Try it out.

Erm, I don't know of any good tutorials on building inventory management databases I'm afraid. But for general Access/VBA tutorials lookup functionx

 

[novtalath]

Hi,

I can't see it solved so I will give you an example here:

DLookUp("[In Stock]","Inventory","[Manufacturer Part Number]=" & "Part001")

This will lookup in database all records with 'Manufacturer Part Number' equal to 'Part001'. So best would be to either have a combobox with all possible part numbers available, or if there are too many just a textbox to type such value there. Then code then would look:

DLookUp("[In Stock]","Inventory","[Manufacturer Part Number]=" & Textbox1.value)

or

DLookUp("[In Stock]","Inventory","[Manufacturer Part Number]=" & ComboBox1.value)

That should work.