The younger brother have work table has three tables, "stock record", "record" and "inventory"
Stock record

Sales record

Inventory


O master taught under the
I want to increase (decrease or modify the sales records, purchase records, inventory number can synchronous change
And direct copy a sales record is inserted into the table to form a new sales records, or sales record write wrong delete, restore inventory data
Vba program how to write?

CodePudding user response:

One, and this is the most basic transaction, a transaction record update and inventory at the same time,
Such as new stock record of pseudo code below

 
 start affairs(" INSERT INTO stock record... 
 ")IF there are records (" SELECT * FROM inventory WHERE number 1=& lt; Warehousing goods & gt;" ) THEN 
(" UPDATE inventory SET number=number + & lt; Inventory quantity & gt; WHERE number 1=& lt; Warehousing goods & gt;" ) 
The ELSE 
(" INSERT INTO inventory... 
 ")END the IF 
Submit a transaction 

Two, "copy" has been recorded: just at the front desk as input data of the templates, point button or do it according to the new record, for sure,
Three, no matter when, according to the number of changes in a transaction to update the inventory,

CodePudding user response:

Sorry didn't say I'm a excle table with vba how to write a program?

CodePudding user response:

Excel most of the editing functions are automatic, it is difficult to precise control,
The most stupid method: purchase/sales records of these two Worksheet Change event, to fill the inventory table whole again,

CodePudding user response:

It seems that you this, use "formula" -using,

CodePudding user response:

Use the formula is simple,
If the stock record - sheet1
Sales record -- -- -- -- -- -- -- sheet2
Inventory table ap400 in the second row

Is: the size of the region in the c2 enter the following formula: (the specific oneself changes)

=sunif (sheet1! $c $2: $e $1000, "=" & amp; B2, sheet1! $e $2: $$1000) - e sumif (sheet2! $e $2: $g $1000, "=" & amp; B2, sheet2! $g $2: $g $1000)

As for the below it, can be directly drive

CodePudding user response:

All statistics again like line but also have article one thousand the sales record and replenish onr's stock, inventory more than 30 varieties, changes, statistics can be a bit slower?

CodePudding user response:

Consider this scenario: new purchase records, the name is not found in inventory, original
Use the formula can't add a new brand, must code to fill,
Change event, of course, only need to fill in the brand column, inventory quantity and can be directly set a formula, without programming statistics and filling unit,

CodePudding user response:

If must inventory list, or writing code to handle,

Or, only when the query inventory, only temporary statistics (of course also can generate a temporary inventory table), in that case, just update the entry and outbound table simply edit record,

CodePudding user response:

QQ2776478814, I have been a decade of related software, you can contact me