For Next Loop

[poucedeleon]

I have a form with 34 text boxes on it. (These represent each visit booth available.) When the form opens it only looks at the current day. I would like to have a code that loops through each visit for the day and hour and turns the textboxes red and adds the text “FULL” as visits are scheduled. I have a For Next loop that sets the text boxes gray On Load but I am not sure how to write code to look at today’s date and the hour on the recordset and count the visit booths (Textboxes) I think If I got a starting point I could figure it out. Would appreciate any help.

Also some time video booths break and are down. Should I create a table for the booths so I can mark them as broken and take them out of the count, or can this be done in the code?

tblInput
See attached image

 

[ajetrumpet]

use the backcolor property to turn the textboxes.

current point in time for VBA = Now()

current date = Date()

now I think you can get it, no?

 

[poucedeleon]

Thanks Adam, the part I am lost on is how to count the records for each day and hour. I think I can get the formatting part once I can fiqure out how to count through the records. Below is the code I tried, but no luck.

For i = 1 To 34
 
If Me.Controls("CelCnt" & i) = Null Then
      Me.Controls("Booth" & i).BackColor = #A5A5A5 'Medium Gray
      Me.Controls("TxtStatus" & i).BackColor = #A5A5A5 'Medium Gray
Else
      Me.Controls("InputDate" & i) = CurDay And _
      Me.Controls("InputTime" & i) = cboSortByTime 
      Me.Controls("CelCnt" & i).BackColor = #ED1C24'Red
      Me.Controls("CelCnt" & i).BackColor = #FD1A54'White
 
        End If
      Next i
 
   StrgSQL = "SELECT * FROM tblInput WHERE InputDate=" & CLng(Me.cboSortByTime.Column(1)) & " AND (Format([InputTime], 'h')=" & Me.cboSortByTime );"
                Set rst = CurrentDb.OpenRecordset(StrgSQL, dbOpenDynaset)

 

[ajetrumpet]

Thanks Adam, the part I am lost on is how to count the records for each day and hour.

I am too, unless I see a file. I might be able to fix it for you if I could manipulate it myself, but if you stay here, my help will be no good to you.

Below is the code I tried, but no luck.

[COLOR="Red"]you can't append a number to a string and expect an outcome.  change "i" to "cstr(i)"

also, "= NULL" is not really valid, but works sometimes.  Use "IsNull(EXPRESSION) = true/false"[/COLOR]

For i = 1 To 34 
 
If Me.Controls("CelCnt" & i) = Null Then
      Me.Controls("Booth" & i).BackColor = #A5A5A5 'Medium Gray
      Me.Controls("TxtStatus" & i).BackColor = #A5A5A5 'Medium Gray
Else
      Me.Controls("InputDate" & i) = CurDay And _
      Me.Controls("InputTime" & i) = cboSortByTime 
      Me.Controls("CelCnt" & i).BackColor = #ED1C24'Red
      Me.Controls("CelCnt" & i).BackColor = #FD1A54'White
 
        End If
      Next i
 
   StrgSQL = "SELECT * FROM tblInput WHERE InputDate=" & CLng(Me.cboSortByTime.Column(1)) & " AND (Format([InputTime], 'h')=" & Me.cboSortByTime );"
                Set rst = CurrentDb.OpenRecordset(StrgSQL, dbOpenDynaset)

 

[poucedeleon]

I have uploaded a copy of the database. I plan on splitting the db. The VisitOfc will be used in reception of the building and the VideoVisit will be used upstairs. They both hit the same data.
Thanks for the help.