UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> .addnew, Access 2013    
 
   
Alialas
post Mar 5 2018, 12:56 PM
Post#1



Posts: 9
Joined: 24-February 18



Am using a main form for posting ware house inventory into a subform , the posted data in main form are (ID , recede_no) and in sub form are ( item , qunty , price ) , now I create a command bottom with the code below to add the new data details ( items and quantities ) into another table , but each time I press the cmd bottom it adds the whole quantities all over again not only the recent ones < any clues please
Private Sub Command27_Click()
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim concat As Long
Set db = CurrentDb
Set rs1 = db.OpenRecordset("material_detail")
Set rs2 = db.OpenRecordset("materials_inv")
rs2.MoveFirst
Do Until rs2.EOF
concat = rs2!qunty
rs1.MoveFirst
Do Until rs1.EOF
If rs2![Item] = rs1![Item] Then
concat = (concat) + (rs1!qunty)
rs1.Edit
End If
rs1.MoveNext
Loop
rs2.Edit
rs2!qunty = concat
rs2.Update
rs2.MoveNext
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
End Sub
Go to the top of the page
 
projecttoday
post Mar 5 2018, 03:22 PM
Post#2


UtterAccess VIP
Posts: 10,365
Joined: 10-February 04
From: South Charleston, WV


It's because you are looping through all of the details, not just recently added details. You need 1) to make a query that only reads recently added details, 2) to change it so you start with 0 instead of the existing quantity, or 3) to do away with this calculated value and do the total quantity calculation whenever you need from the details (the recommended design).
Go to the top of the page
 
Alialas
post Mar 6 2018, 02:03 AM
Post#3



Posts: 9
Joined: 24-February 18



the two tables have a one-to-many relations , when adding a new record in main form that will change the (ID) in main form that will lead to the adding of new records in subform thus will be added to the quantities on the (materials_inv) table , this is the idea but i can not execute it in a code
This post has been edited by Alialas: Mar 6 2018, 02:05 AM
Go to the top of the page
 
projecttoday
post Mar 6 2018, 02:36 AM
Post#4


UtterAccess VIP
Posts: 10,365
Joined: 10-February 04
From: South Charleston, WV


Why do you need to store the quantity in _inv if you can get it from _dev?
Go to the top of the page
 
Alialas
post Mar 6 2018, 02:52 PM
Post#5



Posts: 9
Joined: 24-February 18



So that the subtracted materials amounts can be calculated from the same table via selling processes ( sell form) , the result will be the warehouse exact materials .. this is how it thought i can deal with the both operations ( in & out) materials for any time inventory purposes .
Go to the top of the page
 
projecttoday
post Mar 6 2018, 07:15 PM
Post#6


UtterAccess VIP
Posts: 10,365
Joined: 10-February 04
From: South Charleston, WV


Inventory is a complicated subject. Have you looked at Allen Browne's website?

The general rule in software design is not to store calculated values (there are exceptions).

Your code reads through 2 tables with no filtering at all. So is this part of the data entry process that gets executed every time some quantity of goods is received or is this a separate process for updating quantities in the main table from time to time?
Go to the top of the page
 
Alialas
post Mar 7 2018, 12:36 AM
Post#7



Posts: 9
Joined: 24-February 18



it is a part of data entry , each time parts are received or sold this procedure should be executed .
Go to the top of the page
 
DanielPineault
post Mar 7 2018, 06:38 AM
Post#8


UtterAccess VIP
Posts: 6,274
Joined: 30-June 11



What is rs1.Edit for exactly?

And are you trying to edit or add a new entry? Currently you are using .Edit.

I think you'll need to explain further for us to be able to better assist you. A screenshot or sample database would go a long way in helping us understand your current setup, but I agree with George that you're opening 2 table without any filtering, this will have an impact on performance and things will progressively slowdown over time as your tables get populated with more and more data. With the current approach, you are effectively recalculating the value for every entry in materials_inv, why? Should you not only be recalculating those being edited in the form?


Go to the top of the page
 
projecttoday
post Mar 7 2018, 07:50 AM
Post#9


UtterAccess VIP
Posts: 10,365
Joined: 10-February 04
From: South Charleston, WV


George?

Yes, more information would be nice. But if you decide to keep the calculated values, what you need to do is develop a procedure for syncing-up the 2 tables. You need this in case they get out of sync. For example, the power could go off in the middle of an update. What would you do? And then, would this procedure suffice by itself? I mean, do you have to have that update immediately every time you make an entry? Or could you just run this update once or several times a day?
Go to the top of the page
 
Alialas
post Mar 8 2018, 12:27 PM
Post#10



Posts: 9
Joined: 24-February 18



this is exactly what am trying to know " how to update only items on subform not all item in material_inv"
Go to the top of the page
 
DanielPineault
post Mar 8 2018, 12:33 PM
Post#11


UtterAccess VIP
Posts: 6,274
Joined: 30-June 11



Directly use the subform's recordset then.

CODE
Set rs = Me.RecordSet


or

CODE
Set rs = Me.SubformName.Form.RecordSet



Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2018 - 06:26 AM