Modify Allen Browne Calendar - Automatically Enter Day For Chosen

G

Guest

I'd like to modify Allen Browne's popup calendar form to also automatically
enter the day for the chosen date in to a text box named txtday when the OK
button is pressed. I'm using MS Access 2002.

Many thanks


Here’s the code for reference:

Author: Allen Browne. (e-mail address removed)
'You may use this example for private, business, or educational purposes,
with acknowledgement.
'However, you may not publish it without the express, written permission of
the author.

'You also need this code in a standard module:
'---------------------standard module code begins-------------------------
'Public gtxtCalTarget As TextBox 'Text box to return the date from the
calendar to.
'Public Function CalendarFor(txt As TextBox, Optional strTitle As String)
'On Error GoTo Err_Handler
' 'Purpose: Open the calendar form, identifying the text box to return
the date to.
' 'Arguments: txt = the text box to return the date to.
' ' strTitle = the caption for the calendar form (passed in
OpenArgs).
'
' Set gtxtCalTarget = txt
' DoCmd.OpenForm "frmCalendar", windowmode:=acDialog, OpenArgs:=strTitle
'
'Exit_Handler:
' Exit Function
'
'Err_Handler:
' MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation,
"CalendarFor()"
' Resume Exit_Handler
'End Function
'---------------------standard module code ends-------------------------

Option Compare Database
Option Explicit

Private Const lngcFirstDayOfWeek = vbSunday 'Weekday of the first column in
the calendar.
Private Const lngcWeekendForeColor = 192& 'RGB value for Saturdays and
Sundays.
Private Const conMod = "frmCalendar" 'Name of this module (for error
handler.)

Private Sub cmdCancel_Click()
On Error GoTo Err_Handler
'Purpose: Close without transferring date back to calling text box.

DoCmd.Close acForm, Me.Name, acSaveNo

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation,
conMod & ".cmdCancel_Click"
Resume Exit_Handler
End Sub

Private Sub cmdMonthDown_Click()
Call SetDate("M", -1)
End Sub
Private Sub cmdMonthUp_Click()
Call SetDate("M", 1)
End Sub

Private Sub cmdOk_Click()
On Error Resume Next
'Purpose: Transfer the result back to the calling text box (if there
is one), and close.

If gtxtCalTarget = Me.txtDate Then
'do nothing
Else
gtxtCalTarget = Me.txtDate
End If
gtxtCalTarget.SetFocus
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Private Sub cmdYearDown_Click()
Call SetDate("YYYY", -1)
End Sub
Private Sub cmdYearUp_Click()
Call SetDate("YYYY", 1)
End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err

'Initialize to the existing date, or today if null.
If IsDate(gtxtCalTarget) Then
Me.txtDate = gtxtCalTarget.Value
Else
Me.txtDate = Date
End If

'Set the title
If Len(Me.OpenArgs) > 0& Then
Me.Caption = Me.OpenArgs
End If

'Set up the calendar for this month.
Call ShowCal

Form_Open_Exit:
Exit Sub

Form_Open_Err:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical,
conMod & ".frmCalendar.Form_Open"
Resume Form_Open_Exit
End Sub

Private Function SetSelected(ctlName As String)
On Error GoTo Err_Handler

Me.txtDate = DateSerial(Year(txtDate), Month(txtDate),
CLng(Me(ctlName).Caption))
Call ShowHighligher(ctlName)

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation,
conMod & ".SetSelected"
Resume Exit_Handler
End Function

Private Function SelectDate(ctlName As String)
Call SetSelected(ctlName)
Call cmdOk_Click
End Function

Private Function SetDate(Unit As String, Optional intStep As Integer = 1)
On Error GoTo Err_Handler

Me.txtDate = DateAdd(Unit, intStep, Me.txtDate)
Call ShowCal

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation,
conMod & ".SetDate"
Resume Exit_Handler
End Function

Private Function ShowCal() As Boolean
On Error GoTo Err_Handler
'Purpose:
Dim dtStartDate As Date 'First of month
Dim iDays As Integer 'Days in month
Dim iOffset As Integer 'Offset to first label for month.
Dim i As Integer 'Loop controller.
Dim j As Integer 'Inner loop controller.
Dim iDay As Integer 'Day under consideration.
Dim bshow As Boolean 'Flag: show label

