Dlook up problem

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • a Khan
    #1

    Dlook up problem

    I am getting error, RUN TIME ERROR 13, type mismatch
    I have 2 tables. products and tblUpdates. My form frmProductUpdat e is
    assosiated with tblUpdates.I am trying to update quantity on hand. can
    any one help plzz


    Private Sub Quantity_AfterU pdate()
    Dim TotalQty As Integer


    TotalQty = DLookup("[Quantity]", "[Products]", "[ProductID]=" &
    Forms![frmProductUpdat e]![ProductID])
    If (TotalQty - [QtyOnHand]) < 12 Then
    MsgBox "Inventory Below 0. Quantity Issued is To High!"
    Cancel = True
    Exit Sub
    End If
    TotalQty = TotalQty - [QtyOnHand]
    DoCmd.SetWarnin gs False
    DoCmd.RunSQL "UPDATE [Products] SET " & _
    "[Products].[QtyOnHand] = " & TotalQty & _
    "WHERE ((([Products].[ProductID])='" & [ProductID] & "'));"
    DoCmd.SetWarnin gs True
    End Sub

    *** Sent via Developersdex http://www.developersdex.com ***
    Tags: None
    • Randy Harris
      #2
      Re: Dlook up problem

      "a Khan" <ahmad341@hotma il.com> wrote in message
      news:Iw5ee.24$J d7.1488@news.us west.net...[color=blue]
      > I am getting error, RUN TIME ERROR 13, type mismatch
      > I have 2 tables. products and tblUpdates. My form frmProductUpdat e is
      > assosiated with tblUpdates.I am trying to update quantity on hand. can
      > any one help plzz
      >
      >
      > Private Sub Quantity_AfterU pdate()
      > Dim TotalQty As Integer
      >
      >
      > TotalQty = DLookup("[Quantity]", "[Products]", "[ProductID]=" &
      > Forms![frmProductUpdat e]![ProductID])
      > If (TotalQty - [QtyOnHand]) < 12 Then
      > MsgBox "Inventory Below 0. Quantity Issued is To High!"
      > Cancel = True
      > Exit Sub
      > End If
      > TotalQty = TotalQty - [QtyOnHand]
      > DoCmd.SetWarnin gs False
      > DoCmd.RunSQL "UPDATE [Products] SET " & _
      > "[Products].[QtyOnHand] = " & TotalQty & _
      > "WHERE ((([Products].[ProductID])='" & [ProductID] & "'));"
      > DoCmd.SetWarnin gs True
      > End Sub
      >
      > *** Sent via Developersdex http://www.developersdex.com ***[/color]

      Looks to me like ProductID might be a text field, since you have quotes
      around it in your UPDATE statement. You don't have quotes around it in the
      DLookUP.



        Comment

        • Keith
          #3
          Re: Dlook up problem

          "a Khan" <ahmad341@hotma il.com> wrote in message
          news:Iw5ee.24$J d7.1488@news.us west.net...[color=blue]
          >I am getting error, RUN TIME ERROR 13, type mismatch[/color]

          Somewhere along the way you are trying to make a join on fields of different
          data types. Also, you will need to use quotation marks around text type
          field names in your SQL.

          Regards,
          Keith.
          https://www.keithwilby.com



            Comment

            • khan
              #4
              Re: Dlook up problem

              no its not a text, it is actually an autonumber field. I made mistake
              in UPDATE but it is giving me error TotalQty = DLookup("[Quantity]",
              "[Products]", "[ProductID]=" &
              Forms![frmProductUpdat e]![Prod­uctID])

                Comment

                • khan
                  #5
                  Re: Dlook up problem

                  getting, error, 3075
                  Syntax Error (Missing Operator) in query expression '8WHERE
                  [Products].[ProductID]=1'


                  it is working now but now I am getting trouble at another line of code
                  in the same block

                  DoCmd.RunSQL ("UPDATE [PRODUCTS] SET [Products].[QtyOnHand] =" &
                  TotalQty & "WHERE [Products].[ProductID]=" & [ProductID] & ";")

                  AM i MISSING SOME THING

                    Comment

                    • Randy Harris
                      #6
                      Re: Dlook up problem

                      "khan" <ahmad336@gmail .com> wrote in message
                      news:1115228828 .679390.141730@ o13g2000cwo.goo glegroups.com.. .[color=blue]
                      > getting, error, 3075
                      > Syntax Error (Missing Operator) in query expression '8WHERE
                      > [Products].[ProductID]=1'
                      >
                      >
                      > it is working now but now I am getting trouble at another line of code
                      > in the same block
                      >
                      > DoCmd.RunSQL ("UPDATE [PRODUCTS] SET [Products].[QtyOnHand] =" &
                      > TotalQty & "WHERE [Products].[ProductID]=" & [ProductID] & ";")
                      >
                      > AM i MISSING SOME THING[/color]

                      Yes you are. Look at the error message. It's telling you exactly what is
                      wrong. You are missing a space in front of the word WHERE.


                        Comment

                        • khan
                          #7
                          Re: Dlook up problem

                          Thanks Randy < I appoligize man I got my answer here I did not know that

                            Comment

                            Previous template Next
                            Working...