After years of having all my questions answered here, now I can't find it and need help.
We are running a DOS based software for our Order Entry/AR/Inventory. The software is still being sold, and isn't half bad, but doesn't meet our needs. Over the years we have developed an Access database to supplement this program, and use it for printing, customer & order lookup and editing, and even created a full Order Fulfillment system that directly writes to existing records in the database and uses wireless barcode scanners for data entry. It's fantastic.
NOW I want to write Order Entry into this Access database - the beginning stages of eliminating the DOS program and converting to SQL.
The problem is that I don't know if I should edit the data directly in the Header and Line Item file, or if is should copy the data for the sales order into temporary tables and then edit it.
The software we currently use will copy the data out of the main header and lineitem tables and put it in temp tables so you can edit it. When you are done, you complete the order and it copies the data back, including any new lineitem records. I like this feature, because if you want to abort the changes you can, if something gets corrupted while editing (or you crash,) you have a choice to keep the data or not, and it allows for flexibility if you want to do some testing on the order.
BUT I have not been able to find out the best way to do this. I don't think I can use append and update queries, because what if I add a lineitem and then run an update query? Will it add that record? I thought about using VB to open up the main sales order table and rewrite the data line-by-line and add a record if it doesn't exist. Is this the way to go? Should I just maintain the data directly from the tables using queries? Books don't seem to touch on this (or my library isn't big enough.)
What is the best way of creating and maintaining orders in an access database, and what high level processes do you use to manipulate the data?
Thanks in advance - and thanks for all the other answered questions that have helped me through the years!
Matt
Start Free Trial
