Hello helper people who are smarter than me:
I have a form that needs to submit multiple queries to different tables
during one Sub's execution. Some sections are as simple as:
1| With rst
2| .Open query1
3| .Close
4| .Open query2
5| End With
However, what I run into is that .Open and .Close don't always get
triggered, or maybe the state isn't set, but I do know I frequently run
into the error "Method not allowed when the object is open" message at
places like line 4.
I take the next step: run an empty loop that waits for the Recordset to
Close before going on:
1| With rst
2| .Open query1
3| .Close
4| Do While .State <> adStateClosed
5| Loop
6| .Open query2
7| End With
Problem is, I get stuck in an infinite loop, because the recordset
never closes, even though ".Close" was the last command read before
going into the loop?
So, what would cause .Close not to close? 6 6830 bl*****@carolin a.rr.com wrote: Hello helper people who are smarter than me: I have a form that needs to submit multiple queries to different tables during one Sub's execution. Some sections are as simple as:
1| With rst 2| .Open query1 3| .Close 4| .Open query2 5| End With
However, what I run into is that .Open and .Close don't always get triggered, or maybe the state isn't set, but I do know I frequently run into the error "Method not allowed when the object is open" message at places like line 4.
I take the next step: run an empty loop that waits for the Recordset to Close before going on:
1| With rst 2| .Open query1 3| .Close 4| Do While .State <> adStateClosed 5| Loop 6| .Open query2 7| End With
Problem is, I get stuck in an infinite loop, because the recordset never closes, even though ".Close" was the last command read before going into the loop?
So, what would cause .Close not to close?
So why are you closing the recordset prior to opening query2? You might
be better op opening rst with query1, closing. Then create a new
With/Endwith set for query2.
Instead of opening/closing queries you could write the queries directly
in your loop using an array of select statements from your Form's code
module:
Sub RunMultipleQuer ies()
Dim arrQ As Variant, i As Integer, j As Integer, k As Integer
Dim DB As DAO.Database, RS As DAO.Recordset
arrQ = Array("Select fldx From tbl1 Where fldy = '" & txtSomething &
"'", "Select fldx From tbl2 Where fldy = '" & txtSomethingEls e & "'")
Set DB = CurrentDB
For i = 0 to Ubound(arrQ)
Set RS = DB.OpenRecordse t(arrQ(i))
Do While Not RS.EOF
'Do your stuff
RS.MoveNext
Loop
RS.Close
Next
End Sub
As you can see, this procedure also accommodates/uses parameters very
easily. If you want to run this procedure from a standard module
instead of a Form module, you can replace txtSomething/SomethingElse
with string variables that you could either pass in to the procedure or
use global string vars.
Sub RunMultipleQuer ies(strSomethin g As String, strSomethingEls e As
String)
...
End Sub
Rich
*** Sent via Developersdex http://www.developersdex.com ***
I get an error when I consecutively call
..Open query1
..Open query2
citing that you can't open an open recordset. Makes sense to me.
The problem is, sometimes the ".Close" command between gets ignored,
and I get the error anyway.
It's not a matter of how I should construct the queries; these queries
are often very different, and setting up a loop to construct the
queries would be impractical, if not impossible - and again, that's not
the problem. Put in simple terms, the problem is this:
Bob: "Open a recordset based on query 1."
PC: "OKAY."
Bob: ".....ok, that's done, now close the recordset."
PC: "OKAY."
Bob: "And now open the same recordset using query 2."
PC: "I CAN'T. RECORDSET STILL OPEN."
Bob: "WT......?" bl*****@carolin a.rr.com wrote: I get an error when I consecutively call .Open query1 .Open query2 citing that you can't open an open recordset. Makes sense to me. The problem is, sometimes the ".Close" command between gets ignored, and I get the error anyway.
It's not a matter of how I should construct the queries; these queries are often very different, and setting up a loop to construct the queries would be impractical, if not impossible - and again, that's not the problem. Put in simple terms, the problem is this:
Bob: "Open a recordset based on query 1." PC: "OKAY." Bob: ".....ok, that's done, now close the recordset." PC: "OKAY." Bob: "And now open the same recordset using query 2." PC: "I CAN'T. RECORDSET STILL OPEN." Bob: "WT......?"
Instead of being tricky why not open/close in steps
Set rst = New ADODB.Recordset
With rst
.Open "Query1"
If .State = adStateOpen Then
.Close
End If
End With
set rst = Nothing
Set rst = New ADODB.Recordset
With rst
.Open "Query2"
If .State = adStateOpen Then
.Close
End If
End With
set rst = Nothing bl*****@carolin a.rr.com wrote in message
<11************ **********@i40g 2000cwc.googleg roups.com> : Hello helper people who are smarter than me: I have a form that needs to submit multiple queries to different tables during one Sub's execution. Some sections are as simple as:
1| With rst 2| .Open query1 3| .Close 4| .Open query2 5| End With
However, what I run into is that .Open and .Close don't always get triggered, or maybe the state isn't set, but I do know I frequently run into the error "Method not allowed when the object is open" message at places like line 4.
I take the next step: run an empty loop that waits for the Recordset to Close before going on:
1| With rst 2| .Open query1 3| .Close 4| Do While .State <> adStateClosed 5| Loop 6| .Open query2 7| End With
Problem is, I get stuck in an infinite loop, because the recordset never closes, even though ".Close" was the last command read before going into the loop?
So, what would cause .Close not to close?
Why are you opening recordsets when you don't do anything with them?
Have you hidden what you do with the recordsets, or are you opening
recordsets to execute action queries?
If the latter, consider trying something like this
With yourconnection
.execute "query1", , adcmdstoredproc + adexecutenoreco rds
.execute "query2", , adcmdstoredproc + adexecutenoreco rds
End With
--
Roy-Vidar
Found my issue: I was running action queries, but not running .Update
on the Recordset afterward. As soon as I entered that command,
everything ran fine. Odd that I had remembered that sometimes and
forgotten it at others. Guess that's what I get for being so darn
human. :) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Rob Meade |
last post by:
Lo all,
Ok - this is what I was aiming to do, and then I thought - naahhh, that cant
be right!
query database
results to recordset
results to array using GetRows
update values in one column in array
<BOING>
|
by: dmiller23462 |
last post by:
My brain is nuked....Can anybody tell me right off the bat what is
wrong with this code? Along with any glaring errors, please let me
know the syntax to display a message (Response.Write would be fine I
think) that will say "I'm sorry but the data you requested cannot be
found" or something along those lines....
This code is on an archive page I have on my company's intranet....The
end result is to show 3 records at a time pulled from an...
|
by: berlinbrown |
last post by:
What is the best way to open run a query and then open a recordset,
for example.
Set rst = exDatabase.OpenRecordset(sql)
' Run Query
....
....
....
|
by: alex_peri |
last post by:
Hello All,
I am having problems with sorting a recordset by fields in Access. I
have a table with three columns called ID, SNo and Time and would like
to sort the records by Time. I would like to sort them in the actual
database and not just Order them with SQL. I have included the code I
wrote below. Please bear in mind that I am very new to Access and SQL.
I used the Helpfile of Access for this code but i can't make it work
for me....
|
by: Corey |
last post by:
I am missing something here. I have a pop up form (loads from the
"main form")that displays multiple command buttons. When a user
selects a particular button, the recordset from the main form should be
updated. Here is the code that I have written but it doesnt work. I
get too few parameters on rs1. Can anyone shed some light on what I am
missing? I appreciate help in advance-Corey
Private Sub APPRVD_Click()
| |
by: Jan |
last post by:
Hi
I have a database that I use to keep track of the sales promotions that we
send to companies.
I normally send a mailing based on a subset of the companies in the database
(found using the query: QryICTMassDistribution3) , I then use a form and
the code below to create a new record in the corrispondence table to show
what corrispondence has been sent to various companies.
|
by: Darryl Kerkeslager |
last post by:
When I open an ADO Recordset, I close it. However, it seems that there may
be some difference in this manner of opening a Recordset:
Dim rL As ADODB.Recordset
Set rL = New ADODB.Recordset
src = "SELECT Count(*) FROM reviewer INNER JOIN pp_officer " & _
"ON reviewer.reviewer_id = pp_officer.ppo_rev_id " & _
"WHERE rev_login = 'EllisonL'"
Set rL = CurrentProject.Connection.Execute(src, , adCmdText)
|
by: kjvt |
last post by:
Based on a prior posting, I've written a function to convert a
recordset to a dataview. The first call to the function for a given
recordset works perfectly, but the second call always returns a
dataview with a count = 0. Can someone explain why and how I might
work around this problem?
Here is the code for my function:
Public Shared Function GetViewFromRS(ByVal pRS As ADODB.Recordset) _
As DataView
|
by: PW |
last post by:
Hi,
I'd like to close a recordset and set the database to nothing if a
recordset is open if an error has occured. Leaving a recordset open
and a database open isn't a good idea, right?
Thanks,
-paul w
|
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: 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: 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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |