473,323 Members | 1,560 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

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.

Expand|Select|Wrap|Line Numbers
  1. Main Form (Table: StockTrans)
  2. Trans_ID
  3. Trans_Type
  4. Trans_Date
  5. Trans_Cust
  6. Trans_Note
Expand|Select|Wrap|Line Numbers
  1. SubForm (Table: StockTrans_Items)
  2. Trans_ID fk
  3. ItemID fk
  4. Trans_Item_Qty
Take note that, the above works but it is not what I prefer.

Example to describe my current situation.
Expand|Select|Wrap|Line Numbers
  1. Trans_ID    ItemID       Trans_Item_Qty
  2. T0001       CATFOOD-1     10 TINS
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.
Oct 7 '09 #1

✓ answered by NeoPa

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 :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [StockTrans_Items] INNER JOIN [tblTemp]
  2.     ON StockTrans_Items.Trans_ID=tblTemp.Trans_ID
  3.    AND StockTrans_Items.ItemID=tblTemp.ItemID
  4. SET    StockTrans_Items.Trans_Item_Qty=tblTemp.Quantity
  5. WHERE  tblTemp.UserName='YourName'
Expand|Select|Wrap|Line Numbers
  1. DELETE
  2. FROM   [tblTemp]
  3. WHERE  [UserName]='YourName'
Your code would need to determine what YourName is of course. Post if you need help with that.

9 8183
NeoPa
32,556 Expert Mod 16PB
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.
Oct 7 '09 #2
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPost_Click()
  2.  
  3. Dim AddTransItem As String
  4. Dim counter As Integer
  5.  
  6. Dim totalItem As Integer
  7. Dim x As String
  8.  
  9. '-To count the number of Item in the Temp Table
  10. totalItem = Val(DCount("[Trans_ID]", "Trans_Items"))
  11.  
  12. '-For my own checking
  13. MsgBox (totalItem)
  14.  
  15. 'This Do...Loop is to update every each Item(ProductID) Quantity
  16. 'Make necessary adjustment to Item.ItemQty
  17. counter = 0
  18. Do Until counter = totalItem
  19.  
  20.     x = "UPDATE Item SET ItemQty = ItemQty +" & Val(Temp_Qty_Issued) & " WHERE ItemID = '"
  21.  
  22.     DoCmd.RunSQL x
  23.  
  24.     counter = counter + 1
  25.     'If counter = 10 Then Exit Do
  26. Loop
  27.  
  28. '-This will transfer all the records from TEMP Table to the final Table
  29. AddTransItem = "INSERT into StockTrans_Items SELECT * FROM Trans_Items"
  30.  
  31. DoCmd.RunSQL AddTransItem
  32.  
  33. End Sub
Question:

@Line20
Expand|Select|Wrap|Line Numbers
  1. x = "UPDATE Item SET ItemQty = ItemQty +" & Val(Temp_Qty_Issued) & " WHERE ItemID = '"
1-I am not sure how to code WHERE ItemID = subformItemID, should I put Temp.ItemID
Then, that will be
Expand|Select|Wrap|Line Numbers
  1. x = "UPDATE Item SET ItemQty = ItemQty +" & Val(Temp_Qty_Issued) & " WHERE ItemID ='" & Temp.ItemID & "'"
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.

Expand|Select|Wrap|Line Numbers
  1. x = "UPDATE Item SET ItemQty = ItemQty +" & Val(Temp_Qty_Issued) & " WHERE ItemID ='" & Temp.ItemID & "' AND Temp.ItemNo = & ItemNoLoop"
'The below are part of the code inside the Do Until...Loop
ItemNoLoop = ItemNoLoop + 1
Oct 8 '09 #3
ChipR
1,287 Expert 1GB
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.
Oct 8 '09 #4
NeoPa
32,556 Expert Mod 16PB
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.
Oct 8 '09 #5
Dear Neopa
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.
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.
Table1-Transaction (Main Form)
Table2-Transaction_Details : Once user happy, all the records in Temp_Details will be copy to here as a main storage.
Table3-Temp_Details (sub Form) to temporary hold all the Item involved in the particular Transaction
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

Expand|Select|Wrap|Line Numbers
  1. Dim counter As Integer
  2. Dim totalItem As Integer
  3. Dim getItemData As DAO.Database
  4. Dim byRecord As String
  5.  
  6. '-To count the number of Item in the Temp Table
  7. totalItem = Val(DCount("[Trans_ID]", "Trans_Items"))
  8.  
  9. '-For my own checking
  10. MsgBox (totalItem)
  11.  
  12. counter = 1
  13.  
  14. 'To Do Until ... Loop
  15. totalItem = totalItem + 1
  16.  
  17. Set getItemData = CurrentDb
  18.  
  19. 'This Do Until...Loop is suppose to update every each Item's Quantity
  20. 'Again, I have not finish the code yet as I not sure how to implement the Update
  21. Do Until counter = totalItem
  22.  
  23.     'This SQL Line is suppose to extract the ItemID for each Item
  24.     'I used ItemNo as a Tracking/Comparing Each Record
  25.     'If you confuse, just think as:
  26.     'In a receipt, there are many items. To differentiate each another, it can by Item Number or Item ID
  27.     'Eg. Item No 1 Coca-Cola Coke 1Litre | Item No. 2 iPod Touch 3G 32GB
  28.     byRecord = "SELECT ItemID, ItemQty FROM Trans_Items WHERE ItemNo =" & counter
  29.  
  30.     getItemData.Execute "byRecord"
  31.  
  32.     counter = counter + 1
  33.  
  34.     MsgBox (byRecord)
  35. Loop
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.
Oct 9 '09 #6
NeoPa
32,556 Expert Mod 16PB
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.
Oct 9 '09 #7
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.

