Results 1 to 8 of 8
  1. #1
    NISMOJim is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2010
    Posts
    273

    Starting out slow, but still wrong


    Ultimately, i'm trying to update a record in a table, but keep getting a run time error 3061 too few parameters, expected 1. Some of the changes I've tried, I get a syntax error.

    Code:
    CurrentDb.Execute "UPDATE tblVisit SET tblVisit.VisRsn = " & Me.txtReason & " WHERE tblVisit.VisitID = '" & Me.txtVisID & "'"
    VisRsn is a short text field, and the format for txtReason is blank.
    VisitID is an autonumber, & txtVisID format is also blank.
    I've checked the spelling, and scoured the internet to figure out what is wrong. I've used this code many times in other places, so i'm stumped.

    When working, I will need to update several other fields in the record, but starting small to see what I'm doing wrong.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The error is often caused by a misspelled object (form control, table or field name, form name, etc.). If you've validated all such reference names, then often, the other culprit is that coded sql can't be processed the same way as is done by the query side of things. In those cases, it's usually a form field reference that can't be resolved. Assuming the form is open, then there are 2 approaches that I know of. One is to declare and define all the parameters (google MS Access qdef.parameters). A simpler way that works for me is to declare a variable for the parameter and use that in the sql statement instead. So, vba can define what Me.txtMyTextbox is and then assign it to strMyString, and vba can then deal with
    ...WHERE tblVisit.VisitID = '" & strMyString & "'"...

    Having said that, I'm surprised the message was about 1 parameter when you have 2 references. Makes me think it is a spelling problem.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If VisRsn is a text field then use apostrophe delimiters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by June7 View Post
    If VisRsn is a text field then use apostrophe delimiters.
    Good catch, but I would have expected a data type mismatch error for that. Perhaps that would have been a second error message when the parameter problem is solved (assuming the mismatch isn't the cause of the parameter error).

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It is the cause. I tested an UPDATE action to verify.

    However, now I notice that VisitID is a number field so apostrophes there will probably cause data type mismatch error. Those apostrophes need to be eliminated.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    From your field descriptions there should be two parameter errors. Try this
    Code:
    CurrentDb.Execute "UPDATE tblVisit SET tblVisit.VisRsn = '" & Me.txtReason & "' WHERE tblVisit.VisitID = " & Me.txtVisID & ";"
    Or just

    Code:
    CurrentDb.Execute "UPDATE tblVisit SET VisRsn = '" & Me.txtReason & "' WHERE VisitID = " & Me.txtVisID & ";"
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    NISMOJim is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2010
    Posts
    273
    The addition and omission of the apostrophe's did the trick once I able to get back on. I had it right in the beginning, but it didn't work, so I changed it to this. The database was acting funny when I tried making the changes (wouldn't let me type the characters, like the keyboard was locked up), then the db crashed, deleting all of the code. Luckily I made a backup & changed the code there, so all is working perfectly now. Thank you for your help.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome. We were all pleased to help.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Just starting out - need advice
    By NauticalGent65 in forum SQL Server
    Replies: 13
    Last Post: 03-11-2018, 10:25 AM
  2. Starting Access using VBA
    By DHIGHTO in forum Modules
    Replies: 3
    Last Post: 02-19-2015, 09:48 AM
  3. just starting out - need help
    By abomb987 in forum Access
    Replies: 2
    Last Post: 05-16-2014, 06:54 AM
  4. Problem with Form starting at the wrong position of a page
    By thebionicredneck2003 in forum Forms
    Replies: 2
    Last Post: 05-10-2013, 12:49 PM
  5. Starting a new database
    By JFo in forum Access
    Replies: 9
    Last Post: 08-25-2011, 11:00 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