dtStartDate = Me.txtDate - Day(Me.txtDate) + 1 'First of month
iDays = Day(DateAdd("m", 1, dtStartDate) - 1) 'Days in month.
iOffset = Weekday(dtStartDate, lngcFirstDayOfWeek) - 2 'Offset to first
label for month.

'Show the days on the grid.
For i = 0 To 41
With Me("lblDay" & Format(i, "00"))
iDay = i - iOffset
bshow = ((iDay > 0) And (iDay <= iDays))
If .Visible <> bshow Then
..Visible = bshow
End If
If (bshow) And (.Caption <> iDay) Then
..Caption = iDay
End If
End With
Next

'Set the labels for the weekday names, and the colors for weekends.
For i = 0 To 6
iDay = ((lngcFirstDayOfWeek + i - 1) Mod 7) + 1
With Me("lblCol" & i)
..Caption = Left(Format(iDay, "ddd"), 2)
If iDay = vbSunday Or iDay = vbSaturday Then
Me("lblCol" & i).ForeColor = lngcWeekendForeColor
For j = 0 To 5
Me("lblDay" & Format(7 * j + i, "00")).ForeColor =
lngcWeekendForeColor
Next
End If
End With
Next

'Place the highligher circle on the grid for the selected day.
Call ShowHighligher("lblDay" & Format(Day(Me.txtDate) + iOffset, "00"))

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation,
conMod & ".ShowCal"
Resume Exit_Handler
End Function

Private Function ShowHighligher(ctlName As String)
On Error GoTo Err_Handler
Const lngcVOffset As Long = -83

With Me(ctlName)
Me.lblHighlight.Left = .Left
Me.lblHighlight.Top = .Top + lngcVOffset
End With

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation,
conMod & ".ShowHighligher"
Resume Exit_Handler
End Function

Private Sub lblToday_Click()
Me.txtDate = Date
Call ShowCal
End Sub
 
G

Guest

Hi Jonah,
...enter the day for the chosen date in to a text box named txtday...
Click to expand...

As in "Tuesday" or "Wednesday"? You should be able to set an appropriate
format for the textbox in question to display the day, if this is what you
mean.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Jonah said:
I'd like to modify Allen Browne's popup calendar form to also automatically
enter the day for the chosen date in to a text box named txtday when the OK
button is pressed. I'm using MS Access 2002.

Many thanks
Click to expand...

<snipped rest of message>
 
G

Guest

Hi Tom,

Thanks for your quick reply.

Yes, it is what I mean. For example, if the user picks today’s date
22/11/2007 from the popup calendar then Thursday is automatically entered in
the txtDay field, if they pick yesterday’s date 21/11/2007 then Wednesday is
automatically entered and if they pick 01/12/2007 then Saturday is entered
and so on.

You say ‘set an appropriate format for the box in question’, what would this
be?

Jonah
 
G

Guest

Hi Jonah,

Ha! 1/12/2007 is a Friday for me. Of course, I'm using the m/dd/yyyy
convention common for the United States. <smile>

Try entering the following format, in the properties dialog for the text box
in question: dddd

Also, see the following article by Access MVP Allen Browne:

International Dates in Access
http://allenbrowne.com/ser-36.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
J

Jonah

Hi Tom

I’ve tried dddd as you suggested – it didn’t work.

The date from the popup calendar works fine; it enters the chosen date in
the field txtDate, but I can’t get the day for the chosen date to fill in the
txtDay field. I don’t know if additional coding needs to be added in the
cmdOK_Click to make this work?

‘Author: Allen Browne. (e-mail address removed)

Private Sub cmdOk_Click()
On Error Resume Next
'Purpose: Transfer the result back to the calling text box (if there
is one), and close.

If gtxtCalTarget = Me.txtDate Then
'do nothing
Else
gtxtCalTarget = Me.txtDate
End If
gtxtCalTarget.SetFocus
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Am I asking for too much from the calendar and should I just use a Combo Box
for the txtDay field and hope that the user picks the correct day, from the
list, for the date they’ve chosen?

Jonah
 
T

Tom Wickerath

Hi Jonah,
it enters the chosen date in the field txtDate, but I can’t get the
day for the chosen date to fill in the txtDay field.
Click to expand...

