Quantity Update Using Sql?

[techsponge]

Access 2003

I am scanning barcodes into a bound text field.

After scanning is complete I want the user to click the finished button, which will count the serial numbers scanned in, and add that number to the existing inventory balance.


I added an unbound text box (qty) with the following code on the OnClick event of the "Finished" button to count the records, and it seems to work fine.


Me.Qty.Value = Me.RecordsetClone.RecordCount

Question is how do I update the Item Master table with the current count + the record count?

DoCmd.RunSQL "UPDATE T11_Item_Master set T11_57_StockTakeQty = T11_57_StockTakeQty + Forms!frmSerialNumEntry!qty;"


Thanks

 

[AlexCuse]

Maybe this:

strSQL = "UPDATE T11_Item_Master set T11_57_StockTakeQty = T11_57_StockTakeQty + " & Forms("frmSerialNumEntry").Controls("qty").Value & ";"

DoCmd.RunSQL strSQL

You needed to reference your control value differently and outside the quotes.


Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[techsponge]

Thanks Alex,

Tried the syntax as you posted, which returne an error stating it could not find the form? Triple checked and the form name is correct.

I changed to this:
strSQL = "UPDATE T11_Item_Master set T11_57_StockTakeQty = T11_57_StockTakeQty + " & me.qty.Value & ";"

DoCmd.RunSQL strSQL

Which removed the error but did not update the T11_57_StockTakeQty column.

I would also like to update T11_Item_Master.T11_56_StockTakeDate with the Date()


Many thanks in advance



Is there a better way to do this?

 

[AlexCuse]

the syntax you are using to get 'me.qty.value' is not supported for this method in VBA. You need to replace it with

Forms("frmSerialNumEntry").Controls("qty").Value

To update the date it would be simply this:

strSQL = "UPDATE T11_Item_Master set T11_57_StockTakeQty = T11_57_StockTakeQty + " & Forms("frmSerialNumEntry").Controls("qty").Value & ", T11_56_StockTakeDate = Date();"

You also are going to want to add a where clause to your strSQL, so that it doesn't perform the update for every record. So that strSQL would be:

strSQL = "UPDATE T11_Item_Master set T11_57_StockTakeQty = T11_57_StockTakeQty + " & Forms("frmSerialNumEntry").Controls("qty").Value & ", T11_56_StockTakeDate = Date()[COLOR=red]
WHERE [YOUR ID FIELD] = Forms("frmSerialNumEntry").Controls("ID CONTROL").Value[/color];"

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".