Querying a Table in Code... ADO and SQL

[dodgyone]

Hi there...

I'm trying to search a table for a date using the following ado code and sql statement. Is the SQL correct? Is the ADO correct?

After this I wanted to use EOF (or is this just for DAO objects?) to see if the end of the recordset was reached and if it wasn't give an error message.

Dim MySql As String
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim Rs1 As ADODB.Recordset

'the SQL statement to search the Tables
MySql = "SELECT tblTimeSheets.StartingDate "
MySql = MySql & "FROM tblUser INNER JOIN tblTimeSheets ON "
MySql = MySql & "tblUser.UserID = tblTimeSheets.UserID "
MySql = MySql & "WHERE tblUser.Username=" & CurrentUser & " "
MySql = MySql & "AND tblTimeSheets.StartingDate=" & DateChosen & ";"

Rs1.Open MySql, cnn, adOpenStatic, adLockOptimistic

If Not Rs1.EOF Then
i = MsgBox("You have already used this date on a previous Time Sheet." & Chr(13) & Chr(13) & "The date chosen cannot be used again.", vbExclamation, "Date Chosen Invalid...&quot
Else

End If

Any help would be appreciated ;o) Thanks....

 

[TerpFan2001]

If the Username field is text you will have to place ' around CurrentUser

MySql = MySql & "WHERE tblUser.Username='" & CurrentUser & "' "

The DateChosen should have # around it

MySql = MySql & "AND tblTimeSheets.StartingDate=#" & DateChosen & "#"

You can use .EOF with ADO. I think you might be better off using the RecordCount Property instead:

If Rs1.RecordCount > 1 Then

i = MsgBox("You have already used this date on a previous Time Sheet." & Chr(13) & Chr(13) & "The date chosen cannot be used again.", vbExclamation, "Date Chosen Invalid...&quot

Else