OpenRecordset & 3027 read only error

G

google

I am trying to implement form that allows a user to update data in
multiple records in a table based on criteria they enter in the form.
Because I want to allow some user interaction on a record level, and
provide some additional data validation, I'm trying to do it with code
instead of an update query. Unfortunately, it's not letting me update
the data: I'm getting an error saying the data is read only. The same
query statement, when run as straight SQL, will allow edits in the
datasheet view IF I use inconsistent updates (which is why I'm using
the dbinconsistent option for openrecordset). But it still won't
update via code. Following is the pertinant code:

Dim db As Database
Dim rs As Recordset
Dim NewString As String
Set db = CurrentDb

If Me.NewFilterRack > 0 Then ' We want to change filter racks
NewString = DLookup("[FilterBoxDescription]",
"lookupFilterBoxType", "[FilterBoxType] = " & Me.NewFilterRack)
Set rs = db.OpenRecordset("SELECT Units.JobID, Units.GroupID,
Units.Order, Units.Tag, Units.UnitID, Types.UnitType, Types.FilterBox,
Units.FilterTypeID, Units.PreFilterTypeID, Types2.FilterBox, " &
Me.NewFilterRack & " AS NewFilterRack " & _
"FROM (SELECT EquipmentTypes.UnitType, EquipmentTypes.UnitSize,
EquipmentTypes.FilterBox " & _
"FROM EquipmentTypes " & _
"WHERE ((EquipmentTypes.FilterBox)=" & Me.NewFilterRack & ")) AS
Types2 RIGHT JOIN (EquipmentTypes AS Types INNER JOIN FilterJobUnits AS
Units ON Types.UnitID = Units.UnitID) ON (Types2.UnitSize =
Types.UnitSize) AND (Types2.UnitType = Types.UnitType) " & _
"WHERE (((Units.JobID)=" & Me.JobID & ") AND ((Units.GroupID)=" &
Me.GroupID & ") AND ((Types.UnitType)=" & Me.Model & " OR " & Me.Model
& " = 0) AND ((Types.FilterBox)=" & Me.FilterRack & " OR " &
Me.FilterRack & " = 0) AND ((Units.FilterTypeID)=" & Me.FilterType & "
OR " & Me.FilterType & " = -1) AND ((Units.PreFilterTypeID)=" &
Me.PreFilter & " OR " & Me.PreFilter & " = -1)) " & _
"ORDER BY Units.Order;", , dbInconsistent)

With rs
If (.BOF And .EOF) Then Exit Sub
.MoveLast
.MoveFirst
Do Until .EOF
If IsNull(![Types2.FilterBox]) = False Then
.Edit
![Types.FilterBox] = ![Types2.FilterBox]
.Update
.MoveNext
Else
If MsgBox(NewString & " is not a valid filter rack for " &
![Units.Tag] & ". The filter rack for this unit will NOT be changed.
Click OK to continue, or Cancel to abort changing further units.",
vbOKCancel) = vbCancel Then Exit Sub
End If
Loop
Set rs = Nothing
End With



rs.Close
End If
Set rs = Nothing
Set db = Nothing


Any suggestions?
Thanks!
 
S

Salad

I am trying to implement form that allows a user to update data in
multiple records in a table based on criteria they enter in the form.
Because I want to allow some user interaction on a record level, and
provide some additional data validation, I'm trying to do it with code
instead of an update query. Unfortunately, it's not letting me update
the data: I'm getting an error saying the data is read only. The same
query statement, when run as straight SQL, will allow edits in the
datasheet view IF I use inconsistent updates (which is why I'm using
the dbinconsistent option for openrecordset). But it still won't
update via code. Following is the pertinant code:

Dim db As Database
Dim rs As Recordset
Dim NewString As String
Set db = CurrentDb

