Calculation within tabular form.

[mad-q]

Hello,

I have been able to set up a running stock level but I am trying to get it to
show it in a datasheet view. So that as I type in a part number for a client it will give me the running total depending on the part number in that row. At present it will give me a big error because it doesn't know which part number to use. I want it shown in datasheet view since it is simple to see all the parts ordered.

I have the following basic formula to work out.

Running Total = Stock - Parts Sold - Parts ordered but not despatched.

Many Thanks for any ideas.

 

[Access_guy49]

I may get corrected on this, but I think you could achieve the results in 1 or 2 ways.
A query, or using VBA Code and creating a record set.
The query method is probably esier, but what i'm thinking is this:
Create a select query that has desired display fields. for your running Total field, you can create your own field in the query builder.
At the top of the field (in query builder) where you would usually see the name of the field, you can right click and use the "build" option to create an expression.
Your expression would be something like
Running Total: = [Name of stock field] - [Name of Parts sold field] - [Name of not dispatched field]

And you should get the results you wish.

To further customize, if you want to only display the results for a user defined part number, you can set that as a criteria under the stock field. using the build function you can quickly navigate to Forms, Name of the form your users will be on, and then the name of the control which will house the value to filter by.
Adding this to your query will specify that your query must check that form's control for a parameter before it executes.
On your form you can creat a button that the user pushes to impliment that query and display the results on the form.

 

[vbaInet]

What was done in this link might help. But you would need a DSum() function.

 

[mad-q]

vbaInet,

By the looks of it (haven't read through it fully yet) that is exactly what I am trying to do. YOU STAR.

Thanks Access Guy for your reply, I have got as far as you gave but got stumped.

Cheers

 

[vbaInet]

If you get stumped just post back. There are lots of examples like this on this forum so remember to use the search feature too

 

[mad-q]

vbaInet thank you for that, I have downloaded the phone.db and I am going to go through it. The main problem I find with searching for these is what to look for and where.

Rather than just returning a result from the table / query can I use this to perform the calculation.

At present I have 2 queries, one to sum the total of stock sold and another one for sold waiting to be charged for and sent out thus a running stock.

I think the phone.db will help loads since I was having trouble linking a query with a line from a table.

Yours

 

[vbaInet]

You perform the calculation in the ControlSource property if the form is a subform. Just reference the right field values and perform the calculation.

 

[mad-q]

vbaInet,
Thank you for your help, I have managed to implement what you have said. Great. But at present it only works once the subform has been expanded on each part. How can I get it to auto lookup. I have thought about trying to get the sub forms to auto expand but to no avail.

Thanks

 

[vbaInet]

Let's see your db. Here's how:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

Keep some data for testing and also tell which form to open.

 

[mad-q]

Firstly, sorry for my tardy reply. Rather busy with the run up to Easter.

Please find attached a copy of Hal.zip

If you open the "Document" form then this is a form which will be placed within the customer form. At present I have the calculations working when the "Running Stock 2 Query" subform is expanded. I would love it so that this calculation was made without needing to open it as a sub form.

Many Many thanks for any advice / kicking in the right direction you are able to give.

Yours

 

[vbaInet]

I'm not understanding your meaning of the word "expanded" in this context?

I think what you're looking for is a DLookup and the Nz function. Look at the help files or do a quick google search

 

[mad-q]

Under the part section there is + where the sub query "Running Stock 2 Query" is run, when the + is clicked then the calc is performed. Before this it doesn't know the full info.

Will look up Dlookup..

Cheers

 

[vbaInet]

I can see you nested a form and you're looking to nest it again. As long as the subform (the one at the bottom) is based on a query, use the DLookup and use that query as the domain. You will understand what I mean by the time you read-up what the function does.

 

[mad-q]

Thank you for your help, I have now got to the stage where it is pulling up the information but there is a problem with the criteria option. It isn't select the data based on the part number. At present it is just returning the first line in the query for all results. My part numbers are made up of text so I know there has to be ' around it but still no joy.

=DLookUp("[Sum of Quantity]","[Running Stock 2 Query]"," [Part Number]=" & '[part number]')

Many Thanks for helping me.
Iain

 

[mad-q]

=DLookUp("[Sum of Quantity]","[Running Stock 2 Query]"," [Part Number]='"+[Part Number]+"'")