Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 21st, 2007, 11:10 PM
Registered User
 
Join Date: Jan 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update table using SQL query from form

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

 
Old January 24th, 2007, 08:52 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old January 24th, 2007, 10:08 AM
Registered User
 
Join Date: Jan 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old January 24th, 2007, 11:23 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old January 24th, 2007, 11:24 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Update one table to another table using DTS in SQL Printmaker SQL Language 0 July 24th, 2007 07:17 AM
Update Query using inner join with a linked table ceekay Access 3 June 8th, 2007 12:35 PM
Update Table From Form ksbrain Access 2 January 9th, 2007 01:49 PM
Update table from form leeloo Access VBA 1 June 28th, 2004 11:50 AM
Access update table from form values trevander Access VBA 2 April 28th, 2004 03:06 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.