Apostraphy

Some sample data:
Field1     Fieldname     FieldEntry
3     SerAdvID     ABBAWAY PTY LTD
4     SerAdvID     JONES, PETER
5     SerAdvID     ABN AMRO MORGANS LTD
6     SerAdvID     ADVISER INVEST SER
7       SerAdvID        O'Neil <------falls over here

You need to use double-apostrophes; the easiest way to do that is to create a SQLQuote function:

Function sqlQuote(vsInput) As String
   ' / quotes out ' in input
   sqlQuote = Replace(vsInput & "", "'", "''")
 
End Function

Then, in your code:
CurrentDb.Execute "INSERT INTO ExcludedTable (Field1,fieldname,FieldEntry) VALUES ('" _
& SQLQuote(Me.lstSource.ItemData(x)) & "' ,  '" & SQLQuote(Me.txtReqField.Value) & "','" & SQLQuote(Me.lstSource.Column(1, x)) & "');"

Hope that helps,

g.

Some sample data:
Field1     Fieldname     FieldEntry
3     SerAdvID     ABBAWAY PTY LTD
4     SerAdvID     JONES, PETER
5     SerAdvID     ABN AMRO MORGANS LTD
6     SerAdvID     ADVISER INVEST SER
7       SerAdvID        O'Neil <------falls over here

Works well in Access 2000 however I am using Access 97 and it doesn't like the 'Replace' function. Am I missing a reference or is there a similar function in Acc 97?

Works well in Access 2000 however I am using Access 97 and it doesn't like the 'Replace' function. Am I missing a reference or is there a similar function in Acc 97?

Try enclosing fields within square brackets.

CurrentDb.Execute "INSERT INTO ExcludedTable ([Field1],[fieldname],[FieldEntry]) VALUES ('" _
& Me.lstSource.ItemData(x) & "' ,  '" & Me.txtReqField.Value & "','" & Me.lstSource.Column(1, x) & "');"

Alanwarren - your suggestion doesn't work unfortunately.

If shanesuebsah read this, he'd probably say:

Use chr(34):

CurrentDb.Execute "INSERT INTO ExcludedTable (Field1,fieldname,FieldEntry) VALUES (" & chr(34)_
& Me.lstSource.ItemData(x) & chr(34) & " ,  " & chr(34) & Me.txtReqField.Value & chr(34) & "," & chr(34) & Me.lstSource.Column(1, x) & chr(34) & ");"

I just read it :) Yup, that's what I'd say <g>

You can also use this alternative for the Replace function:
http://www.mvps.org/access/strings/str0004.htm

