auto populate and move data for inventory db

By nelzone ·
Q1. I need to populate my table by entering starting sequence and populate data by indicating how many pieces or items to add.

E.g. I will enter starting sequence 301 in a field and 100 as pieces on another field and AfterUpdate should be able to populate my table (tbl1) with 301 to 400 series.

Q2. I need to move data from one table (tbl1) to another table (tblStockOut) by entering starting sequence of items to move and ending with last sequence.

e.g. I will enter starting sequence 340 on the FROM Field and sequence 359 on the TO Field and AfterUpdate should move sequence 340 to 359 to tblStockOut leaving 301 to 339 and 360 to 400 series in tbl1.

i'm using ms access 203.

hope somebody can help.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Going to have to be VBA in Access I think

by Tony Hopkinson In reply to auto populate and move da ...

Look up append queries and delete if you mean take the record out of tbl1 and puts it in tblStockOut.

You aren't thinking sets here.
In a full DBMS you could do this with
SQL and a while loop, but you would try to avoid it.

For instance what happens if you run the thing again. Dulplicates , invalid primary key...
Dealing with that means checking to see if you already have, or complicated logic to check whether you should.
Then there's knock on populate tbl1 move to tblStockOut, now if you only check tbl1 the populate will succeed, but move will fail later. So do you overrwite, skip or fail at that point, check both (and any others that come along)

Tin of worms that...

So why do you need to prepopulate and why do you need to move?

If you had a table productRanges, your show product code could show it if it existed or create it if it didn't but the range was valid then show it.

Equally the move operation could be just setting another column to indicate the operation had occurred and the row was in a different state.
Thats just
Update Stock set Moved = true where sequence between 301 and 400
301 and 400 coming from fields on your form.

Aside from performance reasons splitting teh same sort of data between two or more tables is a bad move.

What if you want a query from both now you are in a situation where you need a union query...

Related Discussions

Related Forums