[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