By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

MS Access Calendar Populate from query with calculated field

P: 5
I'm trying to populate the ms access caledar from a query with a calculated field here s the code from the query
Expand|Select|Wrap|Line Numbers
  1. SELECT HPContacts.FirstName,   HPContacts.LastName, HPContacts.LastContactDate+[DaysBeforeNextContact] AS [Next Contact]
  2. FROM HPContacts
  3. WHERE ((([HPContacts].[LastContactDate]+[DaysBeforeNextContact]) Is Not Null))
  4. 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.
Feb 19 '12 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb
  2.  
  3. strSQL = "SELECT HP.FirstName, HP.LastName, HP.LastContractDate, " & _
  4.          "HP.[DaysBeforeNextContact] " & _
  5.          "FROM HPContacts AS HP WHERE [HP].[LastContractDate] is Not Null " & _
  6.          "AND [HP].[LastContractDate] BETWEEN %F AND %L " & _
  7.          "ORDER BY HP.LastName, HP.FirstName;"
  8. strSQL = Replace(strSQL, "%F", lngFirstOfMonth)
  9. strSQL = Replace(strSQL, "%L", lngLastOfMonth)
  10.  
  11. Set rstEvents = db.OpenRecordset(strSQL)        'Added 4/16/2008
  12.  
  13. With rstEvents
  14.   Do While Not .EOF
  15.     'CFB added 2-18-10
  16.     lngFirstDateInRange = ![LastContractDate]
  17.     If lngFirstDateInRange < lngFirstOfMonth Then
  18.       lngFirstDateInRange = lngFirstOfMonth
  19.     End If
  20.     lngLastDateInRange = ![LastContractDate]
  21.     If lngLastDateInRange > lngLastOfMonth Then
  22.       lngLastDateInRange = lngLastOfMonth
  23.     End If
  24.  
  25.     For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
  26.       bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
  27.       bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
  28.         If astrCalendarBlocks(bytBlockCounter) = "" Then      'no existing Text in Array
  29.           astrCalendarBlocks(bytBlockCounter) = ![LastName] & ", " & Left$(![FirstName], 1) & "." & _
  30.                                                 " - [" & DateAdd("d", ![DaysBeforeNextContact], ![LastContractDate]) & "]"
  31.         Else
  32.           astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
  33.                                                 ![LastName] & ", " & Left$(![FirstName], 1) & "." & _
  34.                                                 " - [" & DateAdd("d", ![DaysBeforeNextContact], ![LastContractDate]) & "]"
  35.       End If
  36.     Next lngEachDateInRange
  37.     'End of CFB added 2-18-10
  38.       .MoveNext
  39. Loop
  40. 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.

Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,637
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.
Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb
  2.  
  3. strSQL = "SELECT HP.FirstName, HP.LastName, HP.LastContractDate, " & _
  4.          "HP.[DaysBeforeNextContact] " & _
  5.          "FROM HPContacts AS HP WHERE [HP].[LastContractDate] is Not Null " & _
  6.          "AND [HP].[LastContractDate] BETWEEN %F AND %L " & _
  7.          "ORDER BY HP.LastName, HP.FirstName;"
  8. strSQL = Replace(strSQL, "%F", lngFirstOfMonth)
  9. strSQL = Replace(strSQL, "%L", lngLastOfMonth)
  10.  
  11. Set rstEvents = db.OpenRecordset(strSQL)        'Added 4/16/2008
  12.  
  13. With rstEvents
  14.   Do While Not .EOF
  15.     'CFB added 2-18-10
  16.     lngFirstDateInRange = ![LastContractDate]
  17.     If lngFirstDateInRange < lngFirstOfMonth Then
  18.       lngFirstDateInRange = lngFirstOfMonth
  19.     End If
  20.     lngLastDateInRange = ![LastContractDate]
  21.     If lngLastDateInRange > lngLastOfMonth Then
  22.       lngLastDateInRange = lngLastOfMonth
  23.     End If
  24.  
  25.     For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
  26.       bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
  27.       bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
  28.         If astrCalendarBlocks(bytBlockCounter) = "" Then      'no existing Text in Array
  29.           astrCalendarBlocks(bytBlockCounter) = ![LastName] & ", " & Left$(![FirstName], 1) & "." & _
  30.                                                 " - [" & DateAdd("d", ![DaysBeforeNextContact], ![LastContractDate]) & "]"
  31.         Else
  32.           astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
  33.                                                 ![LastName] & ", " & Left$(![FirstName], 1) & "." & _
  34.                                                 " - [" & DateAdd("d", ![DaysBeforeNextContact], ![LastContractDate]) & "]"
  35.       End If
  36.     Next lngEachDateInRange
  37.     'End of CFB added 2-18-10
  38.       .MoveNext
  39. Loop
  40. 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.
Attached Files
File Type: zip Rstruthers_Calendar.zip (61.4 KB, 127 views)
Feb 19 '12 #2

P: 5
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
Feb 19 '12 #3

ADezii
Expert 5K+
P: 8,637
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.
Feb 20 '12 #4

P: 5
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
Attached Files
File Type: zip Rstruthers_Calendar2.zip (106.2 KB, 87 views)
Feb 20 '12 #5

ADezii
Expert 5K+
P: 8,637
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.
Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb
  2.  
  3. strSQL = "SELECT HP.FirstName, HP.LastName, HP.LastContractDate, " & _
  4.          "HP.[DaysBeforeNextContact] " & _
  5.          "FROM HPContacts AS HP WHERE [HP].[LastContractDate] is Not Null " & _
  6.          "AND ([LastContractDate]+[DaysBeforeNextContact]) BETWEEN %F AND %L " & _
  7.          "ORDER BY HP.LastName, HP.FirstName;"
  8. strSQL = Replace(strSQL, "%F", lngFirstOfMonth)
  9. strSQL = Replace(strSQL, "%L", lngLastOfMonth)
  10.  
  11. Set rstEvents = db.OpenRecordset(strSQL)        'Added 4/16/2008
  12.  
  13. With rstEvents
  14.   Do While Not .EOF
  15.     'CFB added 2-18-10
  16.     lngFirstDateInRange = (![LastContractDate] + ![DaysBeforeNextContact])
  17.     If lngFirstDateInRange < lngFirstOfMonth Then
  18.       lngFirstDateInRange = lngFirstOfMonth
  19.     End If
  20.     lngLastDateInRange = (![LastContractDate] + ![DaysBeforeNextContact])
  21.     If lngLastDateInRange > lngLastOfMonth Then
  22.       lngLastDateInRange = lngLastOfMonth
  23.     End If
  24.  
  25.     For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
  26.       bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
  27.       bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
  28.         If astrCalendarBlocks(bytBlockCounter) = "" Then      'no existing Text in Array
  29.           astrCalendarBlocks(bytBlockCounter) = ![LastName] & ", " & ![FirstName]
  30.         Else
  31.           astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
  32.                                                 ![LastName] & ", " & ![FirstName]
  33.       End If
  34.     Next lngEachDateInRange
  35.     'End of CFB added 2-18-10
  36.       .MoveNext
  37. Loop
  38. End With
Feb 20 '12 #6

P: 5
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
Feb 21 '12 #7

ADezii
Expert 5K+
P: 8,637
@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.
Feb 21 '12 #8

P: 5
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
Feb 22 '12 #9

ADezii
Expert 5K+
P: 8,637
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'.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDayBlock07_DblClick(Cancel As Integer)
  2.   MsgBox CDate(Me![txtDayBlock07].Tag)
  3. End Sub
Feb 23 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.