Steve Schapel
miscellany at mvps.org
Fri Jul 13 18:53:00 CDT 2007
John, That is very cool. Thanks. I am just learning about writing classes, given that there is such an emphasis on it in Access 2007. However, in this specific example, are you able to say whether this approach has any advantage over simple setting the Control Source property of the checkbox to: =[YourDateField] Is Not Null Regards Steve jwcolby wrote: > I often like to use dates as flags, i.e. if a process happened I want a date > that the process happened, if it did not happen, then I have a void for the > date. However it is often convenient for the user to see the flag as a > check box, i.e. they only care THAT it happened, not WHEN it happened. > Obviously a checkbox is about Boolean values, not dates. In order to > provide a check box interface to a date field in the database I designed a > WithEvents class. The class is passed in an UNBOUND checkbox (for the > visual display) and a BOUND text box (for the date storage). > > When the user checks the check box for the flag, the class sets or voids the > TEXTBOX VALUE property. The txt.Value is set to Date() if the checkbox is > set, and to VOID if the checkbox is cleared. > > Likewise the checkbox DISPLAYS a true if the value of the textbox is NOT > NULL and DISPLAYS a FALSE if the textbox.Value is NULL. > > This code is in actual use in one of my forms, and represents flags for > check stop payments and check voids in the database. > > The code for the class is as follows: > > '*************************************** > ' > 'This is the CLASS module. Cut and paste this into a new class, > 'then save to the name clsCtlChk2Date > ' > Option Compare Database > Option Explicit > ' > 'This class uses a check box to set / void a date in a record > 'The objective is to allow the form to set a date to date() if a check box > is checked > 'and set that date to void if the check box is cleared. > ' > 'Likewise the checkbox should DISPLAY a TRUE if the date is NOT VOID > 'and should display a FALSE if the date is VOID > ' > 'The class functions by passing in an UNBOUND check box which is the visual > indicator > 'A BOUND text box is passed in which actually stores the state of the flag > as a date data type > ' > 'Thus as the user clicks the check box, this class sets the underlying (and > invisible) text box > 'The text box VALUE property is set to DATE if the check box displays a TRUE > (is checked) > 'and the textbox VALUE property is set to NULL if the checkbox displays a > FALSE (is not checked) > ' > Private WithEvents mchk As CheckBox > Private mtxt As TextBox > > Private Sub Class_Terminate() > Set mchk = Nothing > Set mtxt = Nothing > End Sub > > Function init(lchk As CheckBox, ltxt As TextBox) > Set mchk = lchk > Set mtxt = ltxt > SetChkState > End Function > ' > 'This function causes the text box to track the visual state of the control > 'If the user clicks the check box and the check box ends up TRUE then set > the text box to Date() > 'else set the text box to VOID > ' > Private Sub mchk_Click() > On Error GoTo Err_mchk_Click > If IsNull(mtxt) Then > mtxt = Date > Else > mtxt = Null > End If > SetChkState > Exit_mchk_Click: > Exit Sub > Err_mchk_Click: > Select Case Err > Case 0 '.insert Errors you wish to ignore here > Resume Next > Case Else '.All other errors will trap > Beep > MsgBox Err.Description, , "Error in Sub clsctlChk2Date.mchk_Click" > Resume Exit_mchk_Click > End Select > Resume 0 '.FOR TROUBLESHOOTING > End Sub > ' > 'This sub causes the visual state of the checkbox to track the text box > 'If the text box is VOID (no date) then display a FALSE > 'else display a TRUE > ' > Public Sub SetChkState() > On Error GoTo Err_SetChkState > If IsNull(mtxt) Then > mchk.Value = False > Else > mchk.Value = True > End If > Exit_SetChkState: > Exit Sub > Err_SetChkState: > Select Case Err > Case 0 '.insert Errors you wish to ignore here > Resume Next > Case Else '.All other errors will trap > Beep > MsgBox Err.Description, , "Error in Sub clsctlChk2Date.SetChkState" > Resume Exit_SetChkState > End Select > Resume 0 '.FOR TROUBLESHOOTING > End Sub > > '*************************************** > > It hooks in to the form as follows: > > '*************************************** > ' > 'This is the form's "code behind" module > ' > Option Compare Database > Option Explicit > Private fclsCtlVoidReq As clsctlChk2Date 'Dimension four > instances of the class > Private fclsCtlVoidProc As clsctlChk2Date > Private fclsCtlStopReq As clsctlChk2Date > Private fclsCtlStopProc As clsctlChk2Date > > Private Sub Form_Open(Cancel As Integer) > > Set fclsCtlStopReq = New clsctlChk2Date 'Instantiate the > first one > fclsCtlStopReq.init chkStopRequest, txtStopReq 'pass in an UNBOUND > check box and a BOUND text box > > Set fclsCtlStopProc = New clsctlChk2Date > fclsCtlStopProc.init chkStopProcessed, txtStopProcessed > > Set fclsCtlVoidReq = New clsctlChk2Date > fclsCtlVoidReq.init chkVoidRequest, txtVoidReq > > Set fclsCtlVoidProc = New clsctlChk2Date > fclsCtlVoidProc.init chkVoidProcessed, txtVoidProcessed > > End sub > > Private Sub Form_Current() > fclsCtlStopReq.SetChkState 'As the form moves through records, make the > checkbox display correctly > fclsCtlStopProc.SetChkState > fclsCtlVoidReq.SetChkState > fclsCtlVoidProc.SetChkState > End Sub > > '*************************************** > > That is all there is to it. This is another demonstration of where a class > sinking events can replace a TON of code placed directly in the form, making > code maintenance easier and also fo course making the concept usable in > other forms as well as the current form. > > I hope those who don't use classes find this useful in understanding how > classes can be useful, and how to implement classes and WithEvents to solve > everyday problems. > > Enjoy, > > John W. Colby > Colby Consulting > www.ColbyConsulting.com >