Using Replace in Existing Record

[dccjr3927]

This piece of code is meant to open the most recent recordset and replace certain characters. It is throwing an Invalid Argument error on the Set rs statement.


Variables are defined as follows:

Dim db As Object, rs As Object
Set db = CurrentDb()


strSingle = Chr(39)
strDouble = Chr(34)
strRepSingle = "^^^^"
strRepDouble = "~~~~"



 Set rs = db.OpenRecordset("DefectEvents", dbOpenDynaset)
        With rs
            .MoveLast
            .Edit
            !CustomerName = Replace(!Employee, strRepSingle, strSingle): !CustomerName = Replace(!CustomerName, strRepDouble, strDouble)
            !CallNotes = Replace(!Comments, strRepSingle, strSingle): !CallNotes = Replace(!CallNotes, strRepDouble, strDouble)
            !DefectNotes = Replace(!Description, strRepSingle, strSingle): !DefectNotes = Replace(!DefectNotes, strRepDouble, strDouble)
            .Update
        End With
        Set rs = Nothing
        Set db = Nothing

Any help would be greatly appreciated.

[orange]

Can you show us the table structure/fields for table "DefectEvents"?

[dccjr3927]

UPC	Short Text
Category	Short Text
Employee	Short Text
Marketplace	Short Text
Defect	Short Text
CustomerName	Short Text
OrderNumber	Short Text
Status	Short Text
DefectNotes	Long Text
CallInit	Short Text
CallNotes	Short Text
Date_Time	Date/Time

[orange]

Where are you getting Comments and Description?

The order of records in your recordset may not be what you think. You might want to use a query and an Order By with Date_time to ensure the order you want.

[dccjr3927]

Apologies for the Comments and Description. That has been corrected with no change in error. Here is the entire Sub:

Private Sub cmdSaveDefect_Click()
Dim oneControl As Object
Dim str As String, InsStr As String
Dim strSingle As String, strDouble As String, strRepSingle As String, strRepDouble As String
Dim RecordSQL As String, cenSQL As String, endSQL As String
Dim iAttachSQL As String
Dim db As Object, rs As Object
Dim i As Integer
Dim strListSource As String
Dim strFileName As String
Dim dteDateTime As Date


Set db = CurrentDb()


strSingle = Chr(39)
strDouble = Chr(34)
strRepSingle = "^^^^"
strRepDouble = "~~~~"
cenSQL = "','"
endSQL = "');"
dteDateTime = Now()


' Check unique indentifier fields
If IsNull(Me.cbxEmployee) Or IsNull(Me.tbxProductUPC) Or IsNull(Me.cbxDefectCat) Or IsNull(Me.cbxDefect) Or IsNull(Me.cbxMarketplace) Or IsNull(Me.tbxCustName) Or IsNull(Me.tbxOrderNum) Or IsNull(Me.cbxStatus) = True Then
    MsgBox ("All * fields are required.")
    Exit Sub
Else
End If


'Removes quotes and replaces with safe symbols
    
    strCustName = Replace(tbxCustName, strSingle, strRepSingle): strCustName = Replace(strDesc, strDouble, strRepDouble)
    strDefectNotes = Replace(tbxDefectNotes, strSingle, strRepSingle): strDefectNotes = Replace(strNotes, strDouble, strRepDouble)
    strCallNotes = Replace(tbxCallNotes, strSingle, strRepSingle): strCallNotes = Replace(strImmedAct, strDouble, strRepDouble)


'Inserts record into the table
        RecordSQL = ""
        RecordSQL = "INSERT INTO DefectEvents (UPC, Category, Employee, Marketplace, Defect, CustomerName, OrderNumber, Status, CallNotes, DefectNotes, CallInit, Date_Time)" _
            & "VALUES('" & Me.cbxEmployee.Value & cenSQL & Me.cbxDefectCat.Value & cenSQL & Me.cbxEmployee.Value & cenSQL & Me.cbxMarketplace.Value & cenSQL & Me.cbxDefect.Value & cenSQL & Me.tbxCustName.Value & cenSQL & Me.tbxOrderNum.Value & cenSQL & Me.cbxStatus & cenSQL & Me.tbxCallNotes & cenSQL & Me.tbxDefectNotes & cenSQL & Me.cbxInitiatedBy & cenSQL & dteDateTime & endSQL
        CurrentDb.Execute RecordSQL
            If Len(Me.lstAttach & vbNullString) = 0 Then
                For i = 1 To Me.lstAttach.ListCount
                   strFileName = "\\ez-wse\company\Engraving\Databases-DO NOT MOVE\DefectAttach\" & Me.tbxOrderNum.Value & "-" & i
                   strListSource = Me.lstAttach.ListIndex = (i)
                   iAttachSQL = "INSERT INTO AttachTable (OrderNo, FilePath" & (i) & ")" _
                   & "VALUES ('" & Me.tbxOrderNum & cenSQL & Me.lstAttach.ListIndex = (i) & endSQL
                   CurrentDb.Execute iAttachSQL
                   FileCopy strListSource, strFileName
                Next i
            Else
            End If
    
    'Opens recordset from table for last entry and changes the safe symbols back to quotes
        Set rs = db.OpenRecordset("DefectEvents", dbOpenDynaset)
        With rs
            .MoveLast
            .Edit
            !CustomerName = Replace(!Employee, strRepSingle, strSingle): !CustomerName = Replace(!CustomerName, strRepDouble, strDouble)
            !CallNotes = Replace(!CallNotes, strRepSingle, strSingle): !CallNotes = Replace(!CallNotes, strRepDouble, strDouble)
            !DefectNotes = Replace(!DefectNotes, strRepSingle, strSingle): !DefectNotes = Replace(!DefectNotes, strRepDouble, strDouble)
            .Update
        End With
        Set rs = Nothing
        Set db = Nothing


'Clears form for more entries or returns to the Home page
    If MsgBox("Would you like to enter another record?", vbYesNo) = vbYes Then
        For Each oneControl In frmDefectEvent.Controls
            Select Case TypeName(oneControl)
                Case "TextBox"
                oneControl.Value = vbNullString
                Case "ComboBox"
                oneControl.Index = Null
            End Select
        Next oneControl
    Else
        DoCmd.Close acForm, Me.Name
        DoCmd.OpenForm "frmHome", acNormal, "", "", , acNormal
    End If
End Sub

[Micron]

What is the reason for declaring db and rs as objects rather than database and recordset? Furthermore, they should be explicitly typed as DAO or ADO. Not sure Access is liking the rs being an object, which being user defined, doesn't have explicit properties that you need to use.

[davegri]

Do you really want to store all those values in table DefectEvents wrapped in quotes? That's going to vastly complicate handling of those fields in any later code.
I would replace all the Chr(34) and chr(39) with vbnullstring (which is "") early on and be done with it.

[dccjr3927]

The quote substitution and replacement was a simple way to deal with Customer Names or note information that would contain single or double quotes (eg. Mr. O'Malley or specific text on a product.)

I redefined db and rs ad DAO.database and DAO.Recordset respectively. And solved the error. Thank you for the suggestions.

[Micron]

I redefined db and rs ad DAO.database and DAO.Recordset respectively. And solved the error.

Are these 2 things related? That was the reason for the error you posted?
Have to admit I'm a bit bewildered and can't see how you'd make things any better by substituting tilde's and carets for quotation marks. Plus it looks like an awful lot of code that could be accomplished by using an Update query on the table fields with the Replace function. It is also ill advised to store attachments in an Access db even though you can.