While in recordset is not working (1 Viewer)

Anis

Registered User.
Local time
Today, 08:57
Joined
Nov 28, 2016
Messages
11
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.

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:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:57
Joined
Feb 28, 2001
Messages
26,999
This mass of code is hard to read. First use the code tags around your code. Second, try indenting the code by prefixing with a couple of spaces for each level of nesting. Sometimes this will help you with nesting problems.

Instead of

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

show us by using [ C O D E ] and [ / C O D E ] (but with no spaces) enclosing indented code.

Code:
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
 

Cronk

Registered User.
Local time
Tomorrow, 02:57
Joined
Jul 4, 2013
Messages
2,770
Your code sets Cntr1 to the value 1 in the line before the Wend statement.

This means the If part will never be executed after the first pass through the loop.
 

Anis

Registered User.
Local time
Today, 08:57
Joined
Nov 28, 2016
Messages
11
Your code sets Cntr1 to the value 1 in the line before the Wend statement.

This means the If part will never be executed after the first pass through the loop.

Thank you, but it was working fine for condition room=1.
when it goes to second while room=2 it just skip cntr1=0 and execute only cntr1=1
 

MarkK

bit cruncher
Local time
Today, 08:57
Joined
Mar 17, 2004
Messages
8,178
Yes, this is what Cronk is saying. Look at your code here...
Code:
      ...
   End If
   ob.MoveNext
[COLOR="Blue"]   Cntr1 = 1[/COLOR]
Wend
...
Cntr1, after the first loop, is always equal to 1. It is never changed anywhere, and so after the first loop your first If block will never execute.
hth
 

Anis

Registered User.
Local time
Today, 08:57
Joined
Nov 28, 2016
Messages
11
Yes, this is what Cronk is saying. Look at your code here...
Code:
      ...
   End If
   ob.MoveNext
[COLOR="Blue"]   Cntr1 = 1[/COLOR]
Wend
...
Cntr1, after the first loop, is always equal to 1. It is never changed anywhere, and so after the first loop your first If block will never execute.
hth

But before starting the second record set I had assigned cntr1 as 0. Even I Try the same with diffrent cntr2 also. But it shows the same result.

Thank you.
 

MarkK

bit cruncher
Local time
Today, 08:57
Joined
Mar 17, 2004
Messages
8,178
Try adding this line and see what happens...
Code:
...
While Not ob.EOF
[COLOR="Blue"]   msgbox Cntr1[/COLOR]
   Room1 = ob.Fields(1).Value
   ...
The value will be 1 for the first loop, then it will be zero for every other loop.
hth
 

Users who are viewing this thread

Top Bottom