record loops (1 Viewer)

[agehoops]

Hey.
I want to create a loop which runs through all records in a table, to compare the data it finds with the data entered.

What I've got is a time field, and when a value is entered, I want it to run through every record in the table, and check if the time entered is between the time of two of the tables fields (start time and end time) and if it is then throw an error message up, if not then do the next bit.

Thanks for help

Aidy

 

[pbaldy]

The more records in the table, the less efficient that method will be. I would open the recordset on an SQL statement that only pulled records where the times clashed. Then check that recordset for EOF. If true, there are no clashes. In fact, since you don't really need the records, I'd use an SQL statement that counted the clashes, and test the count.

 

[agehoops]

alright, how do I go about doing that? I mean, I can create an SQL string to get a record, but how to I get it to check if one time is between 2 times or equal to them in the table?

 

[WayneRyan]

Hoops,

If you have a form with txtTime on it:

If DCount("[Time1]", "Table1", "#" & Me.txtTime & "# Between StartTime And EndTime") > 0 Then ...

If you really need a loop:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset "Select * From Table1 Where #" & Me.txtTime & "# Between StartTime And EndTime"
While Not rst.EOF And Not rst.BOF
   Debug.print "This record is bad. " & rst!SomeField
   rst.MoveNext
   Wend

hth,
Wayne