MS Access Calendar - Need help with parameters

[GratefulSteve]

I am using Access 2013 with Windows 7.

I am modifying this calendar:

https://bytes.com/topic/access/answers/761255-ms-access-calendar

I am getting a "Too few parameters - Expected 5" error message.

Are any other users of this calendar available to help with some modifications?

I am tracking Goals by date, not Events.

It appears that there hasn't been any activity with this app in a few years. I am hoping that ADezii is still involved with it.

Sidebar: I have spent countless hours looking for a form-based calendar that would, among other criteria, not be dependent on mscal.ocx or ActiveX controls. This calendar is fantastic!

Thank you,

Steve

[PhilOfWalton]

This screenshot is of a calendar I wrote years ago. Any help?

Phil

[GratefulSteve]

Hi Phil,

Looks great! Is that a variation of:

https://bytes.com/topic/access/answe...ccess-calendar

If so, I could use a couple of pointers.

Thank you.

Steve

[PhilOfWalton]

Regret my Calendar is completely different. It consists of 42 continuous subfoms which (as the picture shows) allows different colours to represent different kinds of event (e.g. Blue - Social, green - work, brown - Club Committee meetings). The main problem is that it takes about 2 seconds to load a month as there are a lot of subforms to fill.

Phil

[ADezii]

Can you Upload your Database stripped of any sensitive Data along with a detailed explanation of exactly what is not working?

[GratefulSteve]

Hi ADezii,

Thank you for your reply.

Absolutely. I very much appreciate your help.

I will be able to strip the database later this evening. I'm in Oregon (PDT). I will upload it as soon as it is ready, along with a detailed explanation.

Thanks again!

Steve

[GratefulSteve]

Hi ADezii,

I have stripped the db.

The original calendar displays a datasheet under the calendar. Instead of displaying a datasheet, I want to doubleclick on a date and open a form (DailyGoals). I also want to display the data, for each of the following fields, within each respective date (DayBlock) on the Calendar:

Calendar.NPGoal
Calendar.PVGoal
Calendar.ROFArrived
Calendar.CPRArrived
Calendar.REVGoal

My Calendar table does not have any Start Time or End Time related fields.

I have tried to duplicate the changes that RockKandee made when you helped them, with limited success.

Currently, I am getting the following error, when I launch the Calendar:

Item not found in this collection.

I could use some help with the SELECT statements, given the fields in my Calendar table.

Thank you, once again,

Steve

[ADezii]

I am extremely busy at work right now, but as soon as I get a chance, I'll be happy to look at the Database.

[GratefulSteve]

Hi ADezii,

I completely understand. I appreciate any help you can lend, very much.

I have made progress and am attaching the latest version.

Everything seems to be working fine. The subform populates correctly.

The one thing that I still haven't been able to figure out is populating each date with:

CalendarQ.NPGoal
CalendarQ.PVGoal
CalendarQ.ROFArrived
CalendarQ.CPRArrived
CalendarQ.REVGoal

I believe it has to do with the fact that I do not have a Start Time, End Time or Title.

Once I am able to display the data, I want to be able to double click the date and launch the DailyGoals form, for that respective date.

Once again, I appreciate any coaching you can provide and am very patient. :-)

Take care,

Steve

[ADezii]

The one thing that I still haven't been able to figure out is populating each date...

Here is a partial Code Segment that should at least point you in the right direction:

'CODE INTENTIONALLY OMITTED
Set db = CurrentDb
 
strSQL = "SELECT Calendar.CalendarID, Calendar.DateTime, Calendar.NPGoal, Calendar.PVGoal, Calendar.ROFArrived, " & _
         "Calendar.CPRArrived, Calendar.REVGoal FROM Calendar WHERE Calendar.DateTime BETWEEN %F And %L ORDER BY " & _
         "Calendar.CalendarID"
strSQL = Replace(strSQL, "%F", lngFirstOfMonth)
strSQL = Replace(strSQL, "%L", lngLastOfMonth)
 
Set rstEvents = db.OpenRecordset(strSQL)        'Added 4/16/2008
 
