Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Issues with the comma sign in textboxes ( ' ) 3

Status
Not open for further replies.
newguy86

newguy86

Technical User
May 19, 2008
226
US
Here is the situation. I have implemented a logging function into my database to create a historic record of all changes made to records in a table. The issue is that every time someone puts the comma sign " ' " into a textbox to signify the plural tense of a word, they get an error message stating syntax issues. I have determined that the cause of this issue is because my SQL statement uses the " ' " sign to seperate the different fields of the log and therefore the system is splitting my text field into multiple fields and as a result I am using too many fields in my SQL statement.

Code: 
Public Function WriteAudit(frm As Form, lngID As Long) As Boolean

    Dim ctlC As Control
    Dim fldF As Field
    Dim strSQL As String
    Dim bOK As Boolean
    Dim strFormID As String
    
    bOK = False
    strFormID = "05"
    DoCmd.SetWarnings False
    
    Set ctlC = frm.ActiveControl
    
    If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
        If Not IsNull(ctlC.Value) Then
            strSQL = "INSERT INTO tblDbLog (RecordID, UserID, DbFrmID, ActivityID, FieldChanged, FieldChangedFrom, FieldChangedTo, DateofHit) " & _
                   "VALUES ('" & Form_Complaints.Incident_Number & "', " & _
                   "'" & GetUserName_TSB() & "', " & _
                   "'" & "04-" & strFormID & "', " & _
                   "'" & "01" & "', " & _
                   "'" & ctlC.Name & "', " & _
                   "'" & ctlC.OldValue & "', " & _
                   "'" & ctlC.Value & "', " & _
                   "'" & Now() & "')"
            'Debug.Print strSQL
            DoCmd.RunSQL strSQL
        End If
    End If
    
    WriteAudit = bOK
      
End Function

If anyone can help me modify my code so that we can use the comma sign " ' " inside the textbox again, but still be able to accomplish our goal that would be greatly appreciated.

Travis

"Why would I want to learn programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait!
 
Sort by date Sort by votes
The issue is single quotes. Commas are [red],[/red]. You can try replace all single quotes with two single quotes like:
Code: 
                   "'" & Replace(ctlC.OldValue,"'","''") & "', " & _
                   "'" & Replace(ctlC.Value,"'","''") & "', " & _

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Your right! I meant to say an apostraphe(a.k.a. single quotes).

So then should my code look something like this:

Code: 
Public Function WriteAudit(frm As Form, lngID As Long) As Boolean

    Dim ctlC As Control
    Dim fldF As Field
    Dim strSQL As String
    Dim bOK As Boolean
    Dim strFormID As String
    
    bOK = False
    strFormID = "05"
    DoCmd.SetWarnings False
    
    Set ctlC = frm.ActiveControl
    
    If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
        If Not IsNull(ctlC.Value) Then
            strSQL = "INSERT INTO tblDbLog (RecordID, UserID, DbFrmID, ActivityID, FieldChanged, FieldChangedFrom, FieldChangedTo, DateofHit) " & _
                   "VALUES ('" & Form_Complaints.Incident_Number & "', " & _
                   "'" & GetUserName_TSB() & "', " & _
                   "'" & "04-" & strFormID & "', " & _
                   "'" & "01" & "', " & _
                   "'" & ctlC.Name & "', " & _
                   "'" & Replace(ctlC.OldValue,"'","''") & "', " & _
                   "'" & Replace(ctlC.Value,"'","''") & "', " & _
                   "'" & ctlC.OldValue & "', " & _
                   "'" & ctlC.Value & "', " & _
                   "'" & Now() & "')"
            'Debug.Print strSQL
            DoCmd.RunSQL strSQL
        End If
    End If
    
    WriteAudit = bOK
      
End Function


Travis

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!
 
Upvote 0 Downvote
I tried it and I get this messagebox:

"Number of query values and destination fields are not the same."

Travis

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!
 
Upvote 0 Downvote
You should have replaced a section of your code with my code rather than just inserted my code into yours.
Code: 
Public Function WriteAudit(frm As Form, lngID As Long) As Boolean

    Dim ctlC As Control
    Dim fldF As Field
    Dim strSQL As String
    Dim bOK As Boolean
    Dim strFormID As String
    
    bOK = False
    strFormID = "05"
    DoCmd.SetWarnings False
    
    Set ctlC = frm.ActiveControl
    
    If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
        If Not IsNull(ctlC.Value) Then
            strSQL = "INSERT INTO tblDbLog (RecordID, UserID, DbFrmID, ActivityID, FieldChanged, FieldChangedFrom, FieldChangedTo, DateofHit) " & _
                   "VALUES ('" & Form_Complaints.Incident_Number & "', " & _
                   "'" & GetUserName_TSB() & "', " & _
                   "'" & "04-" & strFormID & "', " & _
                   "'" & "01" & "', " & _
                   "'" & ctlC.Name & "', " & _
                   "'" & Replace(ctlC.OldValue,"'","''") & "', " & _
                   "'" & Replace(ctlC.Value,"'","''") & "', " & _
                   "'" & Now() & "')"
            Debug.Print strSQL
            DoCmd.RunSQL strSQL
        End If
    End If
    
    WriteAudit = bOK
      
End Function

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
To me, it is a lot easier to use parameters.

I would not have Now() as a text field, and I would format it, to ensure that regional settings do not mess up your dates. This would not be necessary with parameters.



 
Upvote 0 Downvote
Dhookom,
That is what my question was but regardless I tried it and it worked. Thank you.

Remou,
Can you illustrate what you mean by using parameters?



Travis

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!
 
Upvote 0 Downvote
Ok.

Code: 
    'Process data
    dteTimeRef = Now()

    strSQL="PARAMETERS txtCompany Text(150), " _
    & "txtStatus Text(50), " _
    & "dteCRDate DateTime; " _
    & "INSERT INTO tblCompanies ([Company], " _
    & "Status, CrDate ) " _
    & "VALUES ( txtCompany, txtStatus, dteCRDate )"

    Set qdf = db.CreateQueryDef("", strSQLText)
    
    qdf.Parameters!txtCompany = Trim(frm!txtCompany)
    qdf.Parameters!txtStatus = "Pending" 'Default value
    qdf.Parameters!dteCrDate = dteTimeRef

    qdf.ReturnsRecords = False
    
    qdf.Execute dbFailOnError
    
    intResult = qdf.RecordsAffected


 
Upvote 0 Downvote
We seem to have some more issues with this code. Now whenever I put a value into a textbox I get an error message that states invalid use of null.

Here is my updated code for those who need it:
Code: 
Public Function WriteAudit(frm As Form, lngID As Long) As Boolean
On Error GoTo err_WriteAudit

    Dim ctlC As Control
    Dim fldF As Field
    Dim strSQL As String
    Dim bOK As Boolean
    Dim strFormID As String
    
    bOK = False
    strFormID = "05"
    DoCmd.SetWarnings False
    
    Set ctlC = frm.ActiveControl
    
    ' For each control.
    'For Each ctlC In frm.Controls
        'If TypeOf ctlC Is TextBox Or TypeOf ctlC Is ComboBox Then
    If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
        If Not IsNull(ctlC.Value) Then
            strSQL = "INSERT INTO tblDbLog (RecordID, UserID, DbFrmID, ActivityID, FieldChanged, FieldChangedFrom, FieldChangedTo, DateofHit) " & _
                   "VALUES ('" & Form_Complaints.Incident_Number & "', " & _
                   "'" & GetUserName_TSB() & "', " & _
                   "'" & "04-" & strFormID & "', " & _
                   "'" & "01" & "', " & _
                   "'" & ctlC.Name & "', " & _
                   "'" & Replace(ctlC.OldValue, "'", "''") & "', " & _
                   "'" & Replace(ctlC.Value, "'", "''") & "', " & _
                   "'" & Now() & "')"
            'Debug.Print strSQL
            DoCmd.RunSQL strSQL
        End If
    End If
    
    WriteAudit = bOK
    
exit_WriteAudit:
    DoCmd.SetWarnings True
    Exit Function
    
err_WriteAudit:
    MsgBox Err.Description
    Resume exit_WriteAudit
    
End Function

If anyone can provide any assistance it would be greatly appreciated. In the meantime I will try out the Parameters code mentioned above and see if that works. Also if anyone can elaborate on the Parameters code or post an updated version of that code so that it will work with the setup I have already built, it would also be appreciated.

Travis

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!
 
Upvote 0 Downvote
Code: 
Replace this:
"'" & Replace(ctlC.OldValue, "'", "''") & "', " & _
with this:
"'" & Replace(ctlC.OldValue [!]& ""[/!], "'", "''") & "', " & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Sorry, I put some messy code in there.

Here is the cleaner version:
Code: 
Public Function WriteAudit(frm As Form, lngID As Long) As Boolean

    Dim ctlC As Control
    Dim fldF As Field
    Dim strSQL As String
    Dim bOK As Boolean
    Dim strFormID As String
    
    bOK = False
    strFormID = "05"
    DoCmd.SetWarnings False
    
    Set ctlC = frm.ActiveControl
    
    If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
        If Not IsNull(ctlC.Value) Then
            strSQL = "INSERT INTO tblDbLog (RecordID, UserID, DbFrmID, ActivityID, FieldChanged, FieldChangedFrom, FieldChangedTo, DateofHit) " & _
                   "VALUES ('" & Form_Complaints.Incident_Number & "', " & _
                   "'" & GetUserName_TSB() & "', " & _
                   "'" & "04-" & strFormID & "', " & _
                   "'" & "01" & "', " & _
                   "'" & ctlC.Name & "', " & _
                   "'" & Replace(ctlC.OldValue, "'", "''") & "', " & _
                   "'" & Replace(ctlC.Value, "'", "''") & "', " & _
                   "'" & Now() & "')"
            'Debug.Print strSQL
            DoCmd.RunSQL strSQL
        End If
    End If
    
    WriteAudit = bOK
        
End Function

Thanks

Travis

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!
 
Upvote 0 Downvote
As usual PHV, it worked like a charm. Thank you.

Travis

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Remove everything including the comma from the last comma of a string. 1
Replies
10
Views
1K
Andrzejek
Andrzejek
M
  • Locked
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
4K
SoftwareRT
SoftwareRT
breezett93
  • Locked
  • Question
Converting from 32bit to 64 bit. After converting, database takes significantly longer to open 1
Replies
14
Views
2K
dhookom
dhookom
Dan Stoica
  • Locked
  • Question
Problems with Foxpro dsnless linked tables
Replies
0
Views
617
Dan Stoica
Dan Stoica
Drago1920
  • Locked
  • Question
Finding the lowest date
Replies
2
Views
601
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top