Results 1 to 14 of 14
  1. #1
    laavista is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    42

    Update sql using currentdb.execute

    When using currentdb.execute, I'm getting the error: too few parameters expected 1



    1st table, tbl_contract
    2 fields: ContractID (number, long)
    StartDate (date, short)

    2nd table: tbl_Customer
    2 fields: CustomerID (number, long)
    RequestDate (date, short)

    Have form: frm_Mainform (for tbl_Contract)
    Have a date field on form: StartDate

    When an user enters a StartDate I need to UPDATE the RequestDate field in tbl_Customer

    '===========
    Dim dtSTARTDATE as date
    dim strSTARTDATE as string
    dim NumID as long
    dim strSQL as string

    dtSTARTDATE = forms!frm_Mainform[STARTDATE]
    strSTARTDATE = dtSTARTDATE
    NumID = Me.ContractID

    strSQL = "UPDATE tbl_customer set tbl_customer.[RequestDate] = ('" & strSTARTDATE & "') where tbl_customer.[CustomerID] = NumID";

    Currentdb.Execute strSQL
    ' ====================

    I also tried putting using the 'date' in the strSQL with no luck
    strSQL = "UPDATE tbl_customer set tbl_customer.[RequestDate] = #" & dtSTARTDATE & "# where tbl_customer.[CustomerID] = NumID";

    Your help would be greatly appreciated!

  2. #2
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    The semicolon ending your SQL statement is outside the quotes.

    try placing it immediately before the quotes at the end of your strSQL line and see if that helps.

    Also, if your RequestDate field is date/time format, you'll want to use the hashes (#) instead of single quotes.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And you have to concatenate the NumID value into the string.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    laavista is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    42

    Solved!

    You are both incredible. I changed the placement of the ; and I concatenated the NumID and it works. I worked on this for HOURS and was so frustrated.

    THANK YOU for taking the time to look at my question and respond. I really appreciate it!

  5. #5
    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. By the way, the semi-colon is optional in Access. I never include it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    robsworld78 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Posts
    181
    Hi, I'm trying to accomplish something similiar and this is almost working but I think I'm having the problem you had until you concatenate the NumID.

    For me its changing all the records in the table, ignoring the ID. You said you concatenate the NumID and it worked but what exactly did you do? I know that means link but how did you do that, through the code? This is the code I have.

    Code:
    Private Sub Form_Load()
    Dim strBarsReturned As String
    Dim OrderID As Long
    Dim strSQL As String
    
    strBarsReturned = 0
    OrderID = Me!NightCountOrdersSubform.Form!OrderID
    
    strSQL = "UPDATE Inventory set Inventory.[BarsReturned] = ('" & strBarsReturned & "') where Inventory.[OrderID] = OrderID;"
    
    CurrentDb.Execute strSQL
    Me.NightCountEnterReturnsSubform.Form.Requery
    End Sub
    If you have any ideas that would be great.

    Thanks

  7. #7
    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 variable, just as with the value (no single quotes if a numeric data type).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    robsworld78 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Posts
    181
    Sorry I don't understand what your saying?

  9. #9
    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 did this:

    Code:
    ...set Inventory.[BarsReturned] = ('" & strBarsReturned & "') where..
    rather than this:

    Code:
    ...set Inventory.[BarsReturned] = ('strBarsReturned') where...
    You need to do the same thing with the variable in the criteria. JET can't interpret the value of the variable.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    robsworld78 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Posts
    181
    What can I say, its not my day....

    I've been playing the last hour but can't get it. When I make the change you point out and only that change it takes the 0 out of all the records in the table instead of adding them like it was with the extra "&

    When I play with anything else it always throws and errors and highlights yellow "CurrentDb.Execute strSQL"

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does this work?

    strSQL = "UPDATE Inventory set Inventory.[BarsReturned] = " & strBarsReturned & " where Inventory.[OrderID] = " & OrderID
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    robsworld78 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Posts
    181
    Ah your amazing, thank-you! Who knows how long I would have been playing to get that, it gets confusing with all those " ' & _ etc....

    So you've solved Part1 of my 2 part problem, and now I'll tell you part2 and see if you care to help me.

    The form is a long story but everything in my db is fine for normalization and no duplicate data being stored. Reason I say that is because this form has a textbox which does math and that number is stored in the same table I'm playing with on this code. Long story why it has to be this way but its legit.

    Here's the problem on the form.

    There's a textbox called "BarsGiven" then we have "BarsReturned" and "BarsSold". BarsGiven is on a different subform then the other 2 because its query isn't updateable and the other 2 numbers need to change at this point. Both subforms are linked the same to the order info subform and both subforms are linked together with a recordset.

    When a user enters a number in "BarsReturned" it subtracts it from "BarsGiven" and puts the answer in "BarsSold" which is bound to the table. Everything works great if a user enters the 0 or whatever in the "BarsReturned" textbox because it triggers the math when updated and the answer goes in "BarsSold"

    What I want is when this form opens (will only be opened once per order) it adds the 0 to all the records so the system assumes everything is sold.

    At this point is adding the 0 to the field like I want however the form isn't doing the math for "BarsSold" like it would if a user manually entered the 0.

    The math for BarsSold is located in the "AfterUpdate" event on the "BarsReturned" textbox. But when this code runs the form doesn't see it as "BarsReturned" being updated. I've added me.requery to the code so it would requery the form as soon as the table updates but that doesn't trigger it either. I also tried adding the math to the code above which doesn't throw any errors but still produces no results.

    Hopefully this makes sense to you and you have an idea?

    Thanks

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did this get resolved on your other thread? Not clear on what you're doing, but you can make the after update event public and call it from elsewhere.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    robsworld78 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Posts
    181
    Yeah its been solved, thanks to June7 who figured it out for me. Now that I see what he did I don't think I needed this solution as well, the default 0 would have worked, but its good I know how to do it now, thanks to you guys. I couldn't have done the db without the forum thats for sure.

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

Similar Threads

  1. db.Execute Error 3061
    By JimG in forum Programming
    Replies: 5
    Last Post: 05-17-2010, 09:34 AM
  2. Can't get any of my code to execute!
    By blacksaibot in forum Programming
    Replies: 4
    Last Post: 03-16-2010, 08:08 AM
  3. Replies: 3
    Last Post: 02-16-2010, 10:43 PM
  4. Can a report execute generate a file
    By techexpressinc in forum Reports
    Replies: 7
    Last Post: 01-16-2010, 04:03 AM
  5. Execute Command Interactively
    By zephaneas in forum Programming
    Replies: 1
    Last Post: 11-08-2008, 09:56 AM

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