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! 4 4448 go****@darincline.com wrote: 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!
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.
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!
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!
Just posting again hoping someone has a suggestion. Surely this isn't
an isolated problem (?) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: matt breedlove |
last post by:
(x-posted to microsoft.public.dotnet.general and
microsoft.public.dotnet.languages.vb)
Hello all.
I am currently creating a Windows Service using VB.NET and would like to
return an error if...
|
by: Jonathan LaRosa |
last post by:
I am trying to open a recordset and I am getting an error and I can't
figure out why.
See code below. sqlString2 does not work. sqlString does. Clearly
the problem is with the nested SELECT...
|
by: GVK |
last post by:
Hi,
I'm facing another problem with my network program. I'm writing a basic server
that handles multiple clients. Whenever a client is connected, a new thread is
created to handle it.
The socket...
|
by: John Mason |
last post by:
I have a file name in my app.config file which contains the & character.
<appSettings>
<!-- User application and configured property settings go here.-->
<!-- Connection string values -->
<add...
|
by: mast2as |
last post by:
Hi everyone... I have a TExceptionHandler class that is uses in the
code to thow exceptions. Whenever an exception is thrown the
TExceptionHander constructor takes an error code (int) as an...
|
by: Bhanu Murthy |
last post by:
Dear Music lovers,
I want to copy one Music CD of my friend who brought from US. I want to burn CD using nero 'copy disk' option. I am not able to copy. Reasons displayed are read error in...
|
by: Roger |
last post by:
ms-access97 & sql server2005
two tables
tblItem
tblItemFeature
form frmItem contains subform frmItemFeature
each form is based on their respective table
creating new record and filling in...
|
by: neridaj |
last post by:
Hello,
I'm trying to figure out how to get rid of these errors:
Notice: Undefined offset: 1 in output_fns.php on line 315
Notice: getimagesize() : Read error! in
output_fns.php on line 315...
|
by: Nike1984 |
last post by:
I'm fairly new to Javascript and it's more of a guessing game for me...
I'm trying to build an app for Google Maps and just had some issues recently.
First off I just wanted to say that everything...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |