[AccessD] How do I pass a control to a function in the ControlSource

I use something very similar, again from way back with Getz etc.

On the form property against the d/click I put =popcal(True) or
=popcal(False)
To indicate if the user can change the date or note.
The calendar form then opens with the existing date in the text box passed
through (no need for screen.active.control) that is handled in the function.

Zero over heads and NO problems with it being called DIRECTLY from the form.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: 30 September 2009 23:34
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] How do I pass a control to a function in the
ControlSource


I use a standard textbox control for dates throughout all applications.
Double click the textbox and it pops my calendar and passes a reference to
the textbox.  The format, input mask and most everything except the field to
which the control is bound is identical throughout applications.  To add a
new date field to any form, just copy and paste from any form to any other
form and it works every time and it's already hooked up to the code.  No
need to figure out whether the control is on a form, a sub form or a sub sub
form as the code acts on the control and doesn't rely on any reference to a
parent or parent's parent and parent.  

 

Since every date field uses the same approach on every form, there is no
maintenance nightmare.  In the property sheet of the control it reads:

 

=fnSetDate(Screen.ActiveControl)

 

This property is copied when the control is copied to any location.

 

The function reads as follows:

 

Public Function fnSetDate(txtCtl As TextBox)

DoCmd.OpenForm "frmCal", , , , , , "1;" & txtCtl.Value & ";"

Forms("frmCal").SetReturn txtCtl

End Function

 

The opening args are there to set the calendar mode and preselect a date if
one exists in the control.  In this case the '1' means to set the calendar
mode as a date picker.  The SetReturn function behind the calendar form just
initializes a module level variable:

 

Public Sub SetReturn(ctlTxt As TextBox)
Set mctlTextBox = ctlTxt
End Sub


In the form close event the value of the passed in control is set:

 

mctlTextBox.Value = mdteSelected


I have been using this since Access 97 was new and have found no reason to
change the approach.  It just works every time.


I could have done this non-light weight by writing code in the double click
event for each control requiring the functionality and pass a reference
along the lines:

 

Dim txtCtl as TextBox

Set txtCtl = Me.txtStartDate

DoCmd.OpenForm "frmCal", , , , , , "1;" & txtCtl.Value & ";"

Forms("frmCal").SetReturn txtCtl

 

This is more work and more code to implement so I'll just keep on doing this
light weight.  I've done the same with a pop up calculator and these two
situations are the only places where I use functions in control property
sheets.  The calculator is nice in that it shows a 'paper ribbon' that can
be corrected by editing the mistaken entry any number of lines back and will
recalculate the output value after keying corrections.  The calendar has
modes for setting meetings and reminders for multiple users and can be set
to show flags or captions on the dates.  The calculator works is hooked up
in almost all money field controls and a few other places where it makes
sense.  Copy an exising one, paste it where needed, set the control source
and you're done.

 

Ciao 

Jürgen Welz 

Edmonton, Alberta 

jwelz at hotmail.com


 
> From: max.wanadoo at gmail.com
> To: accessd at databaseadvisors.com
> Date: Tue, 29 Sep 2009 21:45:00 +0100
> Subject: Re: [AccessD] How do I pass a control to a function in the
ControlSource
> 
> > It allows for "light-weight" forms and creates nightmares for debugging.
> 
> Only if you get it wrong...here we go again, Charlotte LOL
> 
> I have never had a problem with it for over a decade of using it. YMMV...
> 
> Max 		 	   		  
_________________________________________________________________
We are your photos. Share us now with Windows Live Photos.
http://go.microsoft.com/?linkid=9666047
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com