349800 vb access work
N/A
Paid on delivery
Quite an easy requirement – just cant get my head around it for some reason!
We have a system where products are despatched. An order is sent to the warehouse, and the warehouse then sends a file back, matching the invoice (or order) number with the item Serial Number sent out. Each product has its own unique serial number, so no two products despatched will have the same serial number.
We then need to update our invoice records. The invoice structure is basically a header table, with a sub table of items (enclosed). The order structure is the same – its self explanatory which is which. Each item has its own record in the table – so if 5 items of the product ‘X' are sold, then 5 records would be there (as opposed to having a quantity field).
The other table included is the data received from the warehouse – called import_data.
I would ordinarily just update using a query, but of course its not that easy… There may be more than one of the same type of product in the invoice (indeed there can be hundreds) and the invoice may contain all sorts of other products, even warranties etc. Equally, there may be 3 of the same products in the invoice, but not all may be despatched directly from the warehouse (for example a customer may have picked one up, with 2 to follow). For that reason, each item should be updated sequentially, by pressing the button on the form, following certain checks.
Firstly, the item should not have been already allocated a serial number (in other words for the system to update, the field serialno should be empty.
Secondly, the product codes must match.
Third, if the [import_data}![status] is ‘invoice' then the table to be updated is [invoice_itemdata] whereas if it is ‘order' then it should be [order_itemdata].
Fourth : An alert should be raised if the number of items despatched are greater than the number of items invoiced.
So essentially the only field that needs updating is the field Serialno with the data from the field [import_data]![sno]
PLUS - the extended spec - thus...
We have a Stock Record System, where like the invoice system below, each item of stock (unique serial number) has a header record in a stockbk table, and then a linked table called stock history (linked by serialno), where any action that happens to the stock is recorded, such as original purchase, sale, return etc etc.
The stockbk table should already be populated with a stock record that matches the incoming data from the warehouse – inasmuch as before it can be ‘warehoused’ it will have been booked in, so will have a header record describing the product (product code) and a serial number. Equally it will have a stock history record that records it as having been booked in.
The system will need to write a further record into the stock history table based on the data from the warehouse excel file. The record will need to reference the invoice or order table (depending on whether the excel file indicates its an order or an invoice), and get the contact info (surname is all that’s required) and then write into the stock history. The caveat here is that it should also check that the last stock history record for that serial number was not something that booked the stock out (ie a sale, demo, write off), but the opposite – something that booked it in. It is very very unlikely that it will be the case that its not ‘booked in’, but we have to check as it will throw up input errors from the warehouse or other areas. Equally, we need to trap errors such as no matching record found.
In fact any exceptions could be written to a new table (not designed yet) for manual entry at a later date – as long as the user was alerted to the fact that there were exceptions.
Project ID: #2095628
