|
Subject:
|
Update table using SQL query from form
|
|
Posted By:
|
pater53
|
Post Date:
|
1/21/2007 10:10:15 PM
|
I am working on coding an update query in a form that will update a table which tracks our raw material receiving and issue. Can someone look over this newby's code and point me in the right direction?
I have a form [frmMaterialIssue] with two subforms. [subform1] shows material available after an item is entered into [Item].
[subform2] is used for recording how much material from each Lot is used for the Workorder being started.
After I select the pounds of material I am using and the Lot#, I enter it into [subform2]. There could be more than one record for each work order started.
Upon saving the main form I want to update the available stock for each "Lot" in the underlying table for [subform1]. If the available stock is then "0" I also want to insert a check in [ysnClosed] showing all the material for that Lot# has been used.
-----------Structure------------------------------------------------------------------
Main Form::
[frmMaterialIssue]
-[Item]............Item# of material being issued
-[WO]............Workorder being started
-[subform1] ....shows available stock along with Lot#
-[subform2] ....used to record stock issued for each workorder
[tblHeat] ...data entered when material is received from supplier. This is source of [subform1].
fields:: [intBPCLot]..........Lot# given to the material when it arrives from supplier
[intPoundsAvail]....amount of stock available in this lot
[ysnClosed]...........When this is checked it shows all product for this Lot# has been used.
[subform2]...issued stock is recorded here for each workorder. Data is stored in [tblMaterialIssuedStock] including workorder#.
fields:: [intBPCLot]....Lot# of issued material
[intPoundsIssued]....amount of stock issued from each Lot#
---------First Draft of code showing what I want to do-----------------------------------
Dim strSQL As String
strSQL = "UPDATE tblHeat SET intPoundsAvail = (intPoundsAvail - [tblMaterialIssueStock].[intPoundsIssued]),_"
WHERE " & intBPCLot = [tblMaterialIssuedStock].[intBPCLot] & "" & " And Me.strWO = "&[tblMaterialIssueStock].[WO] & " '"
If intPoundsAvail = "<1" Then
ysnClosed.Value = "Yes"
CurrentDb.Execute strSQL, 128
End If
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
1/24/2007 7:52:07 AM
|
I think a good thing for you to do is to create the Inventory Tracking database in Access from the template. Then look at the Products Subform.
The products subform has a footer that has calculated fields. For transactions on the product, the user enters either units purchased, and/or units sold. Then in the footer, there is a calcualted field called UnitsOnHand. The calucaltion is:
=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))
Then on the main form called Products, there is this code in a calculated Unbound text box, set to Enabled No, Locked Yes:
=[Products Subform].Form!UnitsOnHand
This does what I think you want, without storing the value in a table.
So for each Lot ordered, the user can submit transactions against the lot, either added to or sold. The subform footer calculates the available amounts, and that is transmitted to the main form for display.
You can then have an event triggered by the UnitsOnHand text box on the main form to do the last part to your data.
Does this help?
mmcdonal
|
|
Reply By:
|
pater53
|
Reply Date:
|
1/24/2007 9:08:53 AM
|
I have scrapped keeping a value for available stock and now calculate on the fly when we want to issue stock for a work order. I still have not worked out ticking a stock lot closed when we have used all the material as this code was originally set up.
--------
If intPoundsAvail = "<1" Then
ysnClosed.Value = "Yes"
----------
I have made changes which should work yet it does not tick the "ysnClosed" field to closed (checked). Here is my current code.
Dim strSQL As String
If Me!frmMaterialIssuesubform3.Form!intPoundsIssued = Me!frmMaterialIssuesubform2.Form!Expr1 Then
strSQL = "UPDATE tblHeat SET ysnClosed = -1 " & _
"WHERE intBPCLot = '" & Me!frmMaterialIssuesubform3.Form!intBPCLot & "'"
CurrentDb.Execute strSQL, dbFailOnError
End If
-----------------
Any ideas where I may have tripped up?
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
1/24/2007 10:23:20 AM
|
To close the stock lot, add a field called "Closed" Yes/No (check box).
Then on your main form, on the Before Update event, do this:
If Me.RunningTotal = 0 Then
Me.Closed = True
Else
Me.Closed = False
End If
Then in the combo box on your form that allows you to find a stock lot, add the Closed field to the query, Criteria = <>0, and then don't display that column in the combo box. This will prevent the lot from showing up on the form - if you want to do that.
Also, put a label on the form stating the lot is closed, set to invisible, then on the form's On Current event, do this:
If Me.Closed = True Then
Me.lblClosed.Visible = True
Else
Me.lblClosed.Visible = False
End If
HTH
mmcdonal
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
1/24/2007 10:24:49 AM
|
Maybe:
If Me.RunningTotal <= 0 Then
Me.Closed = True
Else
Me.Closed = False
End If
Also, if the lot is closed, set the subform to Allow Additions/Edits = No
mmcdonal
|