I guess I didn't realize that you had two separate text boxes involved for
displaying the date. (I believe you will find that the format should work, if
you had applied it to the txtDate text box).

Okay, since this is just a format of existing data, you don't want to store
it. So, try applying the dddd format to txtDay, and then set the control
source for this textbox to the same field that you are using for txtDate. If
your txtDay textbox is an unbound control (ie. no control source), then set
the control source for txtDay to the name of this control. For example:

txtDate
Control Source: =[txtDay]


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

I think I got the txtDay and txtDate controls switched in my last reply. Try
this, instead:

If your txtDate textbox is an unbound control (ie. no control source), then
set
the control source for txtDay to the name of this control. For example:

txtDay
Control Source: =[txtDate]


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Tom Wickerath said:
Hi Jonah,
it enters the chosen date in the field txtDate, but I can’t get the
day for the chosen date to fill in the txtDay field.
Click to expand...

I guess I didn't realize that you had two separate text boxes involved for
displaying the date. (I believe you will find that the format should work, if
you had applied it to the txtDate text box).

Okay, since this is just a format of existing data, you don't want to store
it. So, try applying the dddd format to txtDay, and then set the control
source for this textbox to the same field that you are using for txtDate. If
your txtDay textbox is an unbound control (ie. no control source), then set
the control source for txtDay to the name of this control. For example:

txtDate
Control Source: =[txtDay]


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Jonah said:
Hi Tom

I’ve tried dddd as you suggested – it didn’t work.

The date from the popup calendar works fine; it enters the chosen date in
the field txtDate, but I can’t get the day for the chosen date to fill in the
txtDay field. I don’t know if additional coding needs to be added in the
cmdOK_Click to make this work?

‘Author: Allen Browne. (e-mail address removed)

Private Sub cmdOk_Click()
On Error Resume Next
'Purpose: Transfer the result back to the calling text box (if there
is one), and close.

If gtxtCalTarget = Me.txtDate Then
'do nothing
Else
gtxtCalTarget = Me.txtDate
End If
gtxtCalTarget.SetFocus
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Am I asking for too much from the calendar and should I just use a Combo Box
for the txtDay field and hope that the user picks the correct day, from the
list, for the date they’ve chosen?

Jonah
Click to expand...
Click to expand...
 
J

Jonah

Hi Tom,

Thanks for your reply. I’ve managed to get it to work; by making both
textboxes, TxtDay and TxtDate, unbound and applying the dddd format and
control as you said to txtDay.

However, I really need to store this data for statistical purposes, so both
these textboxes need to be bound to the following fields TxtDay and TxtDate
in a table called tIncidents; the Control Source for TxtDay is Day and
TxtDate is Date. Is this possible?

Jonah
 
T

Tom Wickerath

Hi Jonah,

Bind the textbox that you want to store the actual date in to the
appropriate underlying field. Remove the dddd format from this textbox, and
consider replacing it with: dd/mm/yyyy

For the other textbox, which you want to display the day of the week, for
example "Tuesday", set the format to: dddd Set the control source for this
textbox to point to the same field as the first textbox.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
J

Jonah

Hi Tom

I’ve tried your last suggestion and I can’t get it to work. If I bind the
txtDate textbox to the field Date in the underlying table, and set the
control source for the TxtDay textbox to =[TxtDate] with the format dddd it
displays the chosen date 26/11/2007, in both textboxes. The date is saved in
the underlying table but no day i.e. Monday – not even 26/11/2007 which is
displayed in the txtDay textbox.

I’ve tried different combinations to no avail, the only one that displays
the day and date is:
TxtDay control =[txtDate]
TxtDate is unbound
but then neither the day nor date is saved in the underlying table.

I really appreciate your help with this problem.

Jonah
 
T

Tom Wickerath

Hi Jonah,

Try this sample:

http://home.comcast.net/~tutorme2/samples/jonah.zip

The QBF (Query by Form) that is set as the Startup form includes an example
of an unbound textbox, txtPurchaseDate, useful for helping locate a record.
The txtDayOfWeek control is bound to the expression:

=[txtPurchaseDate]


Once you locate a record, double-click on it in the subform to open it for
editing. The txtReleaseDate textbox is an example of a bound control. It is
bound to the ReleaseDate field. The txtDayOfWeek control is bound to the
expression:

