Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10937 > unrolled thread

Calendar Problems

Back to article view | Back to comp.databases.ms-access

Contents

  Calendar Problems ray123pearson@gmail.com - 2015-07-18 17:02 -0700
    Re: Calendar Problems Larry Linson <larry.linson@verizon.net> - 2015-07-18 19:50 -0500
      Re: Calendar Problems "Phil" <phil@stantonfamily.co.uk> - 2015-07-19 09:06 +0100
    Re: Calendar Problems Mike P <mikewpayne@tiscali.co.uk> - 2015-07-19 05:00 -0700

#10937 — Calendar Problems

I have copied a calendar from another program which works very well, however it only allows for me to show a booking for a whole day. How do I show bookings for morning, afternoon and evening separately. Do I need three sets of boxes and can anyone show me how to do this.....I'm really only a newbie. I have attached the code as per below. Any help would be greatly appreciated.

Option Compare Database

Dim intFirst As Integer, intLast As Integer, intLastDay As Integer
Dim intMonth As Integer, intYear As Integer

Public Sub SetDaysAuditorium()
Dim intI As Integer, intJ As Integer, strNum As String

    ' First, clear all the boxes
    For intI = 1 To 42
        ' Controls are named "lblnn" and "txtnn"
        ' Where nn = 01 to 42
        ' Using Format to get 2 digits
        strNum = Format(intI, "00")
        ' Clear the day number
        Me("lbl" & strNum).Caption = ""
        ' Clear the contents of the text box
        Me("txt" & strNum).Value = ""
        ' Grey out the background
        Me("txt" & strNum).BackColor = 16737843
        
        ' And disable it
        Me.Text111.SetFocus
        Me("txt" & strNum).Enabled = False
    Next intI

    intMonth = Forms!frmCalendarMain!CboMonth
    intYear = Forms!frmCalendarMain!CboYear
    ' The first box to set is the weekday of the first day of the month
    intFirst = Weekday(DateSerial(intYear, intMonth, 1), vbSunday)
    ' Calculate the last day number
    intLastDay = Day(DateAdd("m", 1, DateSerial(intYear, intMonth, 1)) - 1)
    ' .. and the last box to set
    intLast = intFirst + intLastDay - 1
   
    ' Now set up all the boxes for the current month
    intJ = 1
    For intI = intFirst To intLast
        strNum = Format(intI, "00")
        ' Put the day number in the associated label caption
        Me("lbl" & strNum).Caption = intJ
        ' If the day is a weekend then make the background of the box blue
        
        If (Weekday(DateSerial(intYear, intMonth, intJ), vbMonday) > 5) Then
            Me("txt" & strNum).BackColor = 16763904 'Blue
        ElseIf (Weekday(DateSerial(intYear, intMonth, intJ))) = Date Then
            Me("txt" & strNum).BackColor = 65535 'Yellow
        Else: Me("txt" & strNum).BackColor = 16777215
        End If
                
    Dim rsHoliday As Recordset, sqlHoliday As String
    Dim iHoliday As Integer
    Dim dHoliday(1 To 10) As Date
      
    sqlHoliday = "SELECT * FROM [tblholiday] " & "WHERE (DatePart('m', Holiday) = " & intMonth & _
             " AND DatePart('yyyy', Holiday) = " & intYear & ");"
                 
    Set rsHoliday = CurrentDb().OpenRecordset(sqlHoliday)

    iHoliday = 0

    Do While Not rsHoliday.EOF
        iHoliday = iHoliday + 1
        dHoliday(iHoliday) = rsHoliday!Holiday
        rsHoliday.MoveNext
    Loop

' Then the statement in the loop becomes:

    If iHoliday > 0 Then
             For nCounter = 1 To iHoliday
             
             If DateSerial(intYear, intMonth, intJ) = Date Then
                Me("txt" & strNum).BackColor = 65535  'Yellow
             ElseIf DateSerial(intYear, intMonth, intJ) = dHoliday(nCounter) Then
                Me("txt" & strNum).BackColor = 39423 'Orange
             ElseIf Weekday(DateSerial(intYear, intMonth, intJ), vbMonday) > 5 Then
                Me("txt" & strNum).BackColor = 16763904 'Blue
             'ElseIf DateSerial(intYear, intMonth, intJ) = rsHoliday!Holiday("Closed") Then
                'Me("txt" & strNum).BackColor = 16711935 'Purple
             End If
                            
             Next nCounter
         End If
         
' end of holiday code
        
          ' and Enable it
        Me("txt" & strNum).Enabled = True
        intJ = intJ + 1
    Next intI
            
    ' Hide the last row if we didn't use it
    If intLast < 36 Then
        intJ = False
    Else
        intJ = True
    End If
    For intI = 36 To 42
        Me("txt" & intI).Visible = intJ
    Next intI
              
      ' Now call the procedure to insert the data for the current group
    cmbRoom_AfterUpdate
        
