By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,853 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,853 IT Pros & Developers. It's quick & easy.

Queries in VBA,ADO

100+
P: 171
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
Share this Question
Share on Google+
7 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

100+
P: 171
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

100+
P: 171
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
Expert Mod 10K+
P: 14,534
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
Expert Mod 10K+
P: 14,534
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

100+
P: 171
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
Expert Mod 10K+
P: 14,534
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

Post your reply

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