Count Number of Records in table

[ewong1]

I have the code below - what I am trying to accomplish is determine whether or not there are any records in "tblEquipmentSchedule" that have a matching date and schedule type as the forms current view. I know for a fact that there are some records that exist - for example if I put in the date 5/21/06 for schedule type "1" I should get a response later in my code that there is 1 record in teh database. This is not happening though. Any ideas on how I can fix this?

    Dim db As DAO.Database
    Set db = CurrentDb
    
    Dim strSQL As String
    strSQL = "tblEquipmentSchedule"
    
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset(strSQL, dbOpenTable)
    
    Dim intRecordCount As Integer

    'Determine if Schedule has already been created for the date provided in "dtmSchedule" field and "idScheduleType"
    intRecordCount = 0
    
    Do Until rs.EOF Or intRecordCount = 1
        
        If (rs!dtmSchedule = Me.dtmSchedule) And (rs!idScheduleType = Me.cboScheduleType) Then
            intRecordCount = intRecordCount + 1
        Else
        End If
        rs.MoveNext
    Loop
    
    rs.Close
    db.Close

 

[namliam]

strSQL = "tblEquipmentSchedule"

I would change this to a query, works faster and more accurate...

strSQL = "select * from tblEquipmentSchedule " & _
         "where dtmSchedule = #" & format(Me.dtmSchedule,"mm/dd/yyyy") & "# " & _
         "and    idScheduleType = " & Me.cboScheduleType

Offcourse you will need to change dbOpenTable as well. And you will not need the Do Untill loop, for if the query returns records then you have a match...

General tip: Put all the Dim commands together on the top of the procedure, its concidered 'good practice' to not dim all the way thru your procedure tho VBA allows it.

Back to your question, I cannot really see a problem in your script other than above meantioned "in-perfections". One thing you may consider is that the dates may be treated as strings somewhere and a conversion problem is preventing the match to happen.