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

 

[Andrzejek]

"It does not work" is not enough. What does it do now? What do you want to happen? Please explain.

Have fun.

---- Andy

 

[Andrzejek]

I think your logic needs to be adjusted.

You only accept pType = "I", but if you pass anything else than "1" in pLoc_cls, you always will execute BLUE line of you logic, you will never go into any other SQL:

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 [green]'pType = "I" And pLoc_cls <> "1" [/green][blue]
mySql = "Update toolcls_Jax set qty_tot = (qty_tot +" & pQty & "), qty_ol = (qty_ol - " & pQty _
& ") where barcode = '" & pBarcode & "';"[/blue]
End If
Else
...

Have fun.

---- Andy

 

[SkipVought]

your control CODE is honked! You have stray ELSEs!

did you COMPILE?

Also your control LOGIC is VERY suspect.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MazeWorX]

You thought it was simple ?? >> then everyone would be doing it lol

skip is right you cant End IF then Else

is this what you meant?

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

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

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

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

 

[MajP]

I am pretty certain that is not what the OP meant, because that never short circuits and it is equivalent to simply

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

Either pType = I and ploc_Cls = "3" or everything else falls to the last line.


Likely something like.

If pType = "I"
  select case ploc_cls
    case "1"
        'do something when I and 1
    case "2"
        'do somehing when I and 2
    case "3"
         'do something when I and 3
    case else
       'do you do something when I and not 1,2,3?
  end select
end if

or if you do something when it is not I

 If pType = "I"
  select case ploc_cls
    case "1"
        'do something when I and 1
    case "2"
        'do somehing when I and 2
    case "3"
         'do something when I and 3
    case else
       'do you do something when I and not 1,2,3?
    end select
  else
   ' Do something when Not I
   select case ploc_cls
    case "1"
        'do something when not I and is 1
    case "2"
        'do somehing when not I and 2
    case "3"
         'do something when not I and 3
    case else
       'do you do something when not I and not 1,2,3?
  end select
end if

 

[TheAceMan1]

punky001 . . .

I'll be using [blue]pType[/blue] to make the same determination. I'll also be using [blue]pLoc_cls[/blue] to determine which table to update. With that I come up with the following:

[blue]   Dim db As DAO.Database, sqlTable As String, sqlExpr As String, sqlWhere As String
   
   Set db = CurrentDb
   sqlTable = Choose(Val(pLoc_cls), "UPDATE toolcls_Jax ", _
                                    "UPDATE toolcls_Mob ", _
                                    "UPDATE toolcls_May ")
   If pType = "I" Then
      sqlExpr = "SET [qty_tot] = ([qty_tot] -" & pQty & "), " & _
                    "[qty_ol] = ([qty_ol] + " & pQty & ") "
   Else
      sqlExpr = "SET [qty_tot] = ([qty_tot] +" & pQty & "), " & _
                    "[qty_ol] = ([qty_ol] - " & pQty & ") "
   End If
   
   sqlWhere = "WHERE [barcode] = '" & pBarcode & "';"
   [green]'Debug.Print sqlTable & sqlExpr & sqlWhere[/green]
   
   db.Execute sqlTable & sqlExpr & sqlWhere, dbFailOnError
   
   Set db = Nothing[/blue]

I believe this should do.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

lol me too was a little quick on the cut/paste on my way out the door on friday for the weekend. What was suppose to be there was as suggested by Maj was a Case senerio

broke my one rule read it before you hit submit

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

 

[punky001]

Thanks to all for replies....Aceman - I tried yours first.

Here's the new code:
Public Sub UpdateQty_TOT(pType As String, pBarcode As String, pQty As Integer, pLoc_cls As String)

Dim db As DAO.Database, sqlTable As String, sqlExpr As String, sqlWhere As String

Set db = CurrentDb
sqlTable = Choose(Val(pLoc_cls), "Update toolcls_Jax ", _
"Update toolcls_Mob ", _
"Update Toolcls_May ")

If pType = "I" Then
sqlExpr = "SET [qty_tot] = set [qty_tot] = ([qty_tot] -" & pQty & "), " & _
"[qty_ol] = ([qty_ol] + " & pQty & ") "
Else
sqlExpr = "SET [qty_tot] = set [qty_tot] = ([qty_tot] _" & pQty & "), " & _
"[qty_ol] = ([qty_ol] + " & pQty & ") "

End If

sqlWhere = "Where [barcode] = '" & pBarcode & "';"
'Debug.Print sqlTable & sqlExpr & sqlWhere

db.Execute sqlTable & sqlExpr & sqlWhere, dbFailOnError
Set db = Nothing

End Sub


I ran it and get the following Compile error - Argument not optional on the following:


Private Sub Form_AfterInsert()
If Not IsNull(Me.BARCODE) Then
UpdateQty_TOT "I", Me.BARCODE, Me.QTY_OL

If IsNull(gOrigQty) Then
gOrigQty = 0
End If
' UpdateToolHistory "I", Me.BARCODE, Me.QTY_OL, Me.SHOP, Me.COST, Me.BeginDate, Date, CInt(gOrigQty)
End If

Me.cmbBarCode.Requery
End Sub

What am I missing??

 

[TheAceMan1]

punky001 . . .

In ...

[blue]UpdateQty_TOT "I", Me.BARCODE, Me.QTY_OL[/blue]

... you forgot [blue] pLoc_cls[/blue]. Also since [blue]UpdateQty_TOT[/blue] is a subroutine you need to install a call statement:

