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.NewFilterRac k > 0 Then ' We want to change filter racks
NewString = DLookup("[FilterBoxDescri ption]",
"lookupFilterBo xType", "[FilterBoxType] = " & Me.NewFilterRac k)
Set rs = db.OpenRecordse t("SELECT Units.JobID, Units.GroupID,
Units.Order, Units.Tag, Units.UnitID, Types.UnitType, Types.FilterBox ,
Units.FilterTyp eID, Units.PreFilter TypeID, Types2.FilterBo x, " &
Me.NewFilterRac k & " AS NewFilterRack " & _
"FROM (SELECT EquipmentTypes. UnitType, EquipmentTypes. UnitSize,
EquipmentTypes. FilterBox " & _
"FROM EquipmentTypes " & _
"WHERE ((EquipmentType s.FilterBox)=" & Me.NewFilterRac k & ")) AS
Types2 RIGHT JOIN (EquipmentTypes AS Types INNER JOIN FilterJobUnits AS
Units ON Types.UnitID = Units.UnitID) ON (Types2.UnitSiz e =
Types.UnitSize) AND (Types2.UnitTyp e = Types.UnitType) " & _
"WHERE (((Units.JobID) =" & Me.JobID & ") AND ((Units.GroupID )=" &
Me.GroupID & ") AND ((Types.UnitTyp e)=" & Me.Model & " OR " & Me.Model
& " = 0) AND ((Types.FilterB ox)=" & Me.FilterRack & " OR " &
Me.FilterRack & " = 0) AND ((Units.FilterT ypeID)=" & Me.FilterType & "
OR " & Me.FilterType & " = -1) AND ((Units.PreFilt erTypeID)=" &
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.FilterBo x]) = False Then
.Edit
![Types.FilterBox] = ![Types2.FilterBo x]
.Update
.MoveNext
Else
If MsgBox(NewStrin g & " 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  4460  go****@darincli ne.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.NewFilterRac k > 0 Then ' We want to change filter racks
NewString = DLookup("[FilterBoxDescri ption]",
"lookupFilterBo xType", "[FilterBoxType] = " & Me.NewFilterRac k)
Set rs = db.OpenRecordse t("SELECT Units.JobID, Units.GroupID,
Units.Order, Units.Tag, Units.UnitID, Types.UnitType, Types.FilterBox ,
Units.FilterTyp eID, Units.PreFilter TypeID, Types2.FilterBo x, " &
Me.NewFilterRac k & " AS NewFilterRack " & _
"FROM (SELECT EquipmentTypes. UnitType, EquipmentTypes. UnitSize,
EquipmentTypes. FilterBox " & _
"FROM EquipmentTypes " & _
"WHERE ((EquipmentType s.FilterBox)=" & Me.NewFilterRac k & ")) AS
Types2 RIGHT JOIN (EquipmentTypes AS Types INNER JOIN FilterJobUnits AS
Units ON Types.UnitID = Units.UnitID) ON (Types2.UnitSiz e =
Types.UnitSize) AND (Types2.UnitTyp e = Types.UnitType) " & _
"WHERE (((Units.JobID) =" & Me.JobID & ") AND ((Units.GroupID )=" &
Me.GroupID & ") AND ((Types.UnitTyp e)=" & Me.Model & " OR " & Me.Model
& " = 0) AND ((Types.FilterB ox)=" & Me.FilterRack & " OR " &
Me.FilterRack & " = 0) AND ((Units.FilterT ypeID)=" & Me.FilterType & "
OR " & Me.FilterType & " = -1) AND ((Units.PreFilt erTypeID)=" &
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.FilterBo x]) = False Then
.Edit
![Types.FilterBox] = ![Types2.FilterBo x]
.Update
.MoveNext
Else
If MsgBox(NewStrin g & " 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.Openr ecordset(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 something goes wrong during startup, IE: parsing a
configuration file.
The only way I know of is to throw an exception, and this will prevent the
|
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 statement. But when I copy
sqlString2 into a new query it runs just fine. Futhermore, I have
used a nested SELECT statement with the OpenRecordset() method before
and it works just fine. Any ideas?
thanks much,
|
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 descriptor of the client and it's sockaddr_in structure are passed
as arguments to the function that handles the client. Now, the following code is
giving me an error. It's the function that handles the client:...
|
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 key="ExcelFileName" value="T:\HR & Development\Personnel\Weekly
Report\Weekly Report.XLS" />
When I try to read this in my app, I get an 'error occured while parsing
EntityName'. Now I've found out that this is a known problem, but how do I
|
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 argument.
I was hoping to create a map<int, const char*that would be used in
the showError member function of the TExceptionHandler class where the
key (int) would be the error code and const char* the message printed
out to the console.
My question...
| | |
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 sector ... so on continuously,.. finally, it never copies. But the original CD, after installing, it working fine, but I am not able to copy/burn CD. Now they are out of sale, even to purchase.
Is there any way to copy.... the CD.
thank u...
|
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 two bound fields works fine
|
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
function get_imgdim()
|
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 works fine in FF and IE. It's Chrome I'm having issues with.
I understand that Chrome is still somewhat in beta stages, so some bugs might occur. However this seems like something I might have done.
So...
I used a code that I found on Econym as...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
