Using Allen Browne's calendar in an event procedure

[baud]

I have a button with an "On Click" event procedure.
This procedure asks for a date with a statement like this one:

stReturnDate = InputBox("Enter the return date?","Return Date")

After this statement, the procedure validates the date, and sends an
email message with Outlook, where the date is part of the message body.

I would like to replace the above statement with a call to Allen
Browne's calendar ( http://allenbrowne.com/ser-51.html ).
Instead of receiving the date in a TextBox, I would like to have it
returned to the my procedure, in the stReturnDate string variable.

Doed anyone know a way to achieve this?

Thanks a lot to everybody!

 

[Douglas J. Steele]

I haven't looked at the code behind Allen's calendar, but I would assume
that you should be able to have it update a hidden text box on the form and
then assign your variable whatever value is in that textbox.

 

[Dave Patrick]

You know..... I used to do this a lot using a datetimepicker control. Store,
exactly as you mentioned, in a hidden text box on the form. I found I would
infrequently have a problem where the text box had no value when I tried to
read from it. Something that happened seldom enough that I could never catch
it. A timing issue I suspect. I finally gave up on that and started using a
local temp table to store the value of the datetimepicker then used
functions to read from or write to them. I no longer have the timing and or
null value problems I mentioned. Just an FYI.


Public Function readdate1()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
strSQL = "SELECT TOP 1 currdate " _
& "FROM frmTransDate; "
rs.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
readdate1 = rs!currdate
rs.Close
cnn.Close
End Function
Public Function readshift1()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
strSQL = "SELECT TOP 1 currshift " _
& "FROM frmTransDate; "
rs.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
readshift1 = rs!currshift
rs.Close
cnn.Close
End Function
Public Function writedate1(PassedDate As Date, PassedShift As Integer)
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
strSQL = "SELECT TOP 1 currdate, currshift " _
& "FROM frmTransDate; "
rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
rs!currdate = Format(PassedDate, "mm/dd/yyyy")
If PassedShift = 2 Then rs!currshift = "7:00"
If PassedShift = 3 Then rs!currshift = "19:00"
rs.Update
rs.Close
cnn.Close
End Function


--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I haven't looked at the code behind Allen's calendar, but I would assume
| that you should be able to have it update a hidden text box on the form
and
| then assign your variable whatever value is in that textbox.
|
| --
| Doug Steele, Microsoft Access MVP
|
| (no e-mails, please!)

 

[baud]

Thanks for your quick replies, guys.

I understand I could try with an hidden textbox on the form where the
button lies, but how do I launch Allen's calendar from the vb
procedure?
In Allen's example, the calendar is triggered by an "on click" event
containing '=CalendarFor([InvoiceDate],"Select the Invoice Date")' .
I don't want to click on 2 buttons (one to call the calendar and fill
in the hidden textbox, one to run my vb procedure): so I need to call
the calendar from my vb procedure.

This may sound like a stupid question, but I'm still learning you see...

 

[Dave Patrick]

Sorry but I've never used Allen's frmCalendar

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks for your quick replies, guys.
|
| I understand I could try with an hidden textbox on the form where the
| button lies, but how do I launch Allen's calendar from the vb
| procedure?
| In Allen's example, the calendar is triggered by an "on click" event
| containing '=CalendarFor([InvoiceDate],"Select the Invoice Date")' .
| I don't want to click on 2 buttons (one to call the calendar and fill
| in the hidden textbox, one to run my vb procedure): so I need to call
| the calendar from my vb procedure.
|
| This may sound like a stupid question, but I'm still learning you see...
|

 

[Allen Browne]

You could reprogram the example to do whatever you wish.

The CalendarFor() function accepts a Textbox argument, and sets a public
variable to that textbox.

The Open event of the Calendar form initializes the calendar to match any
date that is already in that textbox, or today if no date is chosen.

The Click event of the Ok button on the calendar form returns the selected
date to the text box, and assumes that--since the calendar is opened
modally--the receiving form is still open.

You could strip out that functionality,and make cmdOk_Click return the value
to a public variable of type String (or Date? or Variant?) instead of to the
text box if you wish.

 

[baud]

Allen, following your advice, I looked your example much more
profundly, and I succeeded to modify it the way I wanted to!

In fact, I was puzzled by the fact that there was no assignment of the
return value to the function name CalendarFor; I finally realized that
the link to the outside world was made by variable gtxtCalTarget.

Thanks a lot to you, Dave and Douglas for the time you spent helping
me!!

Baud.

 

[Allen Browne]

Well done.

Yes, it's not programmed in the most obvious way. There were several reasons
for the circuitous approach. Most important was not altering the return
value unless needed. The function sometimes needs to alter the date, may
need to return a Null, sometimes should not return anything (when the user
cancels), and does not need to change the original date is there is no
change. (I have this thing about not dirtying records in a multi-user
environment any more than needed, to avoid unnecessary writes.) The easiest
way to achieve that was to pass in the text box rather than set a return
value, it it actually acts as a Sub, not a Function.

But it still has to be a function to work in the event property, and that
was the 2nd design goal: absolutely minimal code in the standard module, and
everything happening in the events of the form, which makes it very
portable.

So, glad you were able to create your own path for the return value.