Generic Calendar Form

[Jesse Alan]

Hello Everyone,
Question: How do I pass a reference of a component from one form
and access that component's properties in another form?

What I would like is a calendar form that I can easily move to
other access databases. This means the calendar form should have no
knowledge of other forms residing with it. It should have a function
that accepts the components reference. When the user finally click a
date, the Cmd_click() function will use the reference to set the
component's value in the calling form.

This seems simple & sounds simple to me but I'm a Java programmer
with little to no knowledge of MS-Access programming. Can you guys &
gals help me out with a quick and dirty fix? In the mean time I'm
going to get myself a book on programming MS-Access. We are using MS
Access 2003 county wide here. Does anyone have a recommendation on a
book for that version?

 

[Tom van Stiphout]

On Fri, 10 Jul 2009 05:42:38 -0700 (PDT), Jesse Alan

I solved that without passing anything to the calendar form. Rather I
have a public function in a standard module:
Public Function GetDate(Optional varDate As Variant) As Variant
' If varDate is missing then use today's date.
' Otherwise, set the date to the date passed.

Dim varTempDate As Variant

' Set calendar date
If Not IsDate(varDate) Then varDate = Date
varTempDate = IIf(IsMissing(varDate), Date, CDate(varDate))

' Validate date
If Not IsDate(varTempDate) Then varTempDate = Date
DoCmd.OpenForm FormName:="frmCalendarControl",
WindowMode:=acDialog, OpenArgs:=varTempDate

' If frmCalendarControl is still loaded, then the user clicked OK
so get the
' date from the form. If the form isn't open return a value of
Null.
If IsFormOpen_TSB("frmCalendarControl") Then
GetDate = Forms("frmCalendarControl").Calendar1.Value
DoCmd.Close acForm, "frmCalendarControl"
Else
GetDate = Null
End If
End Function

The calendar form has a Cancel button that closes the form, and an OK
button that just sets the form to invisible:
Me.Visible = False
which as you may know makes the form fall out of the modal loop. In
the next line of the GetDate procedure I check if the form is still
running. If yes the user clicked OK and I grab the value of the
calendar control.

On any form where I have a date textbox I place a button next to it,
and in the Click event I call the above function:
txtStartDate = GetDate(txtStartDate)


-Tom.
Microsoft Access MVP

 

[Jesse Alan]

Tom,
Thanks for your quick response. I'm not sure I fully understand
your implementation here but again, I'm am very new to MS-Access and
VBA. I will take a closer look at your code later. I think I solved my
problem in an easy fashion that I can understand now.

First, I have my data entry form that calls up the calendar form
upon a cmd click passing a string argument.

Private Sub cmdFILED_Click()
DoCmd.OpenForm "formCalendar", , , , , , "formDataEntry|txtDATE"
End Sub


Next, the formCalendar (which uses Calendar Control 11.0) upon a
date selection will parse the string argument and execute this simple
line.

Private Sub Calendar0_Click()
Dim formDataEntry As String
Dim txtDATE As String

'split string argument
'set variables = to split strings

Forms(formDataEntry).Controls.Item(txtDATE).Value
End Sub

This is untested atm but I was able to get a control's value using
the variables above. This implementation also allows me to keep
formCalendar portable to other projects.

Did you have a book reference you could pass along to me? I would
like something to teach me VBA's structure, best practices, etc..