Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

Changing the reservation time contents of a list box to the available times for that day

I want to make a reservation. I type in the date and then click an Ok button to select the reservation time from a list field. But I want the reservation time list box to display the available times to choose for that day (not times that are already reserved or booked).

The table is called Mare-Status, data control called datProcessStatus, reservation date field called ReservationDate, reservation time field called ReservationTime. The reservation times to select from the list box are (if no times where yet selected or no reservation took place for that day) 09:00, 10:00, 11:00, 12:00, 13:00, 14:00, 15:00, 16:00, 17:00, 18:00.
0
willwatters
Asked:
willwatters
  • 7
  • 3
1 Solution
 
trkcorpCommented:
Well, obviously you need to validate your list box entries against your reserved times and cull those that have been used.  Without more details about your data and structures an answer is hard to provide.  But basically, upon entry of the date the time list should be built.  In the date field validation event or lost focus event you will need to query the available time slots.  I would leave the time list empty until I knew what date I was addressing as well...
0
 
willwattersAuthor Commented:
I have started the following code to try and solve this problem. Will you please complete what I have stated as comments, thank you.

Dim ReservationDate As Date
ReservationDate = MonthViewReservationDate 'The selected date

datProcessStatus.RecordSource = "Select [ReservationTime] from MareStatus Where [ReservationDate] ='" & ReservationDate & "'"
datProcessStatus.Refresh
If datProcessStatus.Recordset.RecordCount = 0 Then
    'Put all the time slots (09:00, 10:00 .... 18:00) in lstReservationTime
Else
    frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.MoveFirst
    Do While Not frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.EOF
        If (frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.Fields("ReservationDate") = frmMakeReservation.MonthViewReservationDate) Then
            'Add the time slots (from 09:00 - 18:00 (1hr intervals)) that are not already booked for this day in the reservation time list box (lstReservationTime)
        End If
        frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.MoveNext
    Loop
End If
0
 
trkcorpCommented:
Why don't you try something like this (1) setting the data control recordsource a little differently:

datProcessStatus.RecordSource = "Select [ReservationTime] from MareStatus Where [ReservationDate] ='" & ReservationDate & "' ORDER BY ?TIME_SLOT?"

Where ?TIME_SLOT? is a field whose name I don't know...  Then in the ELSE portion of your IF statement code something lke this:

Dim ix As Integer
Dim sWk As String

frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.MoveFirst
For ix = 1 To 24
   sWk = Format$(x, "0#") & ":00"  'Will be 01:00, 02:00, 03:00, etceteras...
   If Not frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.EOF Then
      If sWk < frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.Fields("?TIME_SLOT?") Then
         'Add the time slot sWk
      Else 'it will be equal because of the new ORDER BY clause
         frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.MoveNext
      End If
   Else
      'Add the time slot sWk
   End If
Next
End Sub

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
willwattersAuthor Commented:
Ive ammended the above code, but it is still not corret. Time slot is not a field. A list box on the form is called lstReservationTime which should contain the relevant times for a day if they are not already booked up for that day. The times for the list box if not already booked for that day are: 09:00, 10:00, 11:00, 12:00, 13:00, 14:00, 15:00, 16:00, 17:00, 18:00. When a booking is made the reservation date is stored in the field ReservationDate and the reservation time is stored in the field ReservationTime in the MareStatus table.

The amended code is displayed below, will you please complete it and correct if incorrect. The main problem is trying to get the relevant times added to the list box - lstReservationTime

datProcessStatus.RecordSource = "Select * from MareStatus Where [ReservationDate] ='" & ReservationDate & "' ORDER BY ReservationTime"
datProcessStatus.Refresh
If datProcessStatus.Recordset.RecordCount = 0 Then
'Put 09:00, 10:00, 11:00, 12:00, 13:00, 14:00, 15:00, 16:00, 17:00, 18:00 in the list box lstReservationTime
Else
    Dim sWk As String
    Dim NewTime As Date
    Dim OldTime As Date
    Dim CorrectTime As String
    OldTime = "08:00"

    frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.MoveFirst
    For ix = 1 To 10
        NewTime = DateAdd("n", 60, OldTime)
       
        'Format time into hh:mm
        CorrectTime = Format$(NewTime, "hh:mm")
        sWk = CorrectTime  'Will be 09:00, 10:00, 11:00, etceteras...
        If Not frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.EOF Then
            If sWk < frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.Fields("ReservationTime") Then
                'Add the time slot sWk to the list box lstReservationTime
            Else 'it will be equal because of the new ORDER BY clause
                frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.MoveNext
            End If
        Else
            'Add the time slot sWk to the list box lstReservationTime
        End If
    Next
End If
0
 
trkcorpCommented:
How about this:
Dim sWk As String

datProcessStatus.RecordSource = "Select * from MareStatus Where [ReservationDate] ='" & ReservationDate & "' ORDER BY ReservationTime"
datProcessStatus.Refresh
If datProcessStatus.Recordset.RecordCount = 0 Then
'Put 09:00, 10:00, 11:00, 12:00, 13:00, 14:00, 15:00, 16:00, 17:00, 18:00 in the list box lstReservationTime
   For ix = 9 To 18
      sWk = Format$(x, "0#") & ":00"  'Will be 09:00 to 18:00
      lstReservationTime.AddItem sWk
   Next
Else
   frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.MoveFirst
   For ix = 9 To 18
       sWk = Format$(x, "0#") & ":00"  'Will be 09:00 to 18:00
       If Not frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.EOF Then
           If sWk < frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.Fields("ReservationTime") Then
               lstReservationTime.AddItem sWk
           Else 'it will be equal because of the new ORDER BY clause
               frmProcessStatusRecordProcessingActions.datProcessStatus.Recordset.MoveNext
           End If
       Else
           lstReservationTime.AddItem sWk
       End If
   Next
End If
0
 
trkcorpCommented:
Don't forget to DIM ix as Interger...
0
 
trkcorpCommented:
Format$(x, "0#") & ":00"  
SHOULD READ:
Format$(ix, "0#") & ":00"
0
 
trkcorpCommented:
??What's happening here??
0
 
willwattersAuthor Commented:
Wonderful work. Thanks very much!!!
0
 
trkcorpCommented:
U R Welcome.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now