Hi,
I am trying to figure out how to write queries using ADO
For example if I want to Select (Field) From (Table) Where
(Field) = (TextBox in Form) and just open that query as a recordset.
I am getting the following error:
RunTime error 3709
The Connection Cannot be used to perform this operation. It is either closed or invalid in this context.
This is my code:
-----------------------------------------
Option Compare Database
Option Explicit
Public rst As New ADODB.Recordset
Public cnn As ADODB.Connection
------------------------------------------------------
</ADO>
Set cnn = CurrentProject.Connection
rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = Forms!frmJobDetailsMain.tbTFN"
If (rst.EOF And rst.BOF) Then MsgBox "The Recordset is Blank" Else MsgBox "The Recordset isn't Blank"
Set rst = Nothing
Set cnn = Nothing
</ADO>
tbTFN is a text box containing the value i would like the query to filter by
Thanking you kindly, This forum has been a great help to me.
7 8421
Try this ... -
rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = [Forms]![frmJobDetailsMain]![tbTFN]"
-
Try this ... -
rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = [Forms]![frmJobDetailsMain]![tbTFN]"
-
I Tried it, but I'm getting the same error, it the error as per my first post is reffering to the current ADODB connection "cnn" being unable to open sql statements as a recordset, maybe I'm missing a reference in the access library or something. I'm not having any problems with opening external Query objects via ADO as recordsets, but even those give problems when I create an external Select Query and set the Criteria field as a text box from a form.
Yeah, I didnt do something to intelligent, Here is a corrected version of what I was doing before I hadn't Defined set the connection "cnn" when I was declaring the recordset. but now its giving me a different error, now its saying No Value Given for one or more given Parameters, the runtime error code is 2147217904.
[Code/]
rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = Forms!frmJobDetailsMain!tbTFN", cnn, adOpenKeyset, adLockOptimistic [/code]
Thanks
I Tried it, but I'm getting the same error, it the error as per my first post is reffering to the current ADODB connection "cnn" being unable to open sql statements as a recordset, maybe I'm missing a reference in the access library or something. I'm not having any problems with opening external Query objects via ADO as recordsets, but even those give problems when I create an external Select Query and set the Criteria field as a text box from a form.
You've used </ADO> to define your code but how is it actually defined in the module. In other words can you post the opening and closing sections of this procedure.
Yeah, I didnt do something to intelligent, Here is a corrected version of what I was doing before I hadn't Defined set the connection "cnn" when I was declaring the recordset. but now its giving me a different error, now its saying No Value Given for one or more given Parameters, the runtime error code is 2147217904.
[Code/]
rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = Forms!frmJobDetailsMain!tbTFN", cnn, adOpenKeyset, adLockOptimistic [/code]
Thanks
Put the square brackets in as per my previous example
[Code/]
rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = [Forms]![frmJobDetailsMain]![tbTFN]", cnn, adOpenKeyset, adLockOptimistic [/code]
Also make sure the form you are referencing is open.
Ok I seem to have fixed it, heres what it looks like
[/code] rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = '& Forms!frmJobDetailsMain!tbTFN &'", cnn, adOpenKeyset, adLockOptimistic [/code]
PS sorry about the poor tagging practices, I have been meaning to learn how to do it properly.
Thanks Guys
Ok I seem to have fixed it, heres what it looks like - rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = '& Forms!frmJobDetailsMain!tbTFN &'", cnn, adOpenKeyset, adLockOptimistic
PS sorry about the poor tagging practices, I have been meaning to learn how to do it properly.
Thanks Guys
Leave the backslash off the first code tag.
Just out of curiousity where is this code?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Andreas Lauffer |
last post by:
I changed from Access97 to AccessXP and I have immense performance
problems.
Details:
- Access XP MDB with Jet 4.0 ( no ADP-Project )
- Linked Tables to SQL-Server 2000 over ODBC
I used...
|
by: WertmanTheMad |
last post by:
Ive been playing with this for a few days and thought I might thow it
out for seggestions.
I have Several Queries that need counts returned
The Queries are Mutually Exclusive meaning whatever...
|
by: Roger Green |
last post by:
I have inherited a complex database that has many dozens of queries that derive data from a people
table.
I now need to be able to run these queries (from within a significant number of forms)...
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my...
|
by: NeilAnderson |
last post by:
I'm a fairly new user of access & I've never had any training, so I'm
wondering if I'm doing the right thing here, or if it matter at all.
I'm building a database for room booking purposes and I'm...
|
by: Jerry Hull |
last post by:
I'm working with a database developed by an untrained person over
several years - and on a network that has recently been upgraded with
a new server installed and MS office upgraded from 2K (I...
|
by: MHenry |
last post by:
Hi,
I know virtually nothing about creating Macros in Access.
I would appreciate some help in creating a Macro or Macros that
automatically run(s) 14 Queries (three Make Table Queries, and 11...
|
by: Greg Strong |
last post by:
Hello All,
Is it better to create a query in DAO where a report has 4 sub-reports
each of whose record source is a query created at runtime and
everything is in 1 MDB file?
From what I've...
|
by: loosecannon_1 |
last post by:
Hello everyone, I am hoping someone can help me with this problem. I
will say up front that I am not a SQL Server DBA, I am a developer. I
have an application that sends about 25 simultaneous...
|
by: marshmallowww |
last post by:
I have an Access 2000 mde application which uses ADO and pass through
queries to communicate with SQL Server 7, 2000 or 2005.
Some of my customers, especially those with SQL Server 2005, have had...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| | |