How to get around special characters??? 3

[steve728]

I'm having a problem updating the storage.memo field with the contents of Forms![Storage Data Entry]!TxtMemo) because of special chars within it (i.e. ##, ' + *, etc.) Please help me solve this issue.

If .TxtPurse.Value > Me.TxtPrior Then
strMemo = "-" & Now() & " Increased pocket from $" & .TxtPrior.Value & " to " _
& .TxtPurse.Value & vbCrLf & Nz(Trim(Forms![Storage Data Entry]!TxtMemo), " ")
Else
strMemo = "-" & Now() & " Decreased pocket from $" & .TxtPrior.Value & " to " _
& .TxtPurse.Value & vbCrLf & Nz(Trim(Forms![Storage Data Entry]!TxtMemo), " ")
'Replace(strMemo, "'", "''")
End If
adoconnection.Execute "UPDATE storage SET purse = " & .TxtPurse.Value _
& ", storage.memo = '" & strMemo & "' WHERE Trim(unitnum) = '" & Trim(.TxtUNITNUM.Value) & "';", , 129

 

[JoeAtWork]

The only characters you might have trouble with are quotes (double or single). The way to escape them is to double them up, example:

strMemo = "''cake'' say I"

The other characters you mentioned you shouldn't have a problem with.

 

[steve728]

This is great but please insert the corrections into my code so that I can see where to and how to apply this. Please.

 

[Remou]

How about you insert the code, steve728, and we correct any mistakes? I notice that you have commented out the replace statement that was designed to do just what JoeAtWork says.

 

[danvlas]

If it's Access, create a parameter query
If it's SQL, create a stored procedure
Use the Command object and Parameters.

Set cmd = New ADODB.Command
With cmd
.ActiveConnection=adoConnection
.CommandText="NameOfQuery"
.ComandType=adStoredProc
.Parameters.Refresh
.Parameters("@unitNum")=Trim(unitnum)
.Parameters("@Memo")=Forms![Storage Data Entry]!TxtMemo
.Execute
End With

No problem with any special characters anymore.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[steve728]

I'm using Access 2002 so here goes:

.TxtPurse.Value > Me.TxtPrior Then
strMemo = "-" & Now() & " Increased pocket from $" & .TxtPrior.Value & " to " _
& .TxtPurse.Value & vbCrLf & Nz(Trim(Forms![Storage Data Entry]!TxtMemo), " ")


adoconnection.Execute "UPDATE storage SET purse = " & .TxtPurse.Value _
& ", storage.memo = '" & Replace(strMemo, "'", "''")
& "' WHERE Trim(unitnum) = '" & Trim(.TxtUNITNUM.Value) & "';", , 129

 

[danvlas]

The parameter query should be something like:

PARAMETERS [@purse] Text, [@memo] Text, [@unitnum] Text ( 255 );
UPDATE storage SET purse = [@purse], storage.memo = [@memo]
WHERE trim(unitnum)=[@unitnum;

Save it to the database.

Then, instead of your 'adoconnection.execute' line, insert:

Dim cmd As New ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection=adoConnection
.CommandText="NameOfQueryAsYouSavedIt"
.ComandType=adStoredProc
.Parameters.Refresh
.Parameters("@unitNum")=Trim(unitnum)
.Parameters("@purse")=Me.TxtPurse 'overlooked this one last time
.Parameters("@memo")=Forms![Storage Data Entry]!TxtMemo
.Execute
End With

That should do.



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[steve728]

I've given you all stars for sharing your weekends with me. I Don't know what I would do whithout all my cyber friends out there. Probably punt.

Steve728

 

[ProgramError]

Also take a look at the Replace() method.

Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!