[blue][purple][b]Call[/b][/purple] UpdateQty_TOT "I", Me.BARCODE, Me.QTY_OL, [b]Your pLoc_cls reference here[/b][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[punky001]

I tried adding Call and got the following compile error "Expected: end of statement" with the "I" highlighted.

 

[TheAceMan1]

punky001 . . .

Post what you have now ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

how bout

Call UpdateQty_TOT [red][b]([/b][/red]"I", Me.BARCODE, Me.QTY_OL, Your pLoc_cls reference here[red][b])[/b][/red]

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

 

[punky001]

Module Code:
Option Compare Database
Option Explicit
Public Sub UpdateQty_TOT(pType As String, pBarcode As String, pQty As Integer, pLoc_cls As String)
Dim db As DAO.Database, sqlTable As String, sqlExpr As String, sqlWhere As String
Set db = CurrentDb
sqlTable = Choose(Val(pLoc_cls), "Update toolcls_Jax ", _
"Update toolcls_Mob ", _
"Update Toolcls_May ")

If pType = "I" Then
sqlExpr = "SET [qty_tot] = set [qty_tot] = ([qty_tot] -" & pQty & "), " & _
"[qty_ol] = ([qty_ol] + " & pQty & ") "
Else
sqlExpr = "SET [qty_tot] = set [qty_tot] = ([qty_tot] _" & pQty & "), " & _
"[qty_ol] = ([qty_ol] + " & pQty & ") "

End If

sqlWhere = "Where [barcode] = '" & pBarcode & "';"
'Debug.Print sqlTable & sqlExpr & sqlWhere

db.Execute sqlTable & sqlExpr & sqlWhere, dbFailOnError
Set db = Nothing
End Sub

I ran again using Call () and got the following new error “Can’t find the field ‘Loc_Cls’ referred to in your expression” pointing to the Call Update line.

Private Sub Form_AfterInsert()
Dim strSQL, Loc_Cls, strMsg As String
If Not IsNull(Me.BARCODE) Then
Call UpdateQty_TOT("I", Me.BARCODE, Me.QTY_OL, Me.Loc_Cls)
If IsNull(gOrigQty) Then
gOrigQty = 0
End If
' UpdateToolHistory "I", Me.BARCODE, Me.QTY_OL, Me.SHOP, Me.COST, Me.BeginDate, Date, CInt(gOrigQty)
End If
Me.cmbBarCode.Requery
End Sub

 

[MazeWorX]

did you check your controls name?

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

 

[punky001]

OK Guys....Here's the changes I made...In the module - I changed the ploc_cls to match the field loc_cls in the event procedure. I still get the same error "Can't find the field 'Loc_Cls' referred to in your expression" pointing to the Call Update line.


Module:
Option Compare Database
Option Explicit
Public Sub UpdateQty_TOT(pType As String, pBarcode As String, pQty As Integer, Loc_Cls As String)
Dim db As DAO.Database, sqlTable As String, sqlExpr As String, sqlWhere As String
Set db = CurrentDb
sqlTable = Choose(Val(Loc_Cls), "Update toolcls_Jax ", _
"Update toolcls_Mob ", _
"Update Toolcls_May ")

If pType = "I" Then
sqlExpr = "SET [qty_tot] = set [qty_tot] = ([qty_tot] -" & pQty & "), " & _
"[qty_ol] = ([qty_ol] + " & pQty & ") "
Else
sqlExpr = "SET [qty_tot] = set [qty_tot] = ([qty_tot] _" & pQty & "), " & _
"[qty_ol] = ([qty_ol] + " & pQty & ") "

End If

sqlWhere = "Where [barcode] = '" & pBarcode & "';"
'Debug.Print sqlTable & sqlExpr & sqlWhere

db.Execute sqlTable & sqlExpr & sqlWhere, dbFailOnError
Set db = Nothing

End Sub

Event Procedure AFTER INSERT:

Private Sub Form_AfterInsert()
Dim Loc_Cls As String
If Not IsNull(Me.BARCODE) Then
Call UpdateQty_TOT("I", Me.BARCODE, Me.QTY_OL, Me.Loc_Cls)
If IsNull(gOrigQty) Then
gOrigQty = 0
End If
' UpdateToolHistory "I", Me.BARCODE, Me.QTY_OL, Me.SHOP, Me.COST, Me.BeginDate, Date, CInt(gOrigQty)
End If
Me.cmbBarCode.Requery
End Sub

I cannot figure out what I am missing and being under the gun to get this complete is probably not helping matters any! Am I not defining loc_cls field correctly? I appreciate any help ......

 

[PHV]

Provided that Loc_Cls is either a control in your current form or a field in the underlaying recordset, I'd replace this:
Call UpdateQty_TOT("I", Me.BARCODE, Me.QTY_OL, Me.Loc_Cls)
with this:
UpdateQty_TOT "I", Me!BARCODE, Me!QTY_OL, Me[!]![/!]Loc_Cls

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

 

[punky001]

Thanks PHV - I made that change and got the same error. The loc_cls field is located on the subform: Toolcls_May.loc_cls

 

[PHV]

So, you wanted this ?
UpdateQty_TOT "I", Me!BARCODE, Me!QTY_OL, Me!Toolcls_May.Form!Loc_Cls

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

 

[punky001]

OK....Here's what supposed to happen....and the legacy code worked great (look at first post) until I added the new Loc_cls field which defines the location.
Private Sub form_AfterInsert() is supposed to call for the Update Qty_tot module - this will update the Toolcls_May.Qty_tot and Qty_OL fields. I keep getting the "Can't find the field 'Loc_Cls' referred to in your expression" pointing to the UpdateQty_TOT line of the Private Sub Form_AfterInsert().