MS Access Calendar Populate from query with calculated field

[rstruthers]

I'm trying to populate the ms access caledar from a query with a calculated field here s the code from the query

SELECT HPContacts.FirstName,   HPContacts.LastName, HPContacts.LastContactDate+[DaysBeforeNextContact] AS [Next Contact]
FROM HPContacts
WHERE ((([HPContacts].[LastContactDate]+[DaysBeforeNextContact]) Is Not Null))
ORDER BY HPContacts.LastContactDate+[DaysBeforeNextContact];

I’ve not had any success getting calendar to populate from query (I’m just learning sql coding)
Any help would be appreciated

PS. Using Access 2003 on a Windows 7 machine.

[ADezii]

You picked a rather difficult Project for just learning VBA and SQL Coding, but we will give it a shot. The following changes to the PopulateCalendar() Sub-Routine will display the Last Name, First Initial of the First Name, as well as the Next Contract Date for each Contract Date, sorted by Last Name, First Name. Because of the complexity of this Procedure, I only listed a relevant portion of the Code and also Attached a Demo DB that I created for you. Pay special attention to Code Lines 3 to 9, 16, and 20. Any questions, feel free to ask.

Set db = CurrentDb
 
strSQL = "SELECT HP.FirstName, HP.LastName, HP.LastContractDate, " & _
         "HP.[DaysBeforeNextContact] " & _
         "FROM HPContacts AS HP WHERE [HP].[LastContractDate] is Not Null " & _
         "AND [HP].[LastContractDate] BETWEEN %F AND %L " & _
         "ORDER BY HP.LastName, HP.FirstName;"
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 = ![LastContractDate]
    If lngFirstDateInRange < lngFirstOfMonth Then
      lngFirstDateInRange = lngFirstOfMonth
    End If
    lngLastDateInRange = ![LastContractDate]
    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) = ![LastName] & ", " & Left$(![FirstName], 1) & "." & _
                                                " - [" & DateAdd("d", ![DaysBeforeNextContact], ![LastContractDate]) & "]"
        Else
          astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
                                                ![LastName] & ", " & Left$(![FirstName], 1) & "." & _
                                                " - [" & DateAdd("d", ![DaysBeforeNextContact], ![LastContractDate]) & "]"
      End If
    Next lngEachDateInRange
    'End of CFB added 2-18-10
      .MoveNext
Loop
End With

P.S. - I've also disabled the Events Listbox at the bottom of the Form since I felt that there was no need for it.

[rstruthers]

Thank You ADezii
I will work on DB this evening and let you know tomorrow how I’m progressing
Again thanks for the quick response
Ron

[ADezii]

P.S. - I forgot to mention that Code Line Numbers 28 to 35 control what Text is actually displayed in the Calendar for a given Date.

[rstruthers]

ADezii
I should have given a better detail on my first post. I would like just the first and last name to populate on the appropriate date when prospect should be contacted next (last contact date + Days Before Next Contact) I created a query (NextContact) that calculates the date the contact would populate on the calendar, I also changed the code in the populate calendar section to [LastContactDate] from [LastContractDate] this change has not changed how calendar runs. Also in the select statement can you use HP.LastName in lieu of HPContacts.Lastname if you have other tables that start with HP?
Here is the zipped file (Rstruthers_Calendar2) with added query
Ron

[ADezii]

The following Revised Code, will map the Contract Date + Days Before Next Contract to the Calendar, listing the Last Name, First Names. I did not have the time to Optimize it, and a Query with a Calculated Field is not needed to produce the desired results.

Set db = CurrentDb
 
strSQL = "SELECT HP.FirstName, HP.LastName, HP.LastContractDate, " & _
         "HP.[DaysBeforeNextContact] " & _
         "FROM HPContacts AS HP WHERE [HP].[LastContractDate] is Not Null " & _
         "AND ([LastContractDate]+[DaysBeforeNextContact]) BETWEEN %F AND %L " & _
         "ORDER BY HP.LastName, HP.FirstName;"
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 = (![LastContractDate] + ![DaysBeforeNextContact])
    If lngFirstDateInRange < lngFirstOfMonth Then
      lngFirstDateInRange = lngFirstOfMonth
    End If
    lngLastDateInRange = (![LastContractDate] + ![DaysBeforeNextContact])
    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) = ![LastName] & ", " & ![FirstName]
        Else
          astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
                                                ![LastName] & ", " & ![FirstName]
      End If
    Next lngEachDateInRange
    'End of CFB added 2-18-10
      .MoveNext
Loop
End With

[rstruthers]

ADezii
Thank You
For your time!! MUCH APPRECIATED
I made the code changes and copied calendar into DB working great!!!
I took a look at the widescreen version liked the scroll bar and the list box futures will try to code wide version to use in DB will let you know how successful I was
Again thanks for your help
Ron

[ADezii]

@rstruthers:
You are quite welcome, and we're here should you need us. You should pat yourself on the back, for someone who is just 'learning SQL Coding', you tackled a difficult Project with little difficulty at all.

[rstruthers]

I have the widescreen calendar working as well. While poking around in the code I saw the disabled double click code for each block, I have activated some blocks (ones with data) to play with, I changed the code to open a filtered (only names in that block) form with a command button to do a focused open of a form called call log. This works well but seems a little convoluted.

So here are my questions

1. Anyway of making block entries click aware? If so one could just click on a name to open the call log form. My gut tells me this might be out of the reach of this calendar form.

2. The other way might be to have a temporary command button coded with each block entry place to the right of the entry to load the call log form.

3. Or just go with the two step rout as described above to get the call log form.

Please give me your feed back and coding suggestion if any.

Thanks Ron

[ADezii]

Another undocumented, vital feature of the Access Calendar is that the actual Date corresponding to each Text Box Control (42) is contained within its Tag Property. For February 2012, txtDayBlock07 correlates to February 4, 2012. Placing the following Code in the Dbl-Click Event of this single Text Box returns the actual Date of 2/4/2012. I use this functionality, in the Dbl-Click() Event of all 42 Text Boxes, to sometimes Open Forms Filtered to a specific Date. This in essence make the Text Boxes 'Date Aware'.

Private Sub txtDayBlock07_DblClick(Cancel As Integer)
  MsgBox CDate(Me![txtDayBlock07].Tag)
End Sub