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. - Main Form (Table: StockTrans)
- Trans_ID
-
Trans_Type
-
Trans_Date
-
Trans_Cust
-
Trans_Note
- SubForm (Table: StockTrans_Items)
- Trans_ID fk
- ItemID fk
-
Trans_Item_Qty
Take note that, the above works but it is not what I prefer.
Example to describe my current situation. - Trans_ID ItemID Trans_Item_Qty
-
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.
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 : -
UPDATE [StockTrans_Items] INNER JOIN [tblTemp]
-
ON StockTrans_Items.Trans_ID=tblTemp.Trans_ID
-
AND StockTrans_Items.ItemID=tblTemp.ItemID
-
SET StockTrans_Items.Trans_Item_Qty=tblTemp.Quantity
-
WHERE tblTemp.UserName='YourName'
- DELETE
-
FROM [tblTemp]
-
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.
- Private Sub cmdPost_Click()
-
-
Dim AddTransItem As String
-
Dim counter As Integer
-
-
Dim totalItem As Integer
-
Dim x As String
-
-
'-To count the number of Item in the Temp Table
-
totalItem = Val(DCount("[Trans_ID]", "Trans_Items"))
-
-
'-For my own checking
-
MsgBox (totalItem)
-
-
'This Do...Loop is to update every each Item(ProductID) Quantity
-
'Make necessary adjustment to Item.ItemQty
-
counter = 0
-
Do Until counter = totalItem
-
-
x = "UPDATE Item SET ItemQty = ItemQty +" & Val(Temp_Qty_Issued) & " WHERE ItemID = '"
-
-
DoCmd.RunSQL x
-
-
counter = counter + 1
-
'If counter = 10 Then Exit Do
-
Loop
-
-
'-This will transfer all the records from TEMP Table to the final Table
-
AddTransItem = "INSERT into StockTrans_Items SELECT * FROM Trans_Items"
-
-
DoCmd.RunSQL AddTransItem
-
-
End Sub
Question:
@Line20 - 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 - 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. - 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
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.
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. - 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. - 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.
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 - Dim counter As Integer
-
Dim totalItem As Integer
-
Dim getItemData As DAO.Database
-
Dim byRecord As String
-
-
'-To count the number of Item in the Temp Table
-
totalItem = Val(DCount("[Trans_ID]", "Trans_Items"))
-
-
'-For my own checking
-
MsgBox (totalItem)
-
-
counter = 1
-
-
'To Do Until ... Loop
-
totalItem = totalItem + 1
-
-
Set getItemData = CurrentDb
-
-
'This Do Until...Loop is suppose to update every each Item's Quantity
-
'Again, I have not finish the code yet as I not sure how to implement the Update
-
Do Until counter = totalItem
-
-
'This SQL Line is suppose to extract the ItemID for each Item
-
'I used ItemNo as a Tracking/Comparing Each Record
-
'If you confuse, just think as:
-
'In a receipt, there are many items. To differentiate each another, it can by Item Number or Item ID
-
'Eg. Item No 1 Coca-Cola Coke 1Litre | Item No. 2 iPod Touch 3G 32GB
-
byRecord = "SELECT ItemID, ItemQty FROM Trans_Items WHERE ItemNo =" & counter
-
-
getItemData.Execute "byRecord"
-
-
counter = counter + 1
-
-
MsgBox (byRecord)
-
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.
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.
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. - Dim counter As Integer
-
Dim totalItem As Integer
-
-
Dim UpdateItemQty As DAO.Database
-
Dim byRecord As String
-
-
Dim getEntryItemID As String
-
Dim getEntryItemQty As Integer
-
-
'-To count the number of Item in the Temp Table
-
totalItem = Val(DCount("[Trans_ID]", "Trans_Items"))
-
-
'-For my own checking
-
MsgBox (totalItem)
-
-
counter = 1
-
-
'To Do Until ... Loop
-
totalItem = totalItem + 1
-
-
Set UpdateItemQty = CurrentDb
-
-
'This Do Until...Loop is suppose to update every each Item's Quantity
-
'Again, I have not finish the code yet as I not sure how to implement the Update
-
Do Until counter = totalItem
-
-
getEntryItemID = DLookup("[ItemID]", "Trans_Items", "[Item_No] = " & counter & "")
-
-
'MsgBox (getEntryItemID)
-
-
getEntryItemQty = DLookup("[Temp_Qty_Issued]", "Trans_Items", "[Item_No] = " & counter & "")
-
-
'MsgBox (getEntryItemQty)
-
-
'This SQL Line is suppose to extract the ItemID for each Item
-
'I used ItemNo as a Tracking/Comparing Each Record
-
'If you confuse, just think as:
-
'In a receipt, there are many items. To differentiate each another, it can by Item Number or Item ID
-
'Eg. Item No 1 Coca-Cola Coke 1Litre | Item No. 2 iPod Touch 3G 32GB
-
byRecord = "UPDATE Item SET ItemQty = ItemQty +" & Nz(Val(getEntryItemQty)) & " WHERE ItemID= '" & getEntryItemID & "'"
-
-
UpdateItemQty.Execute byRecord
-
-
counter = counter + 1
-
-
MsgBox (byRecord)
-
Loop
-
-
UpdateItemQty.Close
-
Set UpdateItemQty = Nothing
-
-
End Sub
Oh yeah, there might be quite a number of MsgBox (). I used this to understand error/problem if there is any.
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.
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 : -
UPDATE [StockTrans_Items] INNER JOIN [tblTemp]
-
ON StockTrans_Items.Trans_ID=tblTemp.Trans_ID
-
AND StockTrans_Items.ItemID=tblTemp.ItemID
-
SET StockTrans_Items.Trans_Item_Qty=tblTemp.Quantity
-
WHERE tblTemp.UserName='YourName'
- DELETE
-
FROM [tblTemp]
-
WHERE [UserName]='YourName'
Your code would need to determine what YourName is of course. Post if you need help with that.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |