Results 1 to 10 of 10
  1. #1
    ksmith is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    59

    Update Statement in form code

    I have a form that I have been using to enter info into a table. I have this form setup so that it automatically goes to a new record so the user can start entering data. Form: frmMaterialIn Table: tblMaterialInvoice

    This is where I enter all material invoices. I have another table that tracks raw material. Table: tblRawMaterial

    I would like to be able to update the tblRawMaterial as new invoices are entered into tblMaterialInvoice with frmMaterialIn.

    I am appending a new record into tblMaterialInvoice with each entry.


    And finding the correct record in tblRawMaterial with a common field.

    The common field in both tables, is IncomingMaterialNum (Text Field).

    The field that I am updating in tblRawMaterial is Amount.

    I have been using the UPDATE statement to try to do this. I can get the statement to work as long as I use numbers but when I try to use the name of the form text box in the SQL statement I get an error message.

    I made a Command Button is test my calculation.
    The Code below works.

    Private Sub cmdAddRaw_Click()
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    ' Change values in the Amount field to Amount = (Amount = Amount + txtAmount)
    dbs.Execute "UPDATE tblRawMaterial " _
    & "SET Amount = Amount + 2222 " _
    & "WHERE IncomingMaterialNum = '2437';"
    End Sub

    But, when I replace the '& Set' line of code to this,

    & "SET Amount = Amount + me.txtAmount " _
    I get an error code.

    txtAmount is the name of the form text box where the invoice amount is entered.

    Also, when I get the amount to work I will change the '2437' to the actual text box name for Incmoing Material Number; txtMaterialNum.

    Any help would be greatly appreciated.
    Ksmith

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have to concatenate the form reference:

    & "SET Amount = Amount + " & me.txtAmount & " " _

    personally I'd drop the space and add it to the next line, but that would work with what you have now. You'll need to do the same thing with the criteria, maintaining the single quotes around the value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ksmith is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    59

    Smile Fixed the problem

    Thanks Baldy

    That did it!!

    Thanks a million

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problemo ksmith, happy to help. Welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pipelian is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    7

    Unhappy

    Quote Originally Posted by pbaldy View Post
    You have to concatenate the form reference:

    & "SET Amount = Amount + " & me.txtAmount & " " _

    personally I'd drop the space and add it to the next line, but that would work with what you have now. You'll need to do the same thing with the criteria, maintaining the single quotes around the value.

    Hi guys I have used this solutions as well, but there is only one more thing going on with my code, it is that only works if I enter numeric characters, does not work whit alphas...please help me am I missing something?
    here is my code:

    Sql = "UPDATE log_book " & "SET Sampled_by = " & Me.IDTextBox.Text & "" & " WHERE RM_Number = " & Me.RM_NumberTextBox.Text


    almost forgot...(Fields Data type is "text" in access db)

    Thanks in advance

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Text values require delimiters. Here's a tutorial:

    http://www.baldyweb.com/BuildSQL.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    pipelian is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    7
    Quote Originally Posted by pbaldy View Post
    Text values require delimiters. Here's a tutorial:

    http://www.baldyweb.com/BuildSQL.htm
    HI pbaldy sorry for my late reply, i've been trying to use your tutorial but i could not make it work....(my silliness)

    Please...do you mind taking a look to my code and let me know what am i doing wrong?..

    Dim da As OleDb.OleDbDataAdapter
    Dim strMyString As String

    strMyString = Sampled_byTextBox.text

    Sql = "UPDATE log_book " & "SET Sampled_by = " & ' & strMyString & ' & "WHERE RM_Number = " & Me.RM_NumberTextBox.Text

    RM_NumberTextBox.Text value is already given, user enters new value on "Sampled_byTextBox.text"

    i will appreciate any extra help you could give me..

    Thank you so much

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, your code will error due to the use of .Text, since that control would require focus. Drop that or use .Value, which is the default. The tutorial uses the Chr() function to put in the quotes, which can be used outside the main quotes. You're using the actual single quote, which would have to be inside (you also have a "space" problem):

    "...SET Sampled_by = '" & strMyString & "' WHERE..."
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    pipelian is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    7
    Quote Originally Posted by pbaldy View Post
    For starters, your code will error due to the use of .Text, since that control would require focus. Drop that or use .Value, which is the default. The tutorial uses the Chr() function to put in the quotes, which can be used outside the main quotes. You're using the actual single quote, which would have to be inside (you also have a "space" problem):

    "...SET Sampled_by = '" & strMyString & "' WHERE..."
    Thank you sooooooooooooooooooo much........ it worked. I appreciate it..

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help, and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error in form code
    By devesa in forum Access
    Replies: 3
    Last Post: 05-25-2010, 10:15 AM
  2. Replies: 3
    Last Post: 04-14-2010, 10:07 AM
  3. Code to Update Excel from Access
    By portmancp in forum Programming
    Replies: 2
    Last Post: 03-10-2010, 03:06 AM
  4. Passing Criteria to IN statement from Form
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-09-2009, 04:02 AM
  5. update table in loop based on IF statement
    By LALouw in forum Access
    Replies: 0
    Last Post: 07-26-2009, 08:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums