Invoice & Subform: Add Multiple Record (Item) at Once

Hello all,

I almost complete my mini project - Stock Inventory:
To track goods/products enter and exit from warehouse or simply known as Stock Transaction (IN/OUT). This also include a Inventory Catalog which basically show all the products in the warehouse, each product details including category, description and most importantly quantity on hand.

As mentioned above, I almost complete the database except the most important Form - New Stock Transaction where User enter all the necessary details to issue an IN or OUT Transaction. To make things easy to understand, it share many similarity with Invoice and New Order.

So far, I had made the form worked. A subform is within the form to handle the Product and its Quantity.

Take note that, the above works but it is not what I prefer.

Example to describe my current situation.
When I finished T0001 record and enter 10 Tins in end of the record, it will auto update the Item Quantity on Hand.

I would prefer it to allow me to key-in all the involved Products then I click a POST button. This will confirm all the details of the transaction and make the necessary adjustment to the quantity on hand at Once, not after every one single completed record.

My idea:
- Create another table that hold the involved Products' Detail temporary until User POST the Transaction
- Right after that, use VBA 'Loop' method to update each entered records (Temp Product Detail Table). This is to update (make necessary adjustment) Item Quantity On Hand (Table: Item)
- After that with success, move all the records (Temp Table) to StockTrans_Items (Table).
- After that, delete all records (Temp Table)

If you are confuse, feel free to ask and any suggestion is welcome too.

re: Invoice & Subform: Add Multiple Record (Item) at Once

Forms (even continuous forms) only have one record buffer. To move to another record, the current changes must either be saved or discarded.

The only way I can think of to provide the functionality you require is to bind the form to another (call it Temp) table and, when you're happy, copy the data across to your live table en masse. This would best be done using SQL but you could use Recordsets in VBA if you prefer.

re: Invoice & Subform: Add Multiple Record (Item) at Once

Question:

@Line20
1-I am not sure how to code WHERE ItemID = subformItemID, should I put Temp.ItemID
Then, that will be
2-To update Record-by-Record, how to write SQL Update Statement. Should I add a new Column to Temp Table, called ItemNo as the PK.

'The below are part of the code inside the Do Until...Loop
ItemNoLoop = ItemNoLoop + 1

re: Invoice & Subform: Add Multiple Record (Item) at Once

Looks like you'll want to open a recordset of Trans_Items. Then you can loop through the records and refer to the current record's ItemID in your SQL string.

re: Invoice & Subform: Add Multiple Record (Item) at Once

You don't give any indication if this is following on from my suggestion in post #2 or whether it's simply a random jump to somewhere along your thought path. As such I can only take it as a fresh question.

1. It's unusual for an ID field to be of type Text (as you have it - See Quotes (') and Double-Quotes (") - Where and When to use them).
   Without knowing what you want to compare it to it's hard to help here. If it's on a subform the Referring to Items on a Sub-Form may help.
2. No idea where you're coming from so the question, with no context, is impossible to answer without making assumptions to make up for lack of information. Not a good idea in my view.
   If you can post sensible questions, preferably without ignoring or failing to respond to earlier posts (which can help to provide context), then we will help where possible.

re: Invoice & Subform: Add Multiple Record (Item) at Once

Dear Neopa
I did follow your advise on Post #2 as you can find at Line28.

I admitted that Post #3 Code is not complete and have many flaws. I posted it up as I want to get comment to guide me through.

I'm sorry if all these really confusing. I promised to clear up in this post.

I want to be clear on this.
Each transaction can contains one or more Items

Each transaction involves 3 tables and 2 forms.
A main form stored Transaction_ID, Date, Customer, Note
A sub form stored ItemNo, ItemID, TransQty, Transaction_ID

Below are my code:
The following codes are not complete as I am still figuring about how to update the Item.ItemQty from Temp_Details.TransQty

I hope the explanation above is clear enough.

To continue the code [within the Loop], what should I do to extract the ItemID and ItemQty of each ItemNo? After extraction, I should able update by using SQL Statement: ItemID as the WHERE statement and ItemQty as the SET Value.

re: Invoice & Subform: Add Multiple Record (Item) at Once

I wondered if you had. I really posted that way I suppose to bring to your attention that we're only human. We are trying to work from the other side of a web page and, though we can often make intelligent guesses as to what you're thinking etc, it really is much easier if you communicate these things with us as we go. It's not like you're in the room with us, or even that we can view your project over your shoulder or anything.

I haven't time just now to go through this in detail (I feel it probably warrants a bit of concentration) but I'll be happy to when I get some time free later.

re: Invoice & Subform: Add Multiple Record (Item) at Once

Still, I would like to thank you for your guidance, NeoPa and ChipR. I really appreciate your replies and comments.

And for NeoPa, I heard you and will always keep in mind. I hope I did not offence/frustrate you in any way.

I found a working solution for my problem.
Note that: It still not complete, but the code able to Loop through the Temporary_Table and update every single record.

If you refer back to my previous post, I used Item_No as the WHERE clause.

However, I still welcome any comment, especially a more efficient solution.

Oh yeah, there might be quite a number of MsgBox (). I used this to understand error/problem if there is any.

re: Invoice & Subform: Add Multiple Record (Item) at Once

No. You're good. I still plan to look at this (I expect there'll be some time over the weekend when things get quieter).
This is always a good approach. You may be interested in Debugging in VBA. It gives various tips on easy ways to debug your code.

re: Invoice & Subform: Add Multiple Record (Item) at Once

Generally if you are using Domain Aggregate functions (DLookup(); DCount(); etc) within a loop that is not good programming. If you want to do it in VBA then it can be done, but this doesn't seem to be very much like the sort of code I'd expect to see to be honest. Recordset processing would be required. See Access VBA DAO Recordset Loop Using Two Recordsets for something to get you started.

In post #2 I suggested saving the data away into a temporary table before doing a simple update (using SQL). When the update is completed mind, you would need to clear away the temporary data used. This method (to be honest this effects either method) would require an extra element in the key of the temporary table to ensure that multiple users didn't ever interfere with each other's data. An operator ID would do the trick I expect. The other elements of the key would have to be the [Trans_ID] & the [ItemID]. The only other field required would be a [Quantity].

When the CommandButton is clicked to complete the process you would run a couple of sets of SQL like the following :
Your code would need to determine what YourName is of course. Post if you need help with that.