If Me.NewFilterRack > 0 Then ' We want to change filter racks
NewString = DLookup("[FilterBoxDescription]",
"lookupFilterBoxType", "[FilterBoxType] = " & Me.NewFilterRack)
Set rs = db.OpenRecordset("SELECT Units.JobID, Units.GroupID,
Units.Order, Units.Tag, Units.UnitID, Types.UnitType, Types.FilterBox,
Units.FilterTypeID, Units.PreFilterTypeID, Types2.FilterBox, " &
Me.NewFilterRack & " AS NewFilterRack " & _
"FROM (SELECT EquipmentTypes.UnitType, EquipmentTypes.UnitSize,
EquipmentTypes.FilterBox " & _
"FROM EquipmentTypes " & _
"WHERE ((EquipmentTypes.FilterBox)=" & Me.NewFilterRack & ")) AS
Types2 RIGHT JOIN (EquipmentTypes AS Types INNER JOIN FilterJobUnits AS
Units ON Types.UnitID = Units.UnitID) ON (Types2.UnitSize =
Types.UnitSize) AND (Types2.UnitType = Types.UnitType) " & _
"WHERE (((Units.JobID)=" & Me.JobID & ") AND ((Units.GroupID)=" &
Me.GroupID & ") AND ((Types.UnitType)=" & Me.Model & " OR " & Me.Model
& " = 0) AND ((Types.FilterBox)=" & Me.FilterRack & " OR " &
Me.FilterRack & " = 0) AND ((Units.FilterTypeID)=" & Me.FilterType & "
OR " & Me.FilterType & " = -1) AND ((Units.PreFilterTypeID)=" &
Me.PreFilter & " OR " & Me.PreFilter & " = -1)) " & _
"ORDER BY Units.Order;", , dbInconsistent)

With rs
If (.BOF And .EOF) Then Exit Sub
.MoveLast
.MoveFirst
Do Until .EOF
If IsNull(![Types2.FilterBox]) = False Then
.Edit
![Types.FilterBox] = ![Types2.FilterBox]
.Update
.MoveNext
Else
If MsgBox(NewString & " is not a valid filter rack for " &
![Units.Tag] & ". The filter rack for this unit will NOT be changed.
Click OK to continue, or Cancel to abort changing further units.",
vbOKCancel) = vbCancel Then Exit Sub
End If
Loop
Set rs = Nothing
End With



rs.Close
End If
Set rs = Nothing
Set db = Nothing


Any suggestions?
Thanks!
Click to expand...
My suggestion is to make you SQL string a string and open the recordset
that way. Ex:
Dim strSQL As STring
strSQL = "SElect ...."
Debug.Print strSQL
Set rst = Currentdb.Openrecordset(strSQL...
and when the code blows up go into the debug window, cut/paste the SQL
into a new query and determine where the code is incorrect.

You have 2 FROM statements. Maybe Access is choking on that part.
Anyway, having your string will point to the errors of your ways.
 
G

google

Thanks for the help. Unfortunately, the results are the same.
Copy/Pasting the resulting string into a new query results in the
output of that query being un-updateable UNTIL I set it to inconsistent
updates. I thought that using the dbinconsistent would result in the
same updating capability, but for some reason it's not. The dual FROM
clauses are a necessity of the structure of this query. But as I said,
the query on it's own runs fine with inconsistent updates. But for
some reason, I can't edit the results of that query with code, even
with dbInconsistent. Any more suggestions?

Thanks again!
 
G

google

Just wanted to check back now that the holidays are over, and see if
anyone has any more suggestions. I'm at an impasse here. As I've
mentioned, the query is updateable on it's own with inconsitent
updates, but for some reason, I just can't do it with code. I can't
figure out if the dbInconsistent option of openrecordset isn't working
as it should, or if there's something wrong with the way I'm creating
my database and/or recordset objects that is causing it to be read
only. Since the query itself works, it would SEEM that it shouldn't be
too hard to duplicate in code, but I just can't get there.

Any help would be much appreciated!
 
G

google

Just posting again hoping someone has a suggestion. Surely this isn't
an isolated problem (?)
 
Top