Queries in VBA,ADO | Familiar Sight | | Join Date: Apr 2007 Location: Sydney, Australia
Posts: 168
| | |
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.
|  | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: Queries in VBA,ADO
Try this ... -
rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = [Forms]![frmJobDetailsMain]![tbTFN]"
-
| | Familiar Sight | | Join Date: Apr 2007 Location: Sydney, Australia
Posts: 168
| | | re: Queries in VBA,ADO
Quote:
Originally Posted by mmccarthy 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.
| | Familiar Sight | | Join Date: Apr 2007 Location: Sydney, Australia
Posts: 168
| | | re: Queries in VBA,ADO
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
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: Queries in VBA,ADO
Quote:
Originally Posted by iheartvba 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.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: Queries in VBA,ADO
Quote:
Originally Posted by iheartvba 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.
| | Familiar Sight | | Join Date: Apr 2007 Location: Sydney, Australia
Posts: 168
| | | re: Queries in VBA,ADO
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
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,865
| | | re: Queries in VBA,ADO
Quote:
Originally Posted by iheartvba 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?
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,327 network members.
|