Expand|Select|Wrap|Line Numbers
  1. Dim counter As Integer
  2. Dim totalItem As Integer
  3.  
  4. Dim UpdateItemQty As DAO.Database
  5. Dim byRecord As String
  6.  
  7. Dim getEntryItemID As String
  8. Dim getEntryItemQty As Integer
  9.  
  10. '-To count the number of Item in the Temp Table
  11. totalItem = Val(DCount("[Trans_ID]", "Trans_Items"))
  12.  
  13. '-For my own checking
  14. MsgBox (totalItem)
  15.  
  16. counter = 1
  17.  
  18. 'To Do Until ... Loop
  19. totalItem = totalItem + 1
  20.  
  21. Set UpdateItemQty = CurrentDb
  22.  
  23. 'This Do Until...Loop is suppose to update every each Item's Quantity
  24. 'Again, I have not finish the code yet as I not sure how to implement the Update
  25. Do Until counter = totalItem
  26.  
  27.     getEntryItemID = DLookup("[ItemID]", "Trans_Items", "[Item_No] = " & counter & "")
  28.  
  29.     'MsgBox (getEntryItemID)
  30.  
  31.     getEntryItemQty = DLookup("[Temp_Qty_Issued]", "Trans_Items", "[Item_No] = " & counter & "")
  32.  
  33.     'MsgBox (getEntryItemQty)
  34.  
  35.     'This SQL Line is suppose to extract the ItemID for each Item
  36.     'I used ItemNo as a Tracking/Comparing Each Record
  37.     'If you confuse, just think as:
  38.     'In a receipt, there are many items. To differentiate each another, it can by Item Number or Item ID
  39.     'Eg. Item No 1 Coca-Cola Coke 1Litre | Item No. 2 iPod Touch 3G 32GB
  40.     byRecord = "UPDATE Item SET ItemQty = ItemQty +" & Nz(Val(getEntryItemQty)) & " WHERE ItemID= '" & getEntryItemID & "'"
  41.  
  42.     UpdateItemQty.Execute byRecord
  43.  
  44.     counter = counter + 1
  45.  
  46.     MsgBox (byRecord)
  47. Loop
  48.  
  49. UpdateItemQty.Close           
  50. Set UpdateItemQty = Nothing 
  51.  
  52. End Sub
Oh yeah, there might be quite a number of MsgBox (). I used this to understand error/problem if there is any.
Oct 9 '09 #8
NeoPa
32,556 Expert Mod 16PB
@weirdguy
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).
@weirdguy
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.
Oct 9 '09 #9
NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [StockTrans_Items] INNER JOIN [tblTemp]
  2.     ON StockTrans_Items.Trans_ID=tblTemp.Trans_ID
  3.    AND StockTrans_Items.ItemID=tblTemp.ItemID
  4. SET    StockTrans_Items.Trans_Item_Qty=tblTemp.Quantity
  5. WHERE  tblTemp.UserName='YourName'
Expand|Select|Wrap|Line Numbers
  1. DELETE
  2. FROM   [tblTemp]
  3. WHERE  [UserName]='YourName'
Your code would need to determine what YourName is of course. Post if you need help with that.
Oct 10 '09 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Mary Litten | last post by:
Hi - (This is my very first post) I have gotten to this point of registering to post because I have been spinning my wheels so long, I believe I am all caught up in the weeds. (and mud) I have...
0
by: Bob | last post by:
Hi Everybody I've spent a few hours pondering this one. I have a form frmInvoice (InvoiceID) and a subform zfrmInvoice (InvoiceID) I am able to print a passable report that...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
1
by: xmp333 | last post by:
Hi, I have a form that is designed as a data sheet view. Attached to this is a subform with some VB code. When the user clicks on a row, the subform should pop up and run the VB code which...
15
by: NomoreSpam4Me | last post by:
Hi there i have a little problem with my invoice. Here it is: i have a main menu with buttons, one of my button is "Create new invoice", when click on it a form pop up so i can enter my...
6
by: MLH | last post by:
When the vehicle entry form (frmVehicleEntryForm) first opens, the additional owner SubForm control (frmAddnlOwnrListSubForm) is enabled. You can click on it and it will accept the focus. But after...
9
by: Ecohouse | last post by:
I have a main form with two subforms. The first subform has the child link to the main form identity key. subform1 - Master Field: SK Child Field: TrainingMasterSK The second subform has a...
8
by: Christina123 | last post by:
Currently working with Microsoft Office 2000 and whatever version of Access came with that. I am developing a database to track the comings and goings of shared tools. Everything works...
10
by: Roger | last post by:
ms-access97 & sql server2005 two tables tblItem tblItemFeature form frmItem contains subform frmItemFeature each form is based on their respective table creating new record and filling in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.