Queries in VBA,ADO

[iheartvba]

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.Connectio n
------------------------------------------------------
</ADO>
Set cnn = CurrentProject. Connection
rst.Open "Select tblJobDetails.R cptID From tblJobDetails Where tblJobDetails.T FN = Forms!frmJobDet ailsMain.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.

[MMcCarthy]

Try this ...

rst.Open "Select tblJobDetails.RcptID From tblJobDetails Where tblJobDetails.TFN = [Forms]![frmJobDetailsMain]![tbTFN]"
 

[iheartvba]

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.

[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.R cptID From tblJobDetails Where tblJobDetails.T FN = Forms!frmJobDet ailsMain!tbTFN" , cnn, adOpenKeyset, adLockOptimisti c [/code]

Thanks

[MMcCarthy]

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.

[MMcCarthy]

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.R cptID From tblJobDetails Where tblJobDetails.T FN = Forms!frmJobDet ailsMain!tbTFN" , cnn, adOpenKeyset, adLockOptimisti c [/code]

Thanks

Put the square brackets in as per my previous example
[Code/]
rst.Open "Select tblJobDetails.R cptID From tblJobDetails Where tblJobDetails.T FN = [Forms]![frmJobDetailsMa in]![tbTFN]", cnn, adOpenKeyset, adLockOptimisti c [/code]

Also make sure the form you are referencing is open.

[iheartvba]

Ok I seem to have fixed it, heres what it looks like
[/code] rst.Open "Select tblJobDetails.R cptID From tblJobDetails Where tblJobDetails.T FN = '& Forms!frmJobDet ailsMain!tbTFN &'", cnn, adOpenKeyset, adLockOptimisti c [/code]

PS sorry about the poor tagging practices, I have been meaning to learn how to do it properly.


Thanks Guys

[MMcCarthy]

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?