Inventory Entry Update help

[Richard]

I'm trying to design a inventory DB, however, when it comes to data entry I want what was my current stock changed to my current count example below.

Item:___Current Stock:___My Count:
Apples__1900___________879
Oranges_725____________600

what I want to do is create a form "Continuous Forms" listing all inventory and enter in my daily counts in that form then when I am done entering all the counts I would like to update the inventory and what I would like to see is My Count become my Current Stock and then My Count cleared for next day of inventory.

Currently I am testing it out and I can update what is on the form, however, My Count returns the same value in each record.

for example (This is what happens)
Item:___Current Stock:___My Count:
Apples__879____________879
Oranges_879____________600

as to what I would like it to do is this:
Item:___Current Stock:___My Count:


Apples__879_____________0
Oranges_600_____________0


Any help would be much appreciated.

[Richard]

nvm got it thanks

[cjwagner]

Well how'd you do it?

[daniels31790]

I'd like to know how that is done as well. I have the same question.

[MAF4Fam6]

The following is one way to accomplish this:

1. Ensure your Form's Record Source is a Query.

2. Insert a Command Button in the Form Header section.

3. Use VBA or Macro to perform the following Actions from the Command Button's OnClick Event:

a) Save the Current Record.

b) Run the following SQL statement:

UPDATE FormQueryName SET FormQueryName.CurrentStock = [MyCount], FormQueryName.MyCount = 0;

- Change "FormQueryName" in the SQL statement (3 instances) to read: Name of your Form's Query

c) Use the Requery command to refresh your Form.

A quick sample of an Access 2007 database (inside zip file) is attached.

Hope this helps.

-RC

[daniels31790]

It helps a lot, thank you.

[MAF4Fam6]

You are welcome!
Glad I was able to help out.

-RC

[daniels31790]

Hey quick question,

I don't have the RunSQL in my Actions list. I was saving this project until now, and found out I didn't have it.

Thanks

[daniels31790]

Nevermind found it, had to click "show all actions"