End Sub

Private Sub cmbRoom_AfterUpdate()

Dim DB As DAO.Database, rst As DAO.Recordset
Dim datFirstDate As Date, datLastDate As Date, strSQL As String
Dim intStart As Integer, intEnd As Integer, intI As Integer, strNum As String

    ' Clear all the text boxes
    For intI = 1 To 42
        strNum = Format(intI, "00")
        Me("txt" & strNum).Value = ""
    Next intI
        
    ' Get a pointer to the current database
    Set DB = CurrentDb
    
    ' Calculate the start and end date for the current month
    datFirstDate = DateSerial(intYear, intMonth, 1)
    datLastDate = DateSerial(intYear, intMonth, intLastDay)
    
    ' Set up to select the guest bookings that are in the current month
    
     strSQL = "SELECT * FROM qCalEvents " & _
    "WHERE (Venue = 1 or Venue = 7 or Venue = 5 ) AND BeginDate <= #" & Format([datLastDate], "mm/dd/yy") & _
    "# And EndingDate >= #" & Format([datFirstDate], "mm/dd/yyyy") & "#"
    
     Set rst = DB.OpenRecordset(strSQL)
    
    'Loop through all the rows and set the Hirer names in the calendar
    Do Until rst.EOF
        ' Calculate the start point
        If rst!BeginDate < datFirstDate Then
            intStart = intFirst
        Else
            intStart = Day(rst!BeginDate) + intFirst - 1
        End If
        ' Calculate the end point
        If rst!EndingDate > datLastDate Then
            intEnd = intLast
        Else
            intEnd = Day(rst!EndingDate) + intFirst - 1
        End If
        
        ' Set in the Reservation Number & ID in the appropriate days
        For intI = intStart To intEnd
            strNum = Format(intI, "00")
                             
        If rst!Confirmed = 1 Then
         Me("txt" & strNum).BackColor = 255
         Me("txt" & strNum).ForeColor = 0
        ElseIf rst!Confirmed = -1 Then
         Me("txt" & strNum).BackColor = 65280
         Me("txt" & strNum).ForeColor = 0
        End If
         
         'To show the name of the event
         'Me("txt" & strNum).Value = Me("txt" & strNum).Value & rst!Abbreviation & vbCrLf
         
          'To show the Hirer ID and Abbreviation
        Me("txt" & strNum).Value = Me("txt" & strNum).Value & _
                rst!HirerID & " / " & rst!Abbreviation & vbCrLf
                             
                  
        Next intI
        
        ' Get the next row
        rst.MoveNext
    Loop
  
    rst.Close
    
End Sub

[toc] | [next] | [standalone]

#10938

If there is a category of Controls, Procedures, and Objects that has
more different ones "floating around" than calendar or date-pickers,
I don't know what that category might be. It's more than a little
difficult to debug or assist in debugging remotely without knowing
specifically what we're dealing with.

It's also a problem that we have no description of how you've defined
your data for "bookings". Perhaps the kind of bookings can only
possibly occur in the increments you mention, but is it possible you
might need more flexibility?

If you are using a more recent version, Access has its own Calendar
Control. Sounds to me as if you set up a file with a Booking
Description, a booking date, and a booking time (which could be
Morning, Afternoon, or Evening, not necessarily an hour of the day),
you could set each booking from the same form. That's certainly what
I'd have done if I were creating an application, so the question of
whether more than one date-picker Conntrol was needed would never have
arisen.

 Larry Linson
 Microsoft Access MVP 2003 - 2014
 Co-author, Microsoft Access Small Business Solutions, 
   pub by Wiley, 2010




On Sat, 18 Jul 2015 17:02:20 -0700 (PDT), ray123pearson@gmail.com
wrote:

