?
Solved

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

Posted on 2003-03-27
10
Medium Priority
?
151 Views
Last Modified: 2010-04-07
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
Comment
Question by:willwatters
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 4

Expert Comment

by:trkcorp
ID: 8218178
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
 

Author Comment

by:willwatters
ID: 8242186
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
 
LVL 4

Expert Comment

by:trkcorp
ID: 8246357
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:willwatters
ID: 8255055
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
 
LVL 4

Expert Comment

by:trkcorp
ID: 8256505
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
 
LVL 4

Expert Comment

by:trkcorp
ID: 8256519
Don't forget to DIM ix as Interger...
0
 
LVL 4

Expert Comment

by:trkcorp
ID: 8256825
Format$(x, "0#") & ":00"  
SHOULD READ:
Format$(ix, "0#") & ":00"
0
 
LVL 4

Accepted Solution

by:
trkcorp earned 140 total points
ID: 8271110
??What's happening here??
0
 

Author Comment

by:willwatters
ID: 8349740
Wonderful work. Thanks very much!!!
0
 
LVL 4

Expert Comment

by:trkcorp
ID: 8350936
U R Welcome.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question