Module code question - Access 2007 2

[punky001]

I made a change to a database I inherited and I'm having a problem getting it to work. I thought it was simple? Listed below is the originial code and it works without issue. All I need to do is add a location field (Loc_cls) to the selection. It does not work. Does any one see anything obvious that I am doing wrong? I would greatly appreciate any help.

Originial code = works great…

'Public Sub UpdateQty_TOT(pType As String, pBarcode As String, pQty As Integer)
'Dim mySql As String
'If pType = "I" Then
' mySql = "Update toolcls_Mob set qty_tot = (qty_tot -" & pQty & "), qty_ol = (qty_ol + " & pQty _
' & ") where barcode = '" & pBarcode & "';"
'Else
' mySql = "Update toolcls_Mob set qty_tot = (qty_tot +" & pQty & "), qty_ol = (qty_ol - " & pQty _
' & ") where barcode = '" & pBarcode & "';"
'End If
'DoCmd.RunSQL (mySql)
'End Sub
¬¬¬¬¬¬¬¬¬¬
New Code using Loc _cls field to distinguish different locations:

Public Sub UpdateQty_TOT(pType As String, pBarcode As String, pQty As Integer, pLoc_cls As String)
Dim mySql As String
If pType = "I" And pLoc_cls = "1" Then
mySql = "Update toolcls_Jax set qty_tot = (qty_tot -" & pQty & "), qty_ol = (qty_ol + " & pQty _
& ") where barcode = '" & pBarcode & "';"
Else
mySql = "Update toolcls_Jax set qty_tot = (qty_tot +" & pQty & "), qty_ol = (qty_ol - " & pQty _
& ") where barcode = '" & pBarcode & "';"
End If
Else
If pType = "I" And pLoc_cls = "2" Then
mySql = "Update toolcls_Mob set qty_tot = (qty_tot -" & pQty & "), qty_ol = (qty_ol + " & pQty _
& ") where barcode = '" & pBarcode & "';"
Else
mySql = "Update toolcls_Mob set qty_tot = (qty_tot +" & pQty & "), qty_ol = (qty_ol - " & pQty _
& ") where barcode = '" & pBarcode & "';"
End If
Else
If pType = "I" And pLoc_cls = "3" Then
mySql = "Update toolcls_May set qty_tot = (qty_tot -" & pQty & "), qty_ol = (qty_ol + " & pQty _
& ") where barcode = '" & pBarcode & "';"
Else
mySql = "Update toolcls_May set qty_tot = (qty_tot +" & pQty & "), qty_ol = (qty_ol - " & pQty _
& ") where barcode = '" & pBarcode & "';"
End If
DoCmd.RunSQL (mySql)
End Sub

 

[PHV]

the legacy code worked great (look at first post)
Really ?
Your posted code don't even compile !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

Thats because its on the subform you cant reference it with me.

[Forms]![Form2]![ChildName].[Form]![Loc_Cls] note sub the ChildName for your child name not the form name

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

btw ignore the [Form2] its from a db i use to test replace it with the main forms name

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[punky001]

Finally!! Works Great!.....thanks to all ...especially to TheAceMan1 and MazeWorX.....stars to Both!!

 

[MazeWorX]

did you forget something lol

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[punky001]

Sorry AceMan1 and MazeWorZ.....stars are there now. Again thanks so much.

 

[MazeWorX]

lol

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>