Cannot get a SQL statement to work??? 1

[Cleis]

Hi group:

I've got a little problem below. As I'm not very strong in ADO, can someone please point out my mistake! I'm trying to run this SQL statement and post the result into a bound fieled on a form. When I run this code, I get "Method "open of Object" Recordset failed.



Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

Dim strSQL As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset


strSQL = "SELECT TOP 1 tblCalandar.Month FROM tblCalandar WHERE (((tblCalandar.End) >= Date)) ORDER BY tblCalandar.End;"
rs.Open strSQL, conn, adOpenStatic

Me.txtDetailPeriod = rs
rs.Close
conn.Close

Set rs = Nothing


Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

 

[Cleis]

THANKS FOR ALL OF YOUR HELP. Here is a star for your time!


Have a great weekeend!


Itch

 

[MajP]

Did it work?

 

[Cleis]

You bet it did! I would not of figured this out without your help!! Looks like I need a little more practice in ADO! As non I.T. person I've done pretty good with Access and VB from 2.0, 97 through XP . . . but I've always avoided opening recordsets through code by any means necessary. Ask me to do anything else such as arrays, loops, if thens, selects ect. . . . I can do this. For whatever reason, I've never gotten my hand around recordsets through vb. I don't know if there are just to many variables for my feable brain to figure out or what . . . . but I struggle with this!! I wish that I could spend more time working on this kind stuff but I spend more time managing people than I wish!! For what it's worth . . . . as simple as this code was, I get a thrill when it all comes together!

ahh, I'm just ranting. Once again, THANKS for your help! I was very much appreciated!!


Kind Regards!