Hi Group;
I have five databases where the code below works great. However
on one of the five I get the error message on the line indicated below. Why
is the same Sub_routine working in four, but not the fifth?
Dim db As Database
Dim rst As Recordset
Dim LeftLen As Byte
Dim IX As Byte
Dim wrkStr As String
Dim wrkText As String
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT TEXT FROM SORTED1")
Do Until rst.EOF
wrkText = ""
IX = 1
If Right(rst!Text, 2) = ", " Then
rst.Edit ' Error Mesage on
this line?
LeftLen = Len(rst!Text) - 2
wrkStr = rst!Text
Do
wrkText = wrkText & Mid(wrkStr, IX, 1)
IX = IX + 1
Loop Until IX > LeftLen
rst!Text = wrkText
rst.Update
End If
rst.MoveNext
Loop
rst.Close
Set rst = db.OpenRecordset("SELECT TEXT FROM SORTED1")
Do Until rst.EOF
wrkText = ""
IX = 1
If Right(rst!Text, 3) = ", " Then
rst.Edit
LeftLen = Len(rst!Text) - 3
wrkStr = rst!Text
Do
wrkText = wrkText & Mid(wrkStr, IX, 1)
IX = IX + 1
Loop Until IX > LeftLen
rst!Text = wrkText
rst.Update
End If
rst.MoveNext
Loop
Thanks in Advance J.Q.
Bob Quintal - 14 Jul 2007 15:58 GMT
=?Utf-8?B?Sm9obiBRdWlubg==?=
<JohnQuinn@discussions.microsoft.com> wrote in
news:AB4D3664-F559-41F5-A8B4-529073C657B0@microsoft.com:
> Hi Group;
> I have five databases where the code below works
[quoted text clipped - 3 lines]
> but not the fifth?
> Set rst = db.OpenRecordset("SELECT TEXT FROM SORTED1")
> If Right(rst!Text, 2) = ", " Then
> rst.Edit '
> Error Mesage on
> this line?
> Thanks in Advance J.Q.
Is the source of your select statement, SORTED1 a query that is
for some reason, read-only?

Signature
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
Ken Snell (MVP) - 14 Jul 2007 16:55 GMT
Probably a references issue. Both ADODB and DAO libraries have a Recordset
object, and you need to disambiguate the Dim statement accordingly:
Dim rst As DAO.Recordset

Signature
Ken Snell
<MS ACCESS MVP>
> Hi Group;
> I have five databases where the code below works great.
[quoted text clipped - 54 lines]
>
> Thanks in Advance J.Q.
John Quinn - 14 Jul 2007 17:38 GMT
Ken;
Thanks, it worked! However I did not have to Dimension the Database
and Recordset as DAO in the other databases. Do you know why on this one?
A little confised and thanks again
J.Q.
> Probably a references issue. Both ADODB and DAO libraries have a Recordset
> object, and you need to disambiguate the Dim statement accordingly:
[quoted text clipped - 59 lines]
> >
> > Thanks in Advance J.Q.
Marshall Barton - 14 Jul 2007 18:36 GMT
> Thanks, it worked! However I did not have to Dimension the Database
>and Recordset as DAO in the other databases. Do you know why on this one?
Because you removed the reference to the ADO library the
other databases. Always check the references list to make
sure you are not referencing a library unless it is
absolutely necessary.

Signature
Marsh
MVP [MS Access]