I have a form which allows the user to select various options, which
options I then use to dynamically generate a query into the database.
I then want to display this query to the user. However, no matter
which way I go about it, I seem to run into a snag. Currently, I'm
doing this:
Dim ReportQuery As String
'Code to generate dynamic query
Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.CreateQueryD ef "CreateReportQr y", ReportQuery
qdf.OpenRecords et
DoCmd.OpenQuery "CreateReportQr y", , acReadOnly
This works once but fails if run a second time because a query named
"CreateReportQr y" already exists. I can modify the CreateQueryDef
function call to use "" as the name and create a temp query. However,
if I do that I can't find a way to use the OpenQuery function because
the query doesn't have a name to pass to the function. I've tried
deleting the record set at the end of the function but that gives me an
error "Object variable or with block variable not set" I'm not
positive but I suspect it may be because I'm trying to delete the
record set while the query is still being displayed. I'd try deleting
the record set when the query form is closed, but it's a dynamic query
and I can't find a way to trap the form close event. There's probably
a very simple way to do this but I can't seem to track it down. Any
assistance would be greatly appreciated. 2 4536
Dan <da*********@gm ail.com> wrote: I have a form which allows the user to select various options, which options I then use to dynamically generate a query into the database.
I then want to display this query to the user. However, no matter which way I go about it, I seem to run into a snag. Currently, I'm doing this:
Dim ReportQuery As String 'Code to generate dynamic query
Dim db As Database Dim rs As Recordset Dim qdf As QueryDef
Set db = CurrentDb Set qdf = db.CreateQueryD ef "CreateReportQr y", ReportQuery qdf.OpenRecords et
DoCmd.OpenQuery "CreateReportQr y", , acReadOnly
This works once but fails if run a second time because a query named "CreateReportQr y" already exists.
You should be testing that it exists and delete it if it does...
I can modify the CreateQueryDef function call to use "" as the name and create a temp query. However, if I do that I can't find a way to use the OpenQuery function because the query doesn't have a name to pass to the function.
Depends how you want to use the recordset.....
I've tried deleting the record set
You should be deleting the query, not the recordset.....
at the end of the function but that gives me an error "Object variable or with block variable not set" I'm not positive but I suspect it may be because I'm trying to delete the record set while the query is still being displayed.
As above....
I'd try deleting the record set when the query form is closed, but it's a dynamic query and I can't find a way to trap the form close event. There's probably a very simple way to do this but I can't seem to track it down. Any assistance would be greatly appreciated.
CurrentDB.Query Defs.Delete "CreateReportQr y"
--
regards,
Bradley
A Christian Response http://www.pastornet.net.au/response
Dan wrote: I have a form which allows the user to select various options, which options I then use to dynamically generate a query into the database.
I then want to display this query to the user. However, no matter which way I go about it, I seem to run into a snag. Currently, I'm doing this:
Dim ReportQuery As String 'Code to generate dynamic query
Dim db As Database Dim rs As Recordset Dim qdf As QueryDef
Set db = CurrentDb Set qdf = db.CreateQueryD ef "CreateReportQr y", ReportQuery qdf.OpenRecords et
DoCmd.OpenQuery "CreateReportQr y", , acReadOnly
This works once but fails if run a second time because a query named "CreateReportQr y" already exists. I can modify the CreateQueryDef function call to use "" as the name and create a temp query. However, if I do that I can't find a way to use the OpenQuery function because the query doesn't have a name to pass to the function. I've tried deleting the record set at the end of the function but that gives me an error "Object variable or with block variable not set" I'm not positive but I suspect it may be because I'm trying to delete the record set while the query is still being displayed. I'd try deleting the record set when the query form is closed, but it's a dynamic query and I can't find a way to trap the form close event. There's probably a very simple way to do this but I can't seem to track it down. Any assistance would be greatly appreciated.
This may or may not be what you need
Dim rstFilter As Recordset
Dim rst As Recordset
Set rst = qdf.OpenRecords et
rst.Filter = "ID > 1 and ID < 1000"
rst.Sort = "ID Desc"
Set rstFilter = rst.OpenRecords et This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Rahul Chatterjee |
last post by:
Hello All
I have an asp page in which I am performing the following
1. Querying a database view
2. Returning rows in to a recordset.
3. Looping thru the recordset and printing the data
4. Before displaying the data I am assigning the values from the recordset
into variables
5. There are 2 entry points to this page both of which would run similar
|
by: Bryan |
last post by:
I have a results table that is 5 columns wide. the recordset is
returned with 48 items. I have no problem displaying 5 per row until I
hit the last row where i get a ADODB.Field error '80020009'
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.
My question is how do I close of the table row when I reach the EOF?
i.e
|
by: Jennifer Smith |
last post by:
I want to be able to display my recordset as follows:
a e
b f
c g
d h
Instead of :
a b
c d
|
by: gonzal |
last post by:
Hi, just a quick question regarding performance and speed.
Q. Run a complicated query three times or create a TEMPORARY table and
access it as needed?
|
by: Lyn |
last post by:
I need some guidance for a technique that will allow me to accomplish the
following...
I have a table in which each record contains a photograph. I would like to
display in a form a thumbnail size version of each photograph horizontally
across the form, then have the photgraphs "word wrap" (picture wrap ?) at
the right end of the form onto the next "line" of photographs. Does anyone
know how this can be done?
Obviously, displaying...
| |
by: Tomaz Koritnik |
last post by:
Hi
I have many short HTML files stored in a binary stream storage to display
descriptions for various items in application. HTML would be display inside
application using some .NET control or COM control (like Microsoft
WebBrowser). For each description there is one HTML file and along
description text, it contains links to related information. Clicking related
information would for example open new form in application and display some...
|
by: Sonia Scott |
last post by:
I have done the following code, which seem to partially work. I got my HTML ALT (text) from the database shown, but for some reason cannot display the image.
Below is my connection code:
<%
Dim SurveyID
SurveyID = request("SurveyID")
set dbConnection = Server.CreateObject("ADODB.Connection")
fileType = "Driver={Microsoft Access Driver (*.mdb)};"
fileLocation="DBQ=" & Server.MapPath("PPPCdb/PPPC_Database.mdb")
|
by: Drew |
last post by:
I posted this to the asp.db group, but it doesn't look like there is much
activity on there, also I noticed that there are a bunch of posts on here
pertaining to database and asp. Sorry for cross-posting.
I am trying to build a "checklist", where a user can navigate to an ASP page
on the intranet which shows a list of "questions" that the user can check
off. I am trying to figure out how to do this so that it is scalable, but I
am...
|
by: rghollenbeck |
last post by:
Here's my code so far:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As DAO.Database
Dim rs As DAO.RecordSet
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryTableOfGrades")
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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: 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: 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: 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.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |