ADO Recordset SQL WHERE clause not working

[sko]

Hi I'm trying to get information from one subform to another. Everything works fine if I SELECT * FROM tbl but if I add a WHERE clause or try to SELECT a column name I can't see any record information. Here is my code. Can anyone please tell me what I'm doing wrong?
Thanks!

Dim rst As ADODB.Recordset
Dim CurDB As Database
Dim curID As Integer
Dim curDiagNo As String
Dim myCode As String

curID = [Forms]![SuperBill]![sbID]
curDiagNo = Me![diag8]

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

'CurrentProject.Connection
curID = [Forms]![SuperBill]![sbID]


With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.Name
.Open
End With


'Open read-only recordset to get codeEntry value
Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

'This works without where clause

rst.Open "SELECT * FROM tblDiagEntry WHERE sbID = curID and diagNo = curDiagNo", CurConn

 

[WayneRyan]

sko,

Assuming they're both numbers:

rst.Open "SELECT * FROM tblDiagEntry WHERE sbID = " & curID & " and diagNo = " & curDiagNo, CurConn

Assuming they're both text:

rst.Open "SELECT * FROM tblDiagEntry WHERE sbID = '" & curID & "' and diagNo = '" & curDiagNo & "'", CurConn

Note the single-quotes for string parameters.

Wayne

 

[sko]

Thanks Wayne but it looks like the recordset isn't open now. Any Ideas?

 

[WayneRyan]

sko,

What does a closed recordset look like?

Do you mean that it doesn't return any records?

You can:

Dim sql As String
sql = "SELECT * FROM tblDiagEntry WHERE sbID = '" & curID & "' and diagNo = '" & curDiagNo & "'"
rst.Open sql, CurConn

Then you can bring up a msgbox or print the value of sql in the immediate
window.

Then paste that string in a query "SQL View"

Wayne

 

[sko]

Sorry, I'm not sure how to do that. I'm new to access. I think the recordset is closed when I use the where clause because the rst.EOF says Operation is not allowed when the object is closed. When I don't use the where clause I get true or false. by the way one set is numbers on is a string.

sql = "SELECT * FROM tblDiagEntry WHERE sbID = " & curID & " and diagNo = '" & curDiagNo & "'"

 

[WayneRyan]

sko,

Can you post a sample DB? I'm leaving for a while, but I'm sure that
someone will look at at. If not, I will later.

Wayne

 

[sko]

Wayne, I can't post the db I'm working on so I made a small sample of what I'm trying to achieve. Of course this sample works so I'm not sure why my real db isn't working? At least I know that my SELECT statement works Thanks to you. I'll have to go through everything to see if I can figure out what code I have that's messing me up. If anyone has any ideas where I can start I'd appreciate it.

 

[sko]

Well I finally figured it out! As it turns out I had different data types on the diagNo and diag8. THANK YOU SO MUCH!!!!
SKO

 

[WayneRyan]

sko,

Glad you got it sorted out.

Hope to hear from you again sometime.

Wayne