With rstEvents
  Do While Not .EOF
    'CFB added 2-18-10
    lngFirstDateInRange = ![DateTime]        '<Substitute for [Start Date], if Date Range>
    If lngFirstDateInRange < lngFirstOfMonth Then
      lngFirstDateInRange = lngFirstOfMonth
    End If
    lngLastDateInRange = ![DateTime]         '<Substitute for [End Date], if Date Range>
    If lngLastDateInRange > lngLastOfMonth Then
      lngLastDateInRange = lngLastOfMonth
    End If
 
    For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
      bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
      bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
        If astrCalendarBlocks(bytBlockCounter) = "" Then      'no existing Text in Array
          astrCalendarBlocks(bytBlockCounter) = "NPGoal: " & rstEvents![NPGoal] & vbCrLf & "PVGoal: " & rstEvents![PVGoal] & _
                                                 vbCrLf & "ROFArrived: " & rstEvents![ROFArrived] & vbCrLf & "CPRArrived: " & _
                                                 rstEvents![CPRArrived] & vbCrLf & "REVGoal: " & rstEvents![REVGoal]
        Else
'CODE INTENTIONALLY OMITTED
 

[GratefulSteve]

Thank you, ADezii.

Currently, the code looks like this:

Set db = CurrentDb
 
'CFB added 2-18-10
'<Substitute your own SQL Statement here>
strSQL = "SELECT Calendar.[CalendarID], Calendar.[DateTime], Calendar.[NPGoal], Calendar.[PVGoal], Calendar.[ROFArrived], " & _
         "Calendar.[CPRArrived], Calendar.[REVGoal] FROM Calendar WHERE Calendar.DateTime BETWEEN %F And %L ORDER BY " & _
         "Calendar.[CalendarID]"
strSQL = Replace(strSQL, "%F", lngFirstOfMonth)
strSQL = Replace(strSQL, "%L", lngLastOfMonth)
 
Set rstEvents = db.OpenRecordset(strSQL)    'Added 4/16/2008
 
 
With rstEvents
Do While Not .EOF
  'CFB added 2-18-10
    lngFirstDateInRange = ![DateTime]        '<Substitute for [Start Date], if Date Range>
    If lngFirstDateInRange < lngFirstOfMonth Then
      lngFirstDateInRange = lngFirstOfMonth
   End If
  lngLastDateInRange = ![DateTime]         '<Substitute for [End Date], if Date Range>
 If lngLastDateInRange > lngLastOfMonth Then
      lngLastDateInRange = lngLastOfMonth
    End If
 
    For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
      bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
      bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
 
 
        If astrCalendarBlocks(bytBlockCounter) = "" Then      'no existing Text in Array
          astrCalendarBlocks(bytBlockCounter) = "NPGoal: " & rstEvents![NPGoal] & vbCrLf & "PVGoal: " & rstEvents![PVGoal] & _
                                                 vbCrLf & "ROFArrived: " & rstEvents![ROFArrived] & vbCrLf & "CPRArrived: " & _
                                                 rstEvents![CPRArrived] & vbCrLf & "REVGoal: " & rstEvents![REVGoal]
        Else
        strStartTime = Format$(rstEvents![Start Time], "Short Time")       '<Substitute for [Start Time]>
        'strStartTime = Format$(rstEvents![Start Time], "h:mm AM/PM")       '<Substitute for [Start Time]>
      End If
                                              '<Substitute for [Title]>
      If astrCalendarBlocks(bytBlockCounter) = "" Then
          astrCalendarBlocks(bytBlockCounter) = "NPGoal: " & rstEvents![NPGoal] & vbCrLf & "PVGoal: " & rstEvents![PVGoal] & _
                                                 vbCrLf & "ROFArrived: " & rstEvents![ROFArrived] & vbCrLf & "CPRArrived: " & _
                                                 rstEvents![CPRArrived] & vbCrLf & "REVGoal: " & rstEvents![REVGoal]
'        astrCalendarBlocks(bytBlockCounter) = rstEvents![Title] & vbNewLine & strStartTime
 
      Else                                    '<Substitute for [Title]>
'       astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
'                                              rstEvents![Title] & vbNewLine & strStartTime
      End If
  Next lngEachDateInRange
  'End of CFB added 2-18-10
 
    rstEvents.MoveNext