=[ReleaseDate]

So, just to be clear, the txtDayOfWeek textbox control, used to display the
day of the week (dddd), is bound in both cases to an expression.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
J

Jonah

Hi Tom

Thanks for the link to the sample database.

I can see how you get the day to be displayed for the chosen date. But the
day information i.e Thursday isn’t saved in a table, so therefore you cannot
search, for example, for all titles released on a Thursday or purchased on a
Saturday and this is what I need to be able to do.

As an example, I may need to search for all incidents between January 2007
and June 2007 which have occurred on a Saturday to see if there is a trend or
a pattern. So am I best going back to my earlier suggestion of:-

"Am I asking for too much from the calendar and should I just use a Combo
Box for the txtDay field and hope that the user picks the correct day,
from the list, for the date they’ve chosen?"

That way the day would be saved in the table tIncidents and could be
searched for as described above.

Jonah
 
T

Tom Wickerath

Hi Jonah,
But the day information i.e Thursday isn’t saved in a table,....
Click to expand...

Correct. I think that's what I indicated in my reply posted 11/22/2007 4:31
PM PST
http://www.microsoft.com/office/com...cess&mid=6418f6f1-0354-4214-bc23-ae0be68b913f where I wrote:

"Okay, since this is just a format of existing data,
you don't want to store it."

Storing the day along with the date would be a prime example of a database
normalization error, since one can always calculate the day based on the
date. If you store both values, it can be easy for them to get "out of synch"
with each other. Here is a quote that I like to share with others, concerning
denormalization, from a document written by Database Design expert Michael
Hernandez:

<Begin Quote (from page 23 of document)>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>

Source: See the first download titled "Understanding Normalization",
available here:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

so therefore you cannot
search, for example, for all titles released on a Thursday or purchased on a
Saturday and this is what I need to be able to do.
Click to expand...

Sure you can. You just need to use the built-in Weekday function, or, for
example, provide a combo box on a search form that displays the day to the
user, but provides the corresponding numeric value for a given weekday as a
parameter for a query. Here is an example that you can try out for the sample
Northwind database:

qryWeekdayOrders

SELECT CompanyName, OrderDate,
WeekdayName(Weekday([OrderDate])) AS OrderDayName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Weekday([OrderDate])) Not In (1,7)))
ORDER BY Orders.OrderDate;

Here, you can see that I am excluding the numeric days 1 (Sunday) and 7
(Saturday), in order to return a recordset of all orders placed between
Monday to Friday.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

Looks like that link I provided as a reference to my previous thread didn't
quite work out. It apparently included an extra space. This one may
(hopefully) be better:

http://www.microsoft.com/office/com...cess&mid=6418f6f1-0354-4214-bc23-ae0be68b913f


Also, I did not address this statement:
As an example, I may need to search for all incidents between January 2007
and June 2007 which have occurred on a Saturday to see if there is a trend or
a pattern. So am I best going back to my earlier suggestion of:-
Click to expand...

SELECT [Field1], [Field2], ..... [Fieldn]
FROM [MyTable]
WHERE [IncidentDate] BETWEEN #1/1/2007# AND #6/30/2007#
AND Weekday([IncidentDate] = 7

Notes:
1.) Date formats assume month/day/year. You may need to add the use of a
Format statement to handle International Date formats, as discussed in this
article by Allen Browne:

International Dates in Access
http://allenbrowne.com/ser-36.html

2.) Brackets indicated above ( [ ] ) are only required if your field and/or
table names include special characters, such as spaces, hyphens, etc., or use
reserved words.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
 
J

Jonah

Hi Tom,

Sorry for not replying before now.

I've now got it working, thank you for all your help.

Jonah
 
J

Jonah

Hi Tom

I did post a reply at the beginning of the week, but for some reason it's
not showing.

I've managed to get this working now. Thanks very much for your time and help

Jonah
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify Allen Browne Calendar 2
Printing Batch - Test Db from Allen Browne 3
RTF or XL report filtered (Allen Browne) 4
Form :: performance hit from control conditions? 1
ADP list box filter 3
Error Handling; Allen Browne 2
Duplicate record code from Allen Browne 4
Validation and Popup Calendar 5

Top