Heenan, Lambert
Lambert.Heenan at AIG.com
Thu Aug 28 12:15:08 CDT 2003
Dan,
You say "This procedure is a function only because it is being called by
name from
the property table of the date field. Looks like:
=PopupCalendar([Screen].[ActiveControl]) in the double-click event
for the
date field. The line of code that starts txt.value =
Format(DateSerial( .
. . does return the date value to the calling field."
A couple more observations:
1/
So you only want the date form to appear if the date field is double
clicked. Fine, create a "real" double click event for the field instead of
calling the routine from the property sheet.
Private Sub txtSomeDate_DblClick(Cancel As Integer)
txtSomeDate = PopupCalendar(txtSomeDate)
txtSomeDate_AfterUpdate
End Sub
The only advantage of dropping the text
"=PopupCalendar([Screen].[ActiveControl]) " into the property sheet is that
you can select a bunch of controls and then paste the text into them all at
once, but the headaches that you then get, such as the one your are dealing
with are not worth it. Also you get no syntax checking, you cannot use
AutoComplete (in a code module you can type the first few characters of any
identifier and then hit Control+Space to get a list of matching identifiers
to choose from and so complete the text. Cuts out typos in a major way), and
it's a real pain to type it all in if you want to call a routine that takes
a bunch of parameters.
It's not difficult to just write the code for each control, you can copy the
code for one textbox and paste it into the event for another and just using
find and replace to change the control names.
2/ "The line of code that starts txt.value = Format(DateSerial( ...
does return the date value to the calling field.".
Well the value you want does get plugged into the field, but NOT as a return
value of the function. i.e. not via a statement that assigns a value to the
function name. In your case it happens to work because the control is passed
as the first argument to the function, and the default for VBA is that
parameters are passed by reference. So it's the defaulted ByRef parameter
that is getting the value, but that's really just a side effect and not
truly a return value of the function. It may sound pedantic to point this
out, but there's nothing to say that MS will not have a change of heart
about the ByRef / ByVal parameters defaults for Subs and Functions in future
versions of Access. They did do a complete about turn with VB.Net, VB uses
ByRef as the default, but VB.Net switched to ByVal. The same thing could
happen with access.
So if you want to write a sub or function that can modify the value of a
parameter you should always explicitly declare the parameter as ByRef so it
is perfectly clear that you intend to change the value. (Just my opinion,
but I think it makes sense).
You might also want to look into using WithEvents in a class module. This
can work wonders where you have common code you want to run for a bunch of
controls. Take a look here...
http://smsconsulting.spb.ru/shamil_s/demos/wedstart.htm
Lambert
> -----Original Message-----
> From: Dan Waters [SMTP:dwaters at usinternet.com]
> Sent: Thursday, August 28, 2003 12:32 PM
> To: 'Heenan, Lambert'
> Subject: RE: [AccessD] Calling an AfterUpdate Event in a different
> form.
>
> Lambert - Thanks!
>
> I have to admit that I was using acDialog only because that code came with
> the calendar, and I just didn't change it. I went back to A97 Help to
> find
> out what acDialog meant. More help from the list!
>
> This procedure is a function only because it is being called by name from
> the property table of the date field. Looks like:
> =PopupCalendar([Screen].[ActiveControl]) in the double-click event for
> the
> date field. The line of code that starts txt.value = Format(DateSerial(
> .
> . . does return the date value to the calling field.
>
> Dan
>
>
> -----Original Message-----
> From: Heenan, Lambert [mailto:Lambert.Heenan at AIG.com]
> Sent: Thursday, August 28, 2003 9:46 AM
> To: 'Access Developers discussion and problem solving'
> Cc: 'dwaters at usinternet.com'
> Subject: RE: [AccessD] Calling an AfterUpdate Event in a different form.
>
>
> First point is your 'function' PopupCalendar is not returning any value.
> You should modify it so that it gets the date value from the calendar form
> and returns that value to the caller.
>
> Secondly, presumably you are calling PopupCalendar() from the form that
> needs the date value (in the OnClick event of the date field?). In which
> case, why not simply call the field's AfterUpdate event immediately after
> the call to PopupCalendar(). Because you open the calendar form with
> acDialog, the procedure that called PopupCalendar() will wait until the
> calendar form has closed before executing the AfterUpdate event.
>
> All it takes is one more line of code and no need to write a bunch of
> public
> routines for every field that you want to use the calendar form with.
>
> Private Sub txtDateStart_Click()
> txtDateStart = PopupCalendar(txtDateStart)b ' get the date value as
> the
> function return value
> txtDateStart_AfterUpdate ' call the event handler
> End Sub
>
> Here's how I would change your function...
>
> Public Function PopupCalendar(txt As TextBox) As Variant
> On Error GoTo EH
>
> Dim varStartDate As Variant
>
> If IsNull(txt.Value) Then
> varStartDate = CurrentDate
> Else
> varStartDate = txt.Value
> End If
>
> DoCmd.OpenForm "frmCalendar", , , , , acDialog, varStartDate
>
> If IsFormLoaded("frmCalendar") = True Then
> PopupCalendar = Format(DateSerial(frmCal!Year, frmCal!Month,
> frmCal!Day), "m/d/yy")
> DoCmd.Close acForm, "frmCalendar"
> End If
>
> Exit Function
>
> EH:
> Application.Echo True
> Call GlobalErrors("", Err.Number, Err.Description,
> CurrentObjectName,"PopupCalendar", txt)
>
> End Function
>
> Just a little bit simpler this way. And we all know what KISS means :-)
>
> Lambert
>
> > -----Original Message-----
> > From: Dan Waters [SMTP:dwaters at usinternet.com]
> > Sent: Thursday, August 28, 2003 11:18 AM
> > To: Database Advisors
> > Subject: [AccessD] Calling an AfterUpdate Event in a different form.
> >
> > Is this possible?
> >
> > I have a calendar form that is triggered by double-clicking in a date
> > field. Putting the date into the correct field works fine, but I need
> > to be able to
> > trigger the AfterUpdate event for whatever date field is on whatever
> form,
> > and I haven't been able to figure this out. The AfterUpdate event is
> > Public. The associated code is below, and is in a standard module:
> >
> >
> > Public Function PopupCalendar(txt As TextBox) As Variant
> > On Error GoTo EH
> >
> > [IN THIS EXAMPLE, txt = txtDateStart FROM THE CALLING FORM]
> >
> > Dim frmCal As Form
> > Dim varStartDate As Variant
> > Dim frmParent As Form
> > Dim stgParent As String
> > Dim stgAfterUpdateEvent As String
> >
> > If IsNull(txt.Value) Then
> > varStartDate = CurrentDate
> > Else
> > varStartDate = txt.Value
> > End If
> >
> > DoCmd.OpenForm "frmCalendar", , , , , acDialog, varStartDate
> >
> > If IsFormLoaded("frmCalendar") = True Then
> > Set frmCal = Forms("frmCalendar")
> > txt.Value = Format(DateSerial(frmCal!Year, frmCal!Month,
> > frmCal!Day), "m/d/yy")
> >
> > stgParent = txt.Parent.Name
> > Set frmParent = Forms(stgParent)
> > stgAfterUpdateEvent = txt.Name & "_AfterUpdate"
> >
> > Call frmParent.stgAfterUpdateEvent [THIS IS WHAT I WANT -
> > BUT
> > NO DICE!]
> >
> > Call frmParent.txtDateStart_AfterUpdate [THIS WORKS, BUT WOULD
> > NEED A CASE FOR EVERY FIELD!]
> >
> > DoCmd.Close acForm, "frmCalendar"
> > Set frmCal = Nothing
> > End If
> >
> > Exit Function
> >
> > EH:
> > Application.Echo True
> > Call GlobalErrors("", Err.Number, Err.Description,
> > CurrentObjectName, "PopupCalendar", txt)
> >
> > End Function
> >
> >
> >
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>