Loop
 

I am getting a "Compile error: Expected End With" message.

When you have a moment, could you please point to where this is bombing?


Also, in version 2, I was pulling from the CalendarQ query. You pulled directly from the Calendar table. Is there any reason for not pulling from the query?

Thank you for your continuing help, ADezii. :-)

Steve

[ADezii]

I am getting a "Compile error: Expected End With" message.

You are only looking at a 'Code Segment' in PopulateCalendar() and not the Code in its entirety.

Also, in version 2, I was pulling from the CalendarQ query. You pulled directly from the Calendar table. Is there any reason for not pulling from the query?

None
P.S. - Be patient and I'll have a working Demo for you in a day or two.

[GratefulSteve]

Thank you, ADezii!

In the meantime, I will continue to hammer away at it.

My coding skills are 20+ years old and I'm grappling with If, Then Else logic. At my age, being the student is a welcome state. :-)

If I am able to show progress, I will post Version 3.

My patience will meet, or exceed, your expertise and efforts to help with this. :-)

Thank you, once again, for helping with this.

Steve

[ADezii]

1. Rather than work within the context of your Database, I found it easier to create a New Database and Import the key Components into it, namely: the Calendar Table and DailyGoals Form. It now becomes a simple matter to integrate this new functionality into your Database by simply Importing frmCalendar and the Code Modules into it.
2. When you Open the Demo you will see frmCalendar displayed along with your requested key Fields for each Day Block.
3. Dbl-Clicking on a Day Block/Date will open DailyGoals with Data for that specific Date.
4. Make any changes in DailyGoals then click the Close Button in the lower left corner.
5. To see any changes that you made on the Calendar, click on the Sync Button in the lower left corner.
6. If you are not interested in seeing these changes immediately, you can move to any other Month or Year and return to the modified Date. The Calendar will now be in sync in the Calendar Table.
7. The ListBox functionality on the bottom of frmCalendar has been disabled since I saw no need for it in this current scenario.
8. This is the essence of the Access Calendar. Download the Attachment and good luck with your Project.

P.S. - If you would like to have frmCalendar reflect any changes made on DailyGoals immediately you can Call the PopulateCalendar() Routine within the DblClick() Event of each Day Block. This will work since DailyGoals will be opened in Dialog Mode and the line of Code after the OpenForm() Method will not execute until after the Form is closed. Sorry if my explanation is confusing, but here is an example for Day Block #7:

Private Sub txtDayBlock07_DblClick(Cancel As Integer)
  DoCmd.OpenForm "DailyGoals", acNormal, , "[DateTime] = #" & CDate(Me![txtDayBlock07].Tag) & "#", acFormEdit, acDialog
    PopulateCalendar
End Sub

[GratefulSteve]

Thank you AZDezii!

I am in a long meeting this morning. I look forward to working on this, immediately after my meeting.

You are the best.

Your reputation, at bytes.com, as generous and knowledgeable are highly deserved.

Thank you!!

Steve

[ADezii]

Thank you Steve for the kind compliments. I'll check in again tomorrow to see how you made out - off to work right now.

[GratefulSteve]

Hi ADezii,

You nailed it! Your revised version resolved all of my issues, taught me much and added some things that I hadn't considered.

I can't thank you enough for your generosity and help.

As I went through the revised db and looked at how you did things that I had spent many hours struggling with, I felt like I was opening a Christmas present.

Thank you very much!

Take care,

Steve

[ADezii]

You are quite welcome, good luck with your Project.

[ADezii]

I had some spare time and I decided to increase the functionality of the Calendar by utilizing the List Box at the bottom of frmCalendar. It was there anyway just taking up space, so why not use it? (LOL).

[GratefulSteve]

Thank you very much, ADezii.

You continue to amaze!

I am attaching the latest version of the calendar. I was able to fit all 10 categories within each date.

With this in place, I'm not sure whether to add the datasheet back in. What do you think?

I am attaching a screen capture.

I couldn't have done it without your help.

Thank you, again.

Steve

[ADezii]

I couldn't have done it without your help

I am sure that you could have, just would have taken a little longer. Project looks great - we are always here should you need us.