Help with Code

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

    Help with Code

    Hi, I need some help form someone.

    I use my database for my online shop, so once I have entered a order
    onto the database I have a button that creates the Email to the
    customer to let them know the order has been placed.

    I use to use [ProductID] as a auto number but I have just changed it to
    [ProductCode] Which is not a auto number as I type them in.

    But now my VB code for the email button does not work.
    Can any one help me

    Old Code

    strSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
    Me.OrderNumber
    CRS1.Open strSql, CurrentProject. Connection, adOpenKeyset,
    adLockReadOnly
    While Not CRS1.EOF
    strItemsOrdered = strItemsOrdered & "Item : " & _
    DLookup("[ProductName]", "tblProduct ", "[ProductID] =" &
    CRS1.Fields("Pr oductID")) & vbCrLf & "Qty : " &
    CRS1.Fields("Qu antity") & vbCrLf

    strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
    DLookup("[PriceIncVAT ]", "tblProduct ", "[ProductID] =" &
    CRS1.Fields("Pr oductID")) & vbCrLf


    The email use to look like this

    Item : Test Strips
    Qty : 2
    Unit Price : £15
    Total (Inc VAT): £30.00

    I have changed ProductID to ProductCode in the VB code but it does not
    work

    strSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
    Me.OrderNumber
    CRS1.Open strSql, CurrentProject. Connection, adOpenKeyset,
    adLockReadOnly
    While Not CRS1.EOF
    strItemsOrdered = strItemsOrdered & "Item : " & _
    DLookup("[ProductName]", "tblProduct ", "[ProductCode] =" &
    CRS1.Fields("Pr oductCode")) & vbCrLf & "Qty : " &
    CRS1.Fields("Qu antity") & vbCrLf

    strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
    DLookup("[PriceIncVAT ]", "tblProduct ", "[ProductCode] =" &
    CRS1.Fields("Pr oductCode")) & vbCrLf

    Can any one help I am new to all this

    Tags: None
    • salad
      #2
      Re: Help with Code

      Simon wrote:
      Hi, I need some help form someone.
      >
      I use my database for my online shop, so once I have entered a order
      onto the database I have a button that creates the Email to the
      customer to let them know the order has been placed.
      >
      I use to use [ProductID] as a auto number but I have just changed it to
      [ProductCode] Which is not a auto number as I type them in.
      >
      But now my VB code for the email button does not work.
      Can any one help me
      >
      Old Code
      >
      strSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
      Me.OrderNumber
      CRS1.Open strSql, CurrentProject. Connection, adOpenKeyset,
      adLockReadOnly
      While Not CRS1.EOF
      strItemsOrdered = strItemsOrdered & "Item : " & _
      DLookup("[ProductName]", "tblProduct ", "[ProductID] =" &
      CRS1.Fields("Pr oductID")) & vbCrLf & "Qty : " &
      CRS1.Fields("Qu antity") & vbCrLf
      >
      strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
      DLookup("[PriceIncVAT ]", "tblProduct ", "[ProductID] =" &
      CRS1.Fields("Pr oductID")) & vbCrLf
      >
      >
      The email use to look like this
      >
      Item : Test Strips
      Qty : 2
      Unit Price : £15
      Total (Inc VAT): £30.00
      >
      I have changed ProductID to ProductCode in the VB code but it does not
      work
      >
      strSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
      Me.OrderNumber
      CRS1.Open strSql, CurrentProject. Connection, adOpenKeyset,
      adLockReadOnly
      While Not CRS1.EOF
      strItemsOrdered = strItemsOrdered & "Item : " & _
      DLookup("[ProductName]", "tblProduct ", "[ProductCode] =" &
      CRS1.Fields("Pr oductCode")) & vbCrLf & "Qty : " &
      CRS1.Fields("Qu antity") & vbCrLf
      >
      strItemsOrdered = strItemsOrdered & "Unit Price : " & Chr(163) &
      DLookup("[PriceIncVAT ]", "tblProduct ", "[ProductCode] =" &
      CRS1.Fields("Pr oductCode")) & vbCrLf
      >
      Can any one help I am new to all this
      >
      Go to Queries/New/Design. Add tables tblOrderProduct and tblProduct.
      Link ProductCode between the two tables. Drag ProductDesc to a column
      too. Then run the query. Do you get any results?

      Personally, I'd save the query when you get the query correct, click
      View/SQL, copy the SQL, and make strSQL that string. You'll need to
      modify the SQL string with quotes as needed to separate the variable
      OrderNumber like you have above but that way you don't need the Dlookup.

        Comment

        • Bob Quintal
          #3
          Re: Help with Code

          "Simon" <S.Dickson@shos .co.ukwrote in
          news:1155398368 .406201.240980@ b28g2000cwb.goo glegroups.com:
          Hi, I need some help form someone.
          >
          I use my database for my online shop, so once I have entered a
          order onto the database I have a button that creates the Email
          to the customer to let them know the order has been placed.
          >
          I use to use [ProductID] as a auto number but I have just
          changed it to [ProductCode] Which is not a auto number as I
          type them in.
          >
          But now my VB code for the email button does not work.
          Can any one help me
          >
          Old Code
          >
          strSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
          Me.OrderNumber
          CRS1.Open strSql, CurrentProject. Connection, adOpenKeyset,
          adLockReadOnly
          While Not CRS1.EOF
          strItemsOrdered = strItemsOrdered & "Item : " &
          _
          DLookup("[ProductName]", "tblProduct ", "[ProductID] ="
          &
          CRS1.Fields("Pr oductID")) & vbCrLf & "Qty : " &
          CRS1.Fields("Qu antity") & vbCrLf
          >
          strItemsOrdered = strItemsOrdered & "Unit Price : " &
          Chr(163) & DLookup("[PriceIncVAT ]", "tblProduct ",
          "[ProductID] =" & CRS1.Fields("Pr oductID")) & vbCrLf
          >
          >
          The email use to look like this
          >
          Item : Test Strips
          Qty : 2
          Unit Price : £15
          Total (Inc VAT): £30.00
          >
          I have changed ProductID to ProductCode in the VB code but it
          does not work
          >
          strSql = "SELECT * FROM tblOrderProduct WHERE OrderNumber =" &
          Me.OrderNumber
          CRS1.Open strSql, CurrentProject. Connection, adOpenKeyset,
          adLockReadOnly
          While Not CRS1.EOF
          strItemsOrdered = strItemsOrdered & "Item : " &
          _
          DLookup("[ProductName]", "tblProduct ", "[ProductCode]
          =" &
          CRS1.Fields("Pr oductCode")) & vbCrLf & "Qty : " &
          CRS1.Fields("Qu antity") & vbCrLf
          >
          strItemsOrdered = strItemsOrdered & "Unit Price : " &
          Chr(163) & DLookup("[PriceIncVAT ]", "tblProduct ",
          "[ProductCode] =" & CRS1.Fields("Pr oductCode")) & vbCrLf
          >
          Can any one help I am new to all this
          >
          Is productID stored in a text field?

          If you have changed the type from (auto) number to text, you will
          have to add some delimiters (quotes) to the Dlookup code

          To add delimiters inside a string that's already in a string, you
          have to double thenm up.

          The existing.
          DLookup("[ProductName]", "tblProduct ", "[ProductID] =" &
          CRS1.Fields("Pr oductID"))

          needs to be
          DLookup("[ProductName]", "tblProduct ", "[ProductID] =""" &
          CRS1.Fields("Pr oductID") & """")

          Make the same change in the other dlookup.



          --
          Bob Quintal

          PA is y I've altered my email address.

          --
          Posted via a free Usenet account from http://www.teranews.com

            Comment

            Previous template Next
            Working...