(note, please don't accept this comment as an answer, I am just clarifying the other posts)

Alanwarren - your suggestion doesn't work unfortunately.

This works
You are using an aposrophe as a string delimiter
When a string contains astring delimiter, you need to double up the string delimiter.

Dim sField1 As String
Dim sfieldname As String
Dim sFieldEntry As String
Dim sql As String
sField1 = "'" & Replace(Me.lstSource.ItemData(x), "'", "''") & "'"
sfieldname = "'" & Replace(Me.txtReqField.Value, "'", "''") & "'"
sFieldEntry = "'" & Replace(Me.lstSource.Column(1, x), "'", "''") & "'"
sql = "INSERT INTO [Excluded Items] (Field1,fieldname,FieldEntry) "
sql = sql & "VALUES (" & sField1 & "," & sfieldname & "," & sFieldEntry & ")"
CurrentDb.Execute sql



Alan
http://www.cashoz.com

In the top left of this web page, under your username, is a link for "Reload This Question".  That will refresh the page without posting your last comment again.

For Access 97

Function Replace(pStr As String, pSrch As String, pRep As String) As String 'replaces all occurrence of pSrch (case sensitive) with pRep in pStr 'returns modified string

   Dim StartPos As Integer, LeftPos As Integer, RightPos As Integer
   Dim NewStr As String, LeftStr As String, RightStr As String

   ReplaceStr = pStr                                       'default returns
unmodified
   NewStr = pStr                                           'Modified string
   StartPos = 1                                            'Start search at
1st char
   LeftPos = InStr(StartPos, NewStr, pSrch, 0)             'pos of pSrch in
NewStr (binary)
   Do While LeftPos > 0
      RightPos = LeftPos + Len(pSrch) - 1                 'right pos of
pSrch in NewStr
      LeftStr = Left(NewStr, LeftPos - 1)                 'left of NewStr up
to pSrch
      RightStr = Right(NewStr, Len(NewStr) - RightPos)    'right of pStr
after pSrch
      NewStr = LeftStr & pRep & RightStr                  'modified string
      StartPos = Len(LeftStr) + Len(pRep) + 1             'Start search
after replacement
      LeftPos = InStr(StartPos, NewStr, pSrch, 0)         'pos of pSrch in
NewStr
   Loop
   Replace = NewStr
   Exit Function

End Function



Alan
http://www.cashoz.com 

Holy cow!  It's getting more complex by the minute... just stick with the simple (and much faster) version that I posted a while back (03/13/2003 07:07PM PST).

P.S. The results of your last paste, alanwarren, have some serious line-wrapping problems.

TheAmigo

The line wrap comes from your email program or the web page Here's a version that should survive even the narrowest email editor:


Function _
Replace(pStr _
As _
String, _
pSrch _
As _
String, _
pRep _
As _
String) _
As _
String
Dim _
StartPos _
As _
Integer, _
LeftPos _
As _
Integer, _
RightPos _
As _
Integer
Dim _
NewStr _
As _
String, _
LeftStr _
As _
String, _
RightStr _
As _
String
Replace _
= _
pStr
NewStr _
= _
pStr
StartPos _
= _
1
LeftPos _
= _
InStr(StartPos, _
NewStr, _
pSrch, _
0)
Do _
While _
LeftPos _
> _
0
RightPos _
= _
LeftPos _
+ _
Len(pSrch) _
- _
1
LeftStr _
= _
Left(NewStr, _
LeftPos _
- _
1)
RightStr _
= _
Right(NewStr, _
Len(NewStr) _
- _
RightPos)
NewStr _
= _
LeftStr _
& _
pRep _
& _
RightStr
StartPos _
= _
Len(LeftStr) _
+ _
Len(pRep) _
+ _
1
LeftPos _
= _
InStr(StartPos, _
NewStr, _
pSrch, _
0)
Loop
Replace _
= _
NewStr
Exit _
Function
End _
Function


Adios Amigo

mksilk
Sorry about the linwraps last time
All jokes aside (must be Friday), this will do what you want.

Paste this function into a module
It is a multi purpose function that emulates the Access2k Replace funcion. Not just apostrophe's


Function ReplaceStr(pStr As String, pSrch As String, pRep As String) As String
'replaces all occurrence of pSrch (case sensitive) with pRep in pStr
'returns modified string

   Dim StartPos As Integer, LeftPos As Integer, RightPos As Integer
   Dim NewStr As String, LeftStr As String, RightStr As String

   ReplaceStr = pStr   'default returns unmodified
   NewStr = pStr       'Modified string
   StartPos = 1        'Start search at
1 st char
   LeftPos = InStr(StartPos, NewStr, pSrch, 0) 'pos of pSrch in
NewStr (binary)
   Do While LeftPos > 0
      RightPos = LeftPos + Len(pSrch) - 1 'right pos of pSrch in NewStr
      LeftStr = Left(NewStr, LeftPos - 1) 'left of NewStr up to pSrch
      RightStr = Right(NewStr, Len(NewStr) - RightPos) 'right of pStr
after pSrch
      NewStr = LeftStr & pRep & RightStr 'modified string
      StartPos = Len(LeftStr) + Len(pRep) + 1 'Start search
after replacement
      LeftPos = InStr(StartPos, NewStr, pSrch, 0) 'pos of pSrch in
NewStr
   Loop
   ReplaceStr = NewStr
   Exit Function

End Function

Step...2

adapt Your code like this:

Private Sub Command4_Click()
Dim x As Integer

'I havn't got a clue what the value of x is,
'I assume your setting it to a loop on listcount.
'So I set x = 1
x = 1


'If Nothing selected exit sub
If IsNull(Me.lstSource.Column(1)) Then: Exit Sub

Dim sField1 As String
Dim sfieldname As String
Dim sFieldEntry As String
Dim sql As String

sField1 = "'" & ReplaceStr(Me.lstSource.ItemData(x), "'", "''") & "'"
sfieldname = "'" & ReplaceStr(Me.txtReqField.Value, "'", "''") & "'"
sFieldEntry = "'" & ReplaceStr(Me.lstSource.Column(1, x), "'", "''") & "'"
sql = "INSERT INTO [Excluded Items] (Field1,fieldname,FieldEntry) "
sql = sql & "VALUES (" & sField1 & "," & sfieldname & "," & sFieldEntry & ")"

CurrentDb.Execute sql

End Sub



I have changed the name of the function to avoid ambiguity when your application is upgraded to win2k


Hope this helps
alan@cashoz.com

Ghis68's answer seems to work, I will trying it this weekend and get back to you on Monday with my answer. Thanks for the input from all of you.

chr(34) - doesn't help?

Hi mksilk,
This question has been abandoned and needs to be finalized (101 days since last comment).

   You can accept an answer, split the points, or get a refund. Go to
   http://www.cityofangels.com/Experts/Closing.htm for information and options.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to Ghis68
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

1William
EE Cleanup Volunteer