>I have copied a calendar from another program which works very well, however it only allows for me to show a booking for a whole day. How do I show bookings for morning, afternoon and evening separately. Do I need three sets of boxes and can anyone show me how to do this.....I'm really only a newbie. I have attached the code as per below. Any help would be greatly appreciated.
>
>Option Compare Database
>
>Dim intFirst As Integer, intLast As Integer, intLastDay As Integer
>Dim intMonth As Integer, intYear As Integer
>
>Public Sub SetDaysAuditorium()
>Dim intI As Integer, intJ As Integer, strNum As String
>
>    ' First, clear all the boxes
>    For intI = 1 To 42
>        ' Controls are named "lblnn" and "txtnn"
>        ' Where nn = 01 to 42
>        ' Using Format to get 2 digits
>        strNum = Format(intI, "00")
>        ' Clear the day number
>        Me("lbl" & strNum).Caption = ""
>        ' Clear the contents of the text box
>        Me("txt" & strNum).Value = ""
>        ' Grey out the background
>        Me("txt" & strNum).BackColor = 16737843
>        
>        ' And disable it
>        Me.Text111.SetFocus
>        Me("txt" & strNum).Enabled = False
>    Next intI
>
>    intMonth = Forms!frmCalendarMain!CboMonth
>    intYear = Forms!frmCalendarMain!CboYear
>    ' The first box to set is the weekday of the first day of the month
>    intFirst = Weekday(DateSerial(intYear, intMonth, 1), vbSunday)
>    ' Calculate the last day number
>    intLastDay = Day(DateAdd("m", 1, DateSerial(intYear, intMonth, 1)) - 1)
>    ' .. and the last box to set
>    intLast = intFirst + intLastDay - 1
>   
>    ' Now set up all the boxes for the current month
>    intJ = 1
>    For intI = intFirst To intLast
>        strNum = Format(intI, "00")
>        ' Put the day number in the associated label caption
>        Me("lbl" & strNum).Caption = intJ
>        ' If the day is a weekend then make the background of the box blue
>        
>        If (Weekday(DateSerial(intYear, intMonth, intJ), vbMonday) > 5) Then
>            Me("txt" & strNum).BackColor = 16763904 'Blue
>        ElseIf (Weekday(DateSerial(intYear, intMonth, intJ))) = Date Then
>            Me("txt" & strNum).BackColor = 65535 'Yellow
>        Else: Me("txt" & strNum).BackColor = 16777215
>        End If
>                
>    Dim rsHoliday As Recordset, sqlHoliday As String
>    Dim iHoliday As Integer
>    Dim dHoliday(1 To 10) As Date
>      
>    sqlHoliday = "SELECT * FROM [tblholiday] " & "WHERE (DatePart('m', Holiday) = " & intMonth & _
>             " AND DatePart('yyyy', Holiday) = " & intYear & ");"
>                 
>    Set rsHoliday = CurrentDb().OpenRecordset(sqlHoliday)
>
>    iHoliday = 0
>
>    Do While Not rsHoliday.EOF
>        iHoliday = iHoliday + 1
>        dHoliday(iHoliday) = rsHoliday!Holiday
>        rsHoliday.MoveNext
>    Loop
>
>' Then the statement in the loop becomes:
>
>    If iHoliday > 0 Then
>             For nCounter = 1 To iHoliday
>             
>             If DateSerial(intYear, intMonth, intJ) = Date Then
>                Me("txt" & strNum).BackColor = 65535  'Yellow
>             ElseIf DateSerial(intYear, intMonth, intJ) = dHoliday(nCounter) Then
>                Me("txt" & strNum).BackColor = 39423 'Orange
>             ElseIf Weekday(DateSerial(intYear, intMonth, intJ), vbMonday) > 5 Then
>                Me("txt" & strNum).BackColor = 16763904 'Blue
>             'ElseIf DateSerial(intYear, intMonth, intJ) = rsHoliday!Holiday("Closed") Then
>                'Me("txt" & strNum).BackColor = 16711935 'Purple
>             End If
>                            
>             Next nCounter
>         End If
>         
>' end of holiday code
>        
>          ' and Enable it
>        Me("txt" & strNum).Enabled = True
>        intJ = intJ + 1
>    Next intI
>            
>    ' Hide the last row if we didn't use it
>    If intLast < 36 Then
>        intJ = False
>    Else
>        intJ = True
>    End If
>    For intI = 36 To 42
>        Me("txt" & intI).Visible = intJ
>    Next intI
>              
>      ' Now call the procedure to insert the data for the current group
>    cmbRoom_AfterUpdate
>        
>End Sub
>
>Private Sub cmbRoom_AfterUpdate()
>
>Dim DB As DAO.Database, rst As DAO.Recordset
>Dim datFirstDate As Date, datLastDate As Date, strSQL As String
>Dim intStart As Integer, intEnd As Integer, intI As Integer, strNum As String
>
>    ' Clear all the text boxes
>    For intI = 1 To 42
>        strNum = Format(intI, "00")
>        Me("txt" & strNum).Value = ""
>    Next intI
>        
>    ' Get a pointer to the current database
>    Set DB = CurrentDb
>    
>    ' Calculate the start and end date for the current month
>    datFirstDate = DateSerial(intYear, intMonth, 1)
>    datLastDate = DateSerial(intYear, intMonth, intLastDay)
>    
>    ' Set up to select the guest bookings that are in the current month
>    
>     strSQL = "SELECT * FROM qCalEvents " & _
>    "WHERE (Venue = 1 or Venue = 7 or Venue = 5 ) AND BeginDate <= #" & Format([datLastDate], "mm/dd/yy") & _
>    "# And EndingDate >= #" & Format([datFirstDate], "mm/dd/yyyy") & "#"
>    
>     Set rst = DB.OpenRecordset(strSQL)
>    
>    'Loop through all the rows and set the Hirer names in the calendar
>    Do Until rst.EOF
>        ' Calculate the start point
>        If rst!BeginDate < datFirstDate Then
>            intStart = intFirst
>        Else
>            intStart = Day(rst!BeginDate) + intFirst - 1
>        End If
>        ' Calculate the end point
>        If rst!EndingDate > datLastDate Then
>            intEnd = intLast
>        Else
>            intEnd = Day(rst!EndingDate) + intFirst - 1
>        End If
>        
>        ' Set in the Reservation Number & ID in the appropriate days
>        For intI = intStart To intEnd
>            strNum = Format(intI, "00")
>                             
>        If rst!Confirmed = 1 Then
>         Me("txt" & strNum).BackColor = 255
>         Me("txt" & strNum).ForeColor = 0
>        ElseIf rst!Confirmed = -1 Then
>         Me("txt" & strNum).BackColor = 65280
>         Me("txt" & strNum).ForeColor = 0
>        End If
>         
>         'To show the name of the event
>         'Me("txt" & strNum).Value = Me("txt" & strNum).Value & rst!Abbreviation & vbCrLf
>         
>          'To show the Hirer ID and Abbreviation
>        Me("txt" & strNum).Value = Me("txt" & strNum).Value & _
>                rst!HirerID & " / " & rst!Abbreviation & vbCrLf
>                             
>                  
>        Next intI
>        
>        ' Get the next row
>        rst.MoveNext
>    Loop
>  
>    rst.Close
>    
>End Sub

[toc] | [prev] | [next] | [standalone]

#10939

On 19/07/2015 01:50:26, Larry Linson wrote:
> If there is a category of Controls, Procedures, and Objects that has
> more different ones "floating around" than calendar or date-pickers,
> I don't know what that category might be. It's more than a little
> difficult to debug or assist in debugging remotely without knowing
> specifically what we're dealing with.
> 
> It's also a problem that we have no description of how you've defined
> your data for "bookings". Perhaps the kind of bookings can only
> possibly occur in the increments you mention, but is it possible you
> might need more flexibility?
> 
> If you are using a more recent version, Access has its own Calendar
> Control. Sounds to me as if you set up a file with a Booking
> Description, a booking date, and a booking time (which could be
> Morning, Afternoon, or Evening, not necessarily an hour of the day),
> you could set each booking from the same form. That's certainly what
> I'd have done if I were creating an application, so the question of
> whether more than one date-picker Conntrol was needed would never have
> arisen.
> 
> Larry Linson
> Microsoft Access MVP 2003 - 2014
> Co-author, Microsoft Access Small Business Solutions,
> pub by Wiley, 2010
> 
> 
> 
Is this what you want?

see https://www.flickr.com/photos/philofwaltonspics/

look at the bottop left hand picture

Phil



---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

[toc] | [prev] | [next] | [standalone]

#10940

Hello Ray,

Before embarking on this exercise, consider your role.
If this is the start of a new responsibility you would be well advised to read a lot in order to fill the gaps in your knowledge.
If this is a one-off requirement with no anticipation of other 'jobs' then the easiest route to a solution is probably desirable.
The problem with adapting another solution is that you (we) have to determine what that solution does, which part is relevant to the required solution and how to change the code to produce the required solution.
In short, it is very often easier to start afresh. Determine the exact requirement. This appears to be a booking system for a hire company.

Assuming that the easiest route is needed, without any warranty for its 'fit for purpose', my suggestion is:-

Insert a byte number field in the events table. Call it HireSlot. You could use any numeric data type but byte will be sufficient for 3 options.
In the form that uses the events table, create an 'option group' control and bind it to the HireSlot field.
How?
Use label names: Morning, Afternoon, Evening.
Choose whichever option is the most likely as the default or choose no default.
Accept the values 1, 2 and 3.
Accept the defaults for the option group control.
Enter 'Hire Slot' as the caption for the option group or whatever you prefer.
In the property sheet for the frame control, set the 'Control Source' in the Data tab to HireSlot, the new field in the record source for the form.

If you need a hire slot for the start and end of the hire period then repeat the above; 2 new fields, 2 option groups called 'Hire Slot Start' and 'Hire Slot End'.

Suggestion offered without knowing your version of Access.

Mike P

19/7/15

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web