Results 1 to 10 of 10
  1. #1
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55

    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 offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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 05:52 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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
    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 2007-2019
    www.BaldyWeb.com

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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
    I like my method better, but neither of you is executing the SQL.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    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 offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Jason has a FAQ on the syntax here:

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

  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
    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 2007-2019
    www.BaldyWeb.com

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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 offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 2007-2019
    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, 10:03 AM
  2. update backend from command button
    By slimjen in forum Forms
    Replies: 4
    Last Post: 07-16-2014, 08:39 AM
  3. Replies: 7
    Last Post: 05-13-2014, 10:07 AM
  4. Replies: 1
    Last Post: 12-03-2012, 02:50 PM
  5. Replies: 1
    Last Post: 01-20-2010, 12:54 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