473,320 Members | 1,817 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Queries in VBA,ADO

171 100+
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.
May 8 '07 #1
7 8421
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = [Forms]![frmJobDetailsMain]![tbTFN]"
  2.  
May 8 '07 #2
iheartvba
171 100+
Try this ...

Expand|Select|Wrap|Line Numbers
  1. rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = [Forms]![frmJobDetailsMain]![tbTFN]"
  2.  
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.
May 8 '07 #3
iheartvba
171 100+
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
May 8 '07 #4
MMcCarthy
14,534 Expert Mod 8TB
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.
May 8 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
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.
May 8 '07 #6
iheartvba
171 100+
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
May 8 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
Ok I seem to have fixed it, heres what it looks like
Expand|Select|Wrap|Line Numbers
  1.  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?
May 8 '07 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

6
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...
5
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...
1
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)...
9
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...
3
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...
5
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...
0
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...
44
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...
1
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...
5
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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)...
1
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...
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.