Results 1 to 10 of 10

Append or update if existing via VBA command button

  1. #1
    rawlstonb is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    28

    Append or update if existing via VBA command button

    Scope: Create a button that updates a note if it exists in the table or appends to the table if it doesn't...

    I tried to alter this that I got off some random site I was searching, but it gives a "Run-time error '3061' Too few parameters. Expected 1." on the red line



    Code:
    Private Sub cmdUpdateNotes_Click()
    
    Dim db As Database
    Dim rec As Recordset
    Dim sSQL As String
    
    
    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select * from tblWONotes WHERE [SO] = [Forms]![TESTfrmWOList]![txtNoteSO]")
    
    
    rec.MoveFirst
    rec.MoveLast
    
    
    If rec.RecordCount = 0 Then
      sSQL = "INSERT INTO tblWONotes ( SO, Notes )" & _
            " SELECT [Forms]![TESTfrmWOList]![txtNoteSO] AS Expr2, [Forms]![TESTfrmWOList]![txtNoteComment] AS Expr1" & _
            " FROM tblWONotes;"
    End If
    
    
    If rec.RecordCount = 1 Then
      sSQL = "UPDATE tblWONotes SET tblWONotes.Notes = [Forms]![FrmWOList]![txtNoteComment]" & _
            " WHERE (((tblWONotes.SO)=[Forms]![TESTfrmWOList]![txtNoteSO]));"
    End If
    
    
    Set db = Nothing
    Set rec = Nothing
    
    
    End Sub

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,852
    you have included the control reference within your quotes; i.e. you have quoted the entire thing. I've included single quotes (hard to see) because I think the reference evaluates to a string. Below is only an example, because you've got more than one instanced of the issue. Also, if there are no records, your code should break because you're trying to movefirst/last when there are no records. I'll play with further suggestions in the meantime.
    "Select * from tblWONotes WHERE [SO] = '" [Forms]![TESTfrmWOList]![txtNoteSO] & "'")
    Last edited by Micron; 01-11-2019 at 04:52 PM. Reason: added info
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start every sentence with, like, "so"?

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,333
    I would do this after opening the recordset:

    Code:
    If rs.EOF Then
      rs.AddNew
      rs!SO = [Forms]![TESTfrmWOList]![txtNoteSO]
    Else
      rs.Edit
    End If
    
    rs!Notes = [Forms]![FrmWOList]![txtNoteComment]
    rs.Update
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,852
    Consider (with notes)
    Code:
    Private Sub cmdUpdateNotes_Click()
    
    Dim db As Database
    Dim rec As Recordset
    Dim sSQL As String
    
    On Error GoTo errHandler
    
    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select * from tblWONotes WHERE [SO] = '" & [Forms]![TESTfrmWOList]![txtNoteSO] & "'")
    
    'if both BOF and EOF is true there are no records
    If Not (rs.BOF And rs.EOF) Then 'there is at least 1 rec, so UPDATE
      sSQL = "UPDATE tblWONotes SET tblWONotes.Notes = '" & [Forms]![FrmWOList]![txtNoteComment] & "'" & _
      " WHERE (((tblWONotes.SO)= '" & [Forms]![TESTfrmWOList]![txtNoteSO] & "'));"
    
    Else 'BOF and EOF is true, meaning no records, so APPEND
    
      sSQL = "INSERT INTO tblWONotes (SO, Notes) SELECT '" & [Forms]![TESTfrmWOList]![txtNoteSO] "' AS Expr2, " & _
      [Forms]![TESTfrmWOList]![txtNoteComment] & "' AS Expr1 FROM tblWONotes;"
    End If
    
    exitHere:
    On Error Resume Next
    Set db = Nothing
    Set rec = Nothing
    Exit Sub
    
    errHandler:
    msgbox "Error " & err.Number & ": " & err.Description
    Resume exitHere
    
    End Sub
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start every sentence with, like, "so"?

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,333
    I like my method better, but neither of you is executing the SQL.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  6. #6
    rawlstonb is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    28
    Micron, yours gave an error... but a combination of your syntax that was triggering the original error and pbaldy's simple addition did the trick

    So... thanks to both of you

    I'm not really sure I'm ever going to fully understand quotation syntax in VBA

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,333
    Jason has a FAQ on the syntax here:

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

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,333
    By the way, I suspect that this:

    Quote Originally Posted by Micron View Post
    Code:
      sSQL = "INSERT INTO tblWONotes (SO, Notes) SELECT '" & [Forms]![TESTfrmWOList]![txtNoteSO] "' AS Expr2, " & _
      [Forms]![TESTfrmWOList]![txtNoteComment] & "' AS Expr1 FROM tblWONotes;"
    would need a WHERE clause, otherwise it's going to add however many records exist in the table (though I'm not sure, since the values are only coming from a form). Using a VALUES clause instead of SELECT would avoid that.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,852
    would need a WHERE clause, otherwise it's going to add however many records exist in the table
    I took it from the code that there would only be one record in the recordset, otherwise simply checking for a count of 1 wouldn't make sense. Normally I use the VALUES syntax. I thought is was OK for air code that I couldn't test

    I should have said that the assumption was that there would only be one record in the table that satisfied the criteria, because the expectation seemed to be that there'd only one in the recordset

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,333
    The recordset would return a single record (presumably). That wouldn't affect the append query, there's no relationship between them.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

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

Similar Threads

  1. Update existing and append new
    By krausr79 in forum Access
    Replies: 2
    Last Post: 10-29-2015, 09:03 AM
  2. update backend from command button
    By slimjen in forum Forms
    Replies: 4
    Last Post: 07-16-2014, 07:39 AM
  3. Replies: 7
    Last Post: 05-13-2014, 09:07 AM
  4. Replies: 1
    Last Post: 12-03-2012, 01:50 PM
  5. Replies: 1
    Last Post: 01-20-2010, 11:54 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
  •  
Tech Forums: Microsoft Office Forums