Error updating data in subform when data has a comma in it.

[DougP]

Sometimes I get and error on red line below.
Error>> Run time Error '3020'
Update or CancelUpdate without Addnew or Edit
It only seems to happen when the data has a comma in it like
Clearwater Grande Condominium, LLC

Private Sub List2_Click()
    
    Dim rs As Recordset
    Dim strData As String

    Dim Conn2 As ADODB.Connection
    Dim Rs1 As ADODB.Recordset
    Dim SQLCode As String
    Set Conn2 = CurrentProject.Connection
    Set Rs1 = New ADODB.Recordset
    
    Set rs = Me.[Company subform].Form.RecordsetClone
    strData = "[Name] like " & Chr(34) & Left(Me.List2.Value, 10) & "*" & Chr(34)
    
    rs.FindFirst strData
        
     If rs.NoMatch Then
        Label4.Caption = "No record found! " & Left(Me.List2.Value, 10)
        SQLCode = "INSERT INTO FoldersNotFound ( Folder ) SELECT " & Chr(34) & Me.List2.Value & Chr(34) & " AS Expr1;"
        Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic
    '  close it this way
        Set Rs1 = Nothing
        Set Conn2 = Nothing
        Me.[FoldersNotFound subform].Requery
     Else
        Me.[Company subform].Form.Bookmark = rs.Bookmark
        'set the field "folder location" in the Company sub form
        [COLOR=red]Me.[Company subform]!FolderLocation = Me.List2.Value[/color]

    End If

DougP
< I Built one

http://www.robots2000.com

 

[DougP]

Addendum to above.
Not sure when it causes error, but when the subform has a pencil or is in edit mode it gives the error. Pressing ESC gets rid of the error and places that record back to non-edit. It does not seem to be a comma causing it.


DougP
< I Built one

http://www.robots2000.com

 

[DougP]

here is what I did

Private Sub List2_Click()
    On Error GoTo Err_List2_Click
    
    Dim rs As Recordset
    Dim strData, strInsertData As String

    Dim Conn2 As ADODB.Connection
    Dim Rs1 As ADODB.Recordset
    Dim SQLCode As String
    Set Conn2 = CurrentProject.Connection
    Set Rs1 = New ADODB.Recordset
    
    If Len(Me.List2.Value) < 15 Then
        strInsertData = Me.List2.Value
    Else
        strInsertData = Left(Me.List2.Value, 15)
    End If

    Set rs = Me.[Company subform].Form.RecordsetClone
    strData = "[Name] like " & Chr(34) & strInsertData & "*" & Chr(34)
    
    rs.FindFirst strData
        
     If rs.NoMatch Then
        Label4.Caption = "No record found! " & strInsertData
        SQLCode = "INSERT INTO FoldersNotFound ( Folder ) SELECT " & Chr(34) & strInsertData & Chr(34) & " AS Expr1;"
        Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic
    '  close it this way
        Set Rs1 = Nothing
        Set Conn2 = Nothing
        Me.[FoldersNotFound subform].Requery
     Else
        Me.[Company subform].Form.Bookmark = rs.Bookmark
        'set the field "folder location" in the Company sub form
        rs.Edit
        Me.[Company subform]!FolderLocation = Me.List2.Value
        rs.Update
        rs.Close
    End If
            

Exit_List2_Click:
Exit Sub

Err_List2_Click:
    Select Case Err.Number
        Case 3020
            ' Update or CancelUpdate without Addnew or Edit
            
        Case Else
            MsgBox "Error # " & Err.Number & "  " & Err.Description, vbInformation, "In sub List2_Click"
            
    End Select

    Resume Exit_List2_Click

DougP
< I Built one

http://www.robots2000.com

 

[TheAceMan1]

How are ya DougP . . .

[ol][li]As far as your redlined error is concerned, shouldn't
[red]Me.[Company subform]!FolderLocation = Me.List2.Value[/red]
be
Me.[Company subform][blue].Form[/blue]!FolderLocation = Me.List2.Value[/li]
[li]Note that action queries ([blue]append[/blue] in your case) [red]return no records[/red]. So [blue]I see no use of rs1![/blue] [/li]
[li]The type of object for rs isn't identified: [blue]ADODB.Recordset[/blue] or [blue]DAO.Recordset.[/blue][/li]
[li]Since your using a [blue]recordsetclone[/blue] I see no need for ADO!.[/li]
[li]A better event to use for List2 would be the [blue]After Update[/blue] event. If you do use it, be sure to remove the code in the [blue]On Click[/blue] event.[/li][/ol]
And the code (untested!)

[blue]On Error GoTo GotErr
   
   Dim db As DAO.Database, rs As DAO.Recordset, SQL As String, sqlDat As String
   Dim sfCom As Form, LBx As ListBox, DQ As String
   
   Set db = CurrentDb
   Set LBx = Me!List2
   Set sfCom = [Company subform].Form
   Set rs = sfCom.RecordsetClone
   DQ = """"
   
   sqlDat = Switch(Len(LBx) < 15, LBx, 0 = 0, Left(LBx, 15))
   rs.FindFirst "[Name] Like " & DQ & sqlDat & "*" & DQ
       
   If rs.NoMatch Then
      Me!Label4.Caption = "No record found! " & sqlDat
      
      SQL = "INSERT INTO [FoldersNotFound] (Folder) " & _
            "SELECT " & DQ & sqlDat & DQ & ";"
      db.Execute SQL, dbFailOnError
      
      [FoldersNotFound subform].Form.Requery
   Else
      sfCom.Bookmark = rs.Bookmark
      rs.Edit
      sfCom!FolderLocation = LBx
      rs.Update
   End If

SeeYa:
   Set rs = Nothing
   Set db = Nothing
   Set sfCom = Nothing
   Set LBx = Nothing
   Exit Sub

GotErr:
   Select Case Err.Number
      Case 3020
         ' Update or CancelUpdate without Addnew or Edit
      Case Else
         MsgBox "Error # " & Err.Number & "  " & Err.Description, vbInformation, _
                "In sub List2_Click"
   End Select
   
   Resume SeeYa[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]