readonly error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using this:
Do While Not rs1.EOF
to loop through a table and find a string. When I find the string, I want
to delete it, but the rs1.edit give me a readonly error.
Is it possible to make changes to rs1 while looping through it like this?
Thanks,
ck
 
I'm using this:
Do While Not rs1.EOF
to loop through a table and find a string. When I find the string, I want
to delete it, but the rs1.edit give me a readonly error.
Is it possible to make changes to rs1 while looping through it like this?
Thanks,
ck
Click to expand...

not on a forward only recordset, no. They are read-only. Post your
code. If you open a recordset with dbOpenTable, then you can update
the records.
 
Or DbOpenDynaset

Pieter

not on a forward only recordset, no. They are read-only. Post your
code. If you open a recordset with dbOpenTable, then you can update
the records.
Click to expand...
 
Below is my code. I don't understand how to open the table as being
editable...

Function Remove_Brackets()
Dim rs1 As DAO.Recordset
Dim strPre As String
Dim strPost As String

Set rs1 = CurrentDb.OpenRecordset("QueryName")


Do While Not rs1.EOF

If InStr(1, rs1!Name, "<") Then 'checking for <
If InStr(1, rs1!Name, ">") Then 'checking for >
'REMOVE ALL BETWEEN BRACKETS

strPre = Left(rs1!Name, InStr(1, rs1!Name, "<") - 1)
strPost = Right(rs1!Name, Len(rs1!Name) - InStr(1, rs1!Name, ">"))
rs1.Edit
rs1!Name = strPre & strPost
rs1.Update
'MsgBox strPre & strPost
End If
End If
rs1.MoveNext
Loop
End Function
 
Hi Charlie,

as Pieter suggested open your recordset as a dynaset in this way:

Set rs1 = CurrentDb.OpenRecordset("QueryName", dbopendynaset)

HTH Paolo
 
I tried this, but it still gives me an error when it gets to the rs1.edit
line and says it's read only. Below is the exact code I tried... I don't
understand what's wrong.
Any suggestions?
thanks,
ck


Function Remove_Brackets_Test()
Dim rs1 As DAO.Recordset
Dim strPre As String
Dim strPost As String

'Set rs1 = CurrentDb.OpenRecordset("QueryName")
Set rs1 = CurrentDb.OpenRecordset("QueryName", dbOpenDynaset)


Do While Not rs1.EOF

If InStr(1, rs1!Name, "<") Then 'checking for <
If InStr(1, rs1!Name, ">") Then 'checking for >
'REMOVE ALL BETWEEN BRACKETS

strPre = Left(rs1!Name, InStr(1, rs1!Name, "<") - 1)
strPost = Right(rs1!Name, Len(rs1!Name) - InStr(1, rs1!Name, ">"))
rs1.Edit
rs1!Name = strPre & strPost
rs1.Update
'MsgBox strPre & strPost
End If
End If
rs1.MoveNext
Loop
End Function
 
I tried this, but it still gives me an error when it gets to the rs1.edit
line and says it's read only. Below is the exact code I tried... I don't
understand what's wrong.
Any suggestions?
thanks,
ck


Function Remove_Brackets_Test()
Dim rs1 As DAO.Recordset
Dim strPre As String
Dim strPost As String

'Set rs1 = CurrentDb.OpenRecordset("QueryName")
Set rs1 = CurrentDb.OpenRecordset("QueryName", dbOpenDynaset)


Do While Not rs1.EOF

If InStr(1, rs1!Name, "<") Then 'checking for <
If InStr(1, rs1!Name, ">") Then 'checking for >
'REMOVE ALL BETWEEN BRACKETS

strPre = Left(rs1!Name, InStr(1, rs1!Name, "<") - 1)
strPost = Right(rs1!Name, Len(rs1!Name) - InStr(1, rs1!Name, ">"))
rs1.Edit
rs1!Name = strPre & strPost
rs1.Update
'MsgBox strPre & strPost
End If
End If
rs1.MoveNext
Loop
End Function
 
Is the query updatable when you open it in the Query window?

If the query's not updatable, then a recordset based on that query won't be
either.
 
Hi, how do I know if the query is updatable? Here is the query's SQL:
SELECT Name.Name, Name.Phone, Name.NLine, Name.ELine, Name.ALine, Name.MP,
Meeting.Name AS Church, Field.Name AS Field
FROM (Field INNER JOIN Meeting ON Field.Code = Meeting.FCode) INNER JOIN
Name ON Meeting.Code = Name.MCode
ORDER BY Name.Name;

thanks,
ck
 
....when I open the query in the form view, I can't change any of the names or
data in it, so it must not be updatable? How do I make it updatable? It's
just a simple query listing names, with other data from linked tables.
thanks,
ck
 
SELECT Name.Name, Name.Phone, Name.NLine, Name.ELine, Name.ALine, Name.MP,
Meeting.Name AS Church, Field.Name AS Field
FROM (Field INNER JOIN Meeting ON Field.Code = Meeting.FCode) INNER JOIN
Name ON Meeting.Code = Name.MCode
ORDER BY Name.Name;

Does the table NAME have a primary key field? If so, include it in your
query.


Try using the following as the source for rs1
StrSql = "SELECT Name.Name FROM Name" & _
" WHERE Exists (SELECT * FROM Meeting INNER JOIN Field " & _
" ON Meeting.FCode = Field.Code" & _
" WHERE Meeting.Code = Name.MCode)"

Set rs1 = CurrentDb.OpenRecordset(StrSQL, dbOpenDynaset)
.....
strPre = Left(rs1!Name, InStr(1, rs1!Name, "<") - 1)
strPost = Right(rs1!Name, Len(rs1!Name) - InStr(1, rs1!Name,
">"))
rs1.Edit
rs1!Name = strPre & strPost
rs1.Update
....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Actually, you should rename tables Name and Field, and rename the field Name
(I'd also rename Code while I was at it)

Name and Field are both reserved words, and should never be used for your
own purposes.

If you cannot (or will not) rename the table, at least use square brackets
around the keywords:

StrSql = "SELECT [Name] FROM [Name]" & _
" WHERE Exists (SELECT * FROM Meeting INNER JOIN [Field] " & _
" ON Meeting.FCode = [Field].
Code: 
" & _
" WHERE Meeting.[Code] = Name.MCode)"

For a good discussion on what names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html
 
Back
Top