UPDATE Field in Table using SQL & VBA

[stfcTerryA]

Hi

I'm trying to update a field in table via an AfterUpdate event procedure.

Basically two tables:

tblPurchaseOrderItems
f: POrderItemsID - Autonumber
f: ComponentID - Look up to another table
f: QtyOrdered - Number
f: QtyOS - Number (populated by frmPOrderItems.QtyOrdered_AfterUpdate)
Basically copies same value as qty ordered as being qty outstanding.

tblGoodsInItems
f: GoodsInItemsID - Autonumber
f: POrderItemsID - Table Look Up (above) Displays Component and qty ordered
etc.
f: QtyOS - populated by POrderItems.column(4) by AfterUpdate
f: QuantityRecieved - Number

Ok what i then have is a frmGoodsIn with a subform frmGoodInItems (linked to
relavent table)

What i eventually want to do is in the frmGoodsInItems.POrderItemsID combo
is only display the relevent fields (Components ordered) where
tblPurchaseOrderItems.QtyOS is not = 0. So if i recieve my first lot of
goods in all the items purchase should have QtyOS = QtyOrder then as i select
an item and enter QuantityRecieved If that = QtyOrdered then i need to set
the QtyOS to 0 but if not then set QtyOS to however many it needs to be.

However I have no idea how to UPDATE a field in a table using VBA, I have
tried using the following code but it stops at the CurrentDb line i've tried
with and without () Any help greatly appreciated.


Private Sub QuantityRecieved_AfterUpdate()

Dim intQuantity As Integer
Dim strSQL As String

intQuantity = Me!QtyOS - Me!QuantityRecieved

If intQuantity = 0 Then

strSQL = "UPDATE tblPurchaseOrderItems SET QtyOS = 0 " & _
"WHERE POrderItemsID = Me.POrderItemsID"
CurrentDb().Execute strSQL, dbFailOnError


Else

strSQL = "UPDATE tblPurchaseOrderItems " & _
"SET QtyOS = intQuantity " & _
"WHERE POrderItemsID = Me.POrderItemsID"
CurrentDb.Execute strSQL, dbFailOnError

End If

End Sub


Cheers




Terry

 

[Douglas J. Steele]

What do you mean by "it stops at the CurrentDb line"? Are you getting an
error? If so, what's the error?

One thing I noticed in your SQL is that you've got the variables inside of
the quotes. When Jet runs queries, it doesn't know anything about VBA
variables. You have to put the references to variables outside of quotes so
that the value of the variable is in the SQL, not a reference to variable.

Private Sub QuantityRecieved_AfterUpdate()

Dim intQuantity As Integer
Dim strSQL As String

intQuantity = Me!QtyOS - Me!QuantityRecieved

strSQL = "UPDATE tblPurchaseOrderItems " & _
"SET QtyOS = " & intQuantity & " " & _
"WHERE POrderItemsID = " & Me.POrderItemsID
CurrentDb.Execute strSQL, dbFailOnError

End Sub

That assumes that POrderItemsID is a numeric field. If it's text, you'll
need

strSQL = "UPDATE tblPurchaseOrderItems " & _
"SET QtyOS = " & intQuantity & " " & _
"WHERE POrderItemsID = '" & Me.POrderItemsID & "'"

Exagerated for clarity, that last line is

"WHERE POrderItemsID = ' " & Me.POrderItemsID & " ' "

 

[stfcTerryA]

Hi Doug

Many thanks worked a treat!! Still haven't quite got my head around when and
where to use all the quotes.

Cheers


Terry

 

[stfcTerryA]

Hi Doug

I've noticed a problem where everything works fine until i close down the
form and reopen it, basically the combo box which i would select the
component from the POrder, on previously entered forms is now blank and when
i select the combo box all the entries are duplicated bar the one i've
previously entered - which is sort of what i wanted but not on previously
entered data.

For example:

The first time round i select the combo box which shows me a list of items i
ordered on relavent PO - say 4 items. If i've recieved a part shipment say
just one of the items i go ahead and enter the details.

The second time i open the form to add another goods in reciept the PO Item
box is now blank - now this only occurs when i add the criteria <>0 to the
QtyOutstanding field - which is what i wanted. As in the list which shows me
which items are left outstanding but i didn't want it affecting previously
entered data.

The other problem is if i select the combo box all the rows of components
outstanding are duplicated again no idea why. Any suggestions or guidence
appreciated.

Below is all the code i've entered:

On the frmGoodInItems.POrderItemsID Combo box i have the Row Source as a
standard query following is all on one line

SELECT tblPurchaseOrderItems.POrderItemsID,
tblGoodsIn.PurchaseOrder,
tblPurchaseOrderItems.Component,
tblComponents.PartNo,
tblPurchaseOrderItems.Quantity,
tblPurchaseOrderItems.QtyOutstanding

FROM tblComponents

INNER JOIN (tblPurchaseOrderItems
INNER JOIN tblGoodsIn ON
tblPurchaseOrderItems.POrderID=tblGoodsIn.PurchaseOrder) ON
tblComponents.ComponentID=tblPurchaseOrderItems.Component

WHERE ((Not (tblPurchaseOrderItems.QtyOutstanding)=0))

ORDER BY tblPurchaseOrderItems.Component;

I then have a _BeforeUpdate procedure to incriment the Item line number
automatically as i select a new record.

Private Sub POrderItemsID_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then

Dim strWhere As String
strWhere = "GoodsInNumber = " & Nz(Me.Parent!GoodsInID, 0)
Me.LineItem = Nz(DCount("LineItem", "tblGoodsInItems", strWhere), 0) + 1

End If

End Sub

Finally i have a _AfterUpdate() Procedure you just helped me with

Private Sub QuantityRecieved_AfterUpdate()

Dim intQuantity As Integer
Dim strSQL As String

intQuantity = Me!QtyOS - Me!QuantityRecieved

If intQuantity = 0 Then

strSQL = "UPDATE tblPurchaseOrderItems " & _
"SET QtyOutstanding = 0 " & _
"WHERE POrderItemsID = " & Me.POrderItemsID
CurrentDb().Execute strSQL, dbFailOnError


Else

strSQL = "UPDATE tblPurchaseOrderItems " & _
"SET QtyOutstanding = " & intQuantity & " " & _
"WHERE POrderItemsID = " & Me.POrderItemsID
CurrentDb().Execute strSQL, dbFailOnError

End If

End Sub

Cheers



Terry

 

[Douglas J. Steele]

Sorry, I don't really understand what you're trying to do.

You may wish to start a brand new post explaining exactly what it is you're
trying to accomplish.

Incidentally, I don't see any point to your "If intQuantity = 0 Then" check
in the AfterUpdate event. The following accomplishes exactly the same
results:

Private Sub QuantityRecieved_AfterUpdate()

Dim strSQL As String

strSQL = "UPDATE tblPurchaseOrderItems " & _
"SET QtyOutstanding = " & Me!QtyOS - Me!QuantityRecieved & " " & _
"WHERE POrderItemsID = " & Me.POrderItemsID
CurrentDb().Execute strSQL, dbFailOnError

End Sub

 

[stfcTerryA]

Hi Doug,

Thanks a lot and you are absolutely correct there was no need for the if
then else statement.

And i will try a new thread for the other issue.

Cheers


Terry