How do I transfer ending inventory to new record as begining inventory?
Thank You.

Jur,

Welcome to UtterAccess.

Can you elaborate on just what you are trying to do?

Aqua

I posted File if that's helpful

When I want to make a new entry under the same ProductID I'd like to have ending inventory from last record appear as begining inventory in a new record.

Did you 'try' using decending order on the 'ProductID' in the query? In order to show the last entry on top.

Maybe I'm missing something..(IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Aqua

Yes I did.Didn't make any difference.Mayb I am not clear to explain this.Try one more time.When I go to new record and pull ProductID from cmb box to make a new entry I'd like to have begining inventory box filled automatically from last record of the same PartID from ending inventory.I hope this help.I don't want to look up this record and and do entry manualy.

Inventory is one of the hardest things to do correctly, even for experienced programmers. Your structure as it stands is not correct. It is not normalized. You need a transactions table, but, 'ending inventory' is a calculated value, not one that should be stored. Set up properly, you can use queries to calculate the quantity on hand at any given time. Access can't be effective and work like it's supposed to if you try to make it work like Excel. They're completely different animals.

Not trying to be harsh here, but, your project is just going to continue to give you problems until you fix the structure. My signature has a link to a 'primer' on database design and normalization.

That's what I thought.I just started working on this today for friend of mine.She does't keep track of individualy sold items.She counts ending inventory of of each ProductID at the end of the day and based on that determines how much of each product was sold and what was it's COGS.So I tried to structure this inventory transaction based on that.Also I am just a beginer in access trying to help her somehow.
Thanks Noah.

If that is the way it is done, then you will need a way to determine the "last" ending inventory value of a particular product. Once you know how to do that, then a DLookup can determine the correct value and display it on the form.

This seems to be awfully cumbersome. What does she use for giving receipts or processing orders? Wouldn't it be better to give her a database she can use to create invoices or recipts that will then automatically record each sale so she can calculate inventory based on stock moving in and out (the proper as Noah indicated). Seems to me that will make your friends business run much smoother. it would also save time in counting stock at the end of each business day.

Yes Scott that is the right way.I know little bit about accounting.But she doesn't have a point of sale or a system where each sale documents ProductID sold or quantity.No receipts eather.Just a old cash register where she can print total sale at the end of the each day and based on that and count of ending inventory she determines what was sold and for how much.Right now she is doing it in excel in bunch of different worksheets and workbooks.So I thought that access database would make it little easier for her plus give her easy of use and report capability.

Thaks Frank I'll try that and let you know.It is in early experimental stages right now.Just trying to figure out what works and not, given my limited knowledge of Access.I think I can give my friend something little better and easier to use than Excel.

Ummm, What do the customers think about not getting receipts when they purchase something? I can't imagine doing business like that. Doesn't she have an accountant? What do they say about this? What she NEEDS is a rudimentary POS system and Access can do that fairly easily. She can use the computer instead of the cash register.

I would definitely try to bring her up to stream. She is probably doing double or triple the amount of work she needs to.

No need for a DLookup here.

Any inventory system needs a 'stock take' table, to store the actual count of items and the date of that count. Either with a subform, or by basing the form on a query, you could display the last count with no need for the code required in a DLookup.