Problems with MS Access

[Vincen]

I am currently in a process of designing an invoice processing system / Accounting system in MS Access but starting to give up.
When it comes to entering an invoice line in access, how can you make it not to process that line until the full invoice lines are punched in?

My relationship details is as follows.

CUSTOMER has Many Invoices
INVOICE has MANY INVOICE LINES
PRODUCT has many INVOICE LINES

 

[apr pillai]

Option A:

The current record that you key-in is saved to the table only when you exit from that record and move on to another record. Before completion of that record you can press Ctrl+Z to remove the current record's contents.

After completion of the record you can select the record selector (one or more records) control at the left side and press delete key to remove those records. That action will trigger a warning message before deleting the records.

If you want to remove complete records of an Invoice, you may build a Delete Query by using the current Invoice Number and Customer Code references as Criteria. You may run the Delete Query through a Command Button click event Macro to remove all line entries of a particular Invoice of a Customer.

Option B:

Alternatively you can use a temporary table (with the same structure of the main Invoice Table) to process entries of a particular Invoice. When you are done with the Invoice entries, use an Append Query to add the entries to the main table and use a Delete Query to remove the entries from the temporary table to make it ready for the next Invoice.

Run the Append & Delete Queries from separate Command button click event Macros. If you want to cancel the invoice entries use only the Delete Query running command button.

 

[Micron]

I have used Option B several times. However, the posting does not cover the fact that if you are going to allow future edits, an UPDATE query will also be required. I open a form as "new", "edit" or "copy" and control form properties (e.g. Allow Additions) as required on the fly. Copy would load a modified existing record into the temp tables (removing the value from a textbox that would have a primary key). All actions thereafter, such as final or temporary saves, validation of data in controls or which query to use when done) is controlled by the form mode. However, I found it less of a hassle if I assigned Me.OpenArgs to a variable rather than refer to the property when needed. When there's an error while debugging, the openarg property is often lost. It is possible to reset the reference (not the property) in the immediate window if it's lost, but the variable has to be at the module level. I use the user ID as the key to operate on records since there are multiple users on a common front end. If multiple users each have their own FE, keep the temp table in the FE.

NOTE: you may need a module level variable to trap that edits have ocurred (bolIsDirty) because the main form IsDirty property will be false if the user edits then moves to a subform that is bound to another table.