Hello Friends,
here I am trying to have multiple record set or same table, since I want to check all the record in the table with some conditions.
It is basically for meeting room booking management. I want to check the status of room availability, booked or overbooked in screen.
Booked is my table with fileld1 Room Number, Field2 From date, Field3 Todate, TmDt0 is Date field from form and this is in on load event.
When loading first set is working fine. While executing the second record set it is not checking the if condition and directly going to else if and color the box in Red.
Please some one help to solve this. I am new to access and VBA, I am trying to do this from the help of this forum.
here I am trying to have multiple record set or same table, since I want to check all the record in the table with some conditions.
It is basically for meeting room booking management. I want to check the status of room availability, booked or overbooked in screen.
Booked is my table with fileld1 Room Number, Field2 From date, Field3 Todate, TmDt0 is Date field from form and this is in on load event.
When loading first set is working fine. While executing the second record set it is not checking the if condition and directly going to else if and color the box in Red.
Please some one help to solve this. I am new to access and VBA, I am trying to do this from the help of this forum.
Code:
Dim ob As ADODB.Recordset
Set ob = New ADODB.Recordset
ob.activeconnection = CurrentProject.Connection
ob.Open "Booked"
Cntr1 = 0
While Not ob.EOF
Room1 = ob.Fields(1).Value
Fromdt1 = ob.Fields(2).Value
Todt1 = ob.Fields(3).Value
Todt1 = DateAdd("d", 1, [Todt1])
If (Room1 = 1 And Cntr1 = 0) Then
If (TmDt0 >= Fromdt1 And TmDt0 <= Todt1) Then
Me!Rm2txt1.BackColor = vbBlue
Else
Me!Rm2txt1.BackColor = vbGreen
End If
ElseIf (Room1 = 1 And Cntr1 = 1) Then
If (TmDt0 >= Fromdt1 And TmDt0 <= Todt1) Then
Me!Rm2txt1.BackColor = vbRed
End If
End If
ob.MoveNext
Cntr1 = 1
Wend
ob.Close
Set ob = Nothing
Dim ob1 As ADODB.Recordset
Set ob1 = New ADODB.Recordset
Ob1.activeconnection = CurrentProject.Connection
Ob1.Open "Booked"
Cntr1 = 0
While Not ob1.EOF
Room1 = ob1.Fields(1).Value
Fromdt1 = ob1.Fields(2).Value
Todt1 = ob1.Fields(3).Value
Todt1 = DateAdd("d", 1, [Todt1])
If (Room1 = 2 And Cntr1 = 0) Then
If (TmDt0 >= Fromdt1 And TmDt0 <= Todt1) Then
Me!Rm2txt1.BackColor = vbBlue
Else
Me!Rm2txt1.BackColor = vbGreen
End If
ElseIf (Room1 = 2 And Cntr1 = 1) Then
If (TmDt0 >= Fromdt1 And TmDt0 <= Todt1) Then
Me!Rm2txt1.BackColor = vbRed
End If
End If
Ob1.MoveNext
Cntr1 = 1
Wend
Ob1.Close
Set ob1 = Nothing
Last edited by a moderator: