By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Value from ActiveX Calender

P: n/a
I want to be able to accomplish this, please help:

When click on the DOB field, a ActiveX Calender popup and the user
choose a date, the value is automatically refreshed in the DOB field
in the parent form. (I have many forms fields that need a calendar,
but I don't want to create many calenders for each field) Is there a
way to have just 1 calender, the value selected will be passed onto
the parent form field (whatever field I click to get to the calender
in the first place)?

Thanks for your help.

Sincerely,
Perry
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Hi Perry,

Yup.
Something like this should do that for you.

This code is in a main module (in the database window)
"dlgCalendar" is a dialog-style unbound form that contains the calendar

================================================== ===
Option Compare Database
Option Explicit
Global varDate 'This is in the module's main declaration area

Public Function GetDate()
'-----------------------------------------------------------
'Call this function from a date-formatted control on any form...
' eg:
''''Private Sub BegDate_DblClick(Cancel As Integer)
''''GetDate
''''Me![BegDate] = varDate
''''End Sub
'-----------------------------------------------------------

If Not IsLoaded("dlgCalendar") Then
DoCmd.OpenForm "dlgCalendar", , , , , acDialog
End If

'Because the calendar form is Modal, it retains the focus
'until closed.

'This allows the global variable "varDate" to be set/changed
'as long as the Calendar form remains open.

'Then, after the Calendar form is closed the "calling form's"
'code resumes and inserts the value of the varDate variable into the
'calling form's control.

End Function
--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
=============================
"PerryC" <pe******@yahoo.com> wrote in message
news:ea*************************@posting.google.co m...
I want to be able to accomplish this, please help:

When click on the DOB field, a ActiveX Calender popup and the user
choose a date, the value is automatically refreshed in the DOB field
in the parent form. (I have many forms fields that need a calendar,
but I don't want to create many calenders for each field) Is there a
way to have just 1 calender, the value selected will be passed onto
the parent form field (whatever field I click to get to the calender
in the first place)?

Thanks for your help.

Sincerely,
Perry

Nov 12 '05 #2

P: n/a
Hi,

A bit of a footnote to my post...
If you need the IsLoaded() function, it can be found in "Utility Functions"
module of the Nothwinds sample application which ships with most versions of
Access.
The Access Web also has a function that is similar ...
http://www.mvps.org/access/forms/frm0002.htm
Don
Nov 12 '05 #3

P: n/a
Don, thank for your expert advice... however, I am so green that I have
no idea where is the main module. I have never deal with module... can
you go step by step how to place this code... and yes... I don't have
the IsLoad... I can probably find northwind somewhere... but, I really
don't know what I am doing here... sorry. I use tables, query, forms,
reports and that is it. I thought there is a way to add a code in the
actual form itself to get those info from the popup calender... well...
please help.

Sincerely,
Perry


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

P: n/a
Ditch the ActiveX Calendar and have a look here:
http://www.lebans.com/monthcalendar.htm

Make sure you scroll down the Web page for a detailed explanation of how
to implement the code found behind the sample Forms.

A97MonthCalendar.zip is an A97 database containing a Class that wraps
the Microsoft Month Calendar Common Control in an easy to use interface.
A2K Version here: MonthCalendar Access 2000.zip
This is a completely API generated Month Calendar derived directly from
the Common Control DLL. What this means is that there are no
distribution or versioning issues as there are if you use the ActiveX
Month Calendar control. In other words this is not an ActiveX control!

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"PerryC" <pe******@yahoo.com> wrote in message
news:ea*************************@posting.google.co m...
I want to be able to accomplish this, please help:

When click on the DOB field, a ActiveX Calender popup and the user
choose a date, the value is automatically refreshed in the DOB field
in the parent form. (I have many forms fields that need a calendar,
but I don't want to create many calenders for each field) Is there a
way to have just 1 calender, the value selected will be passed onto
the parent form field (whatever field I click to get to the calender
in the first place)?

Thanks for your help.

Sincerely,
Perry


Nov 12 '05 #5

P: n/a
Hi Perry,

Sheesh! Ok, I was a newbie once too, and not too long ago, so I
understand... :)

I'm by no means an "exburp", but OK ... are you ready for Access97 "Coding
101"?
You've got to understand that you're about to explore VBA programming ...
the "true power" of Access.

============================ Stage One ========================
1.) Go the Database Window
2.) Click on the "Modules" tab
3.) Click [New]
4.) A code window titled "Module 1" will appear and will contain the
following text by default:

Option Compare Database
Option Explicit

5.) Copy all of the code below, position the cursor on the line following
the default text, and paste my code into that code window:
Global varDate 'This is in the module's main declaration area

Public Function GetDate()
'-----------------------------------------------------------
'Call this function from a date-formatted control on any form...
' eg:
''''Private Sub BegDate_DblClick(Cancel As Integer)
''''GetDate
''''Me![BegDate] = varDate
''''End Sub
'-----------------------------------------------------------

If Not IsLoaded("dlgCalendar") Then
DoCmd.OpenForm "dlgCalendar", , , , , acDialog
End If

'Because the calendar form is Modal, it retains the focus
'until closed.

'This allows the global variable "varDate" to be set/changed
'as long as the Calendar form remains open.

'Then, after the Calendar form is closed the "calling form's"
'code resumes and inserts the value of the varDate variable into the
'calling form's control.

End Function
************************************************** ***********
6.) You'll notice right away that the active code is mostly colored black,
and that lines preceded by an apostophe ( ' ) are intended as comments and
are green in color. Red is bad... that means you have syntax errors,or
commands that Access doesn't understand. (Hopefully you wont see any red
here.)
7.) On the menu bar, Debug/Compile Loaded Modules
8.) File / Save. Access will prompt you for a name for your new module. I'd
suggest naming it something meaningful like "modGetDate"

OK, that's one module down, and one more to go...

9.) Create another new module as in steps 1 to 4 above.
10.) Copy and paste the IsLoaded() function --- below --- into this second
module, as in step 5 :

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet
view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function

11.) Repeat steps 7 & 8 as above.

============================= Stage Two ============================
12.) Assuming that you have already created an unbound form named
"dlgCalendar" (if not do so now.)
that contains the following controls:
a calendar control named "MyCal"
a command button named "cmdOK" with the caption "OK"
a label (name is not important) with the caption:

Select a Date from the calendar. <[ctrl] + [Enter]>
When finished click the OK button

13.) View / Code
14.) If this is a 'virgin' form, you'll be greeted with the now familiar
default text:
Option Compare Database
Option Explicit
15.) Copy and paste (you know the drill by now) this code into the
appropriate place.

Private Sub cmdOK_Click()
varDate = Me.MyCal
DoCmd.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
varDate = Me.MyCal
End Sub

Private Sub MyCal_AfterUpdate()
varDate = Me.MyCal
End Sub

16.) Debug / Compile, and close the code window... save the form. You can
dress this form up a little too, while you're at it. You wont need scroll
bars, record selectors, navigation buttons, etc as this is an unbound form.
You can set things like a dialog-style border, Auto-center, and more for a
polished look.
Be sure to save and close the form again after.

============================= Stage Three ============================
17.) Open the form (in design view) that contains the textbox control that
you want to insert the date into. I have no idea what you are calling that
textbox control, so you have to modify the "YourControl" references below to
match YOUR actual control name.

18.) Select YourControl. Open the property sheet if it isn't already open.
19.) click on the "Event" tab.
20.) Choose an event that you would like this action to take place... I like
"On Dbl Click"
21.) Click on the "down-arrow" to the right of this event, and select
"[Event Procedure]"
22.) Click on the ellipse (...) to invoke the code editor, which opens the
code window and creates a place-holder for your event which should look like
this:
(And HEY.. there's that default text again!)
Option Compare Database
Option Explicit

Private Sub YourControl_DblClick(Cancel As Integer)

End Sub

23.) You need to copy and paste this into that event place-holder....

GetDate
Me![YourControl] = varDate

so that it now looks like:

Private Sub YourControl_DblClick(Cancel As Integer)

GetDate
Me![YourControl] = varDate

End Sub

24.) Debug / Compile Save/Close the code window.

25.) You're ready for the "test drive". If it works... enjoy!
If it doesn't, reply to NG with your specific code and any error messages.

PS....
Nobody will likely believe me when I say that I got about as far as step 16
of this the first time, and suffered a power outage!!!!
Note the addition to my signature... :)
--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"Perry Cheng" <pe******@yahoo.com> wrote in message
news:40*********************@news.frii.net...
Don, thank for your expert advice... however, I am so green that I have
no idea where is the main module. I have never deal with module... can
you go step by step how to place this code... and yes... I don't have
the IsLoad... I can probably find northwind somewhere... but, I really
don't know what I am doing here... sorry. I use tables, query, forms,
reports and that is it. I thought there is a way to add a code in the
actual form itself to get those info from the popup calender... well...
please help.

Sincerely,
Perry


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #6

P: n/a
Don, thanks for your sympathy. I really have not messed around with
module at all... well... I don't know if I made myself clear at the
beginning... I am using Access 2002. Your code for IsLoad somehow is
not defined... I got an error saying "Sub or Function not defined".
Thanks for your help.

Perry

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #7

P: n/a
Hi Perry,

The function name is "IsLoaded", NOT "IsLoad". Is that where the problem is
perhaps?

You *did* copy and paste that IsLoaded function into a module as instructed
in step #10, right?
No error messages were displayed when you compiled?

Test the function:
1.) Open any form, (except "dlgCalendar") and leave it open. Remember or
write down the *exact* name of this form.
2.) Open another form in design view, then click on View/Code to open a code
window
3.) Now select View/Immediate Window to open the Immediate (Debug) window.

4.) Now type this exactly as I have it here, except of course replacing the
blank line with the name of the form from step 2:
?IsLoaded("__________________")

5.) The program should respond with "True"

If this doesn't work, let "us" know.

PS.
I also have Access XP (2002) installed. I converted an Access97 app that
uses the IsLoaded function, and it works flawlessly there too ... so I think
that rules out any version issues, unless it has to do with the way you have
AccessXP setup on your PC. AccessXP uses ADO by default, while Access97
uses DAO. (The DAO support is an optional install, if I recall correctly. If
it isn't installed, do yourself a favor and install it. There is a ton of
very useful DAO code available out there.)

I would think that the program would complain loudly about the lack of DAO
when attempting to compile if this were the case???
Does anybody else have any opinions, clarifications, or advice on that?

Don
================

"Perry Cheng" <pe******@yahoo.com> wrote in message
news:40*********************@news.frii.net...
Don, thanks for your sympathy. I really have not messed around with
module at all... well... I don't know if I made myself clear at the
beginning... I am using Access 2002. Your code for IsLoad somehow is
not defined... I got an error saying "Sub or Function not defined".
Thanks for your help.

Perry

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #8

P: n/a
Another Footnote:

I just had a look at Stephen's calendar as per his suggestion in this
thread.( I AM going to try this myself some day!)
There is a paragraph in there that pretty much confirms the DAO /
References issue....

Quote:
You may likely run into a "References" problem with Stephen's code with
Access versions A2K or A2K2. The arch-nemesis of Access is missing
references. If you receive a message saying something like "Can't find
project or library" or "Undefined Function" it will most likely be due to
the fact that you do not have a reference set to the DAO object library
and/or your file locations for those libraries are in different places.

To fix the References problem follow these steps:
- Open any module in Design view.
- On the Tools menu, click References.
- At least one Reference should say "Missing". WRITE down the one(s)
missing, most likely it will be the DAO library.
- Click to clear the check box for the type library or object library marked
as "Missing:."
- Close the References Window box.
- Open the References Window back up. Hopefully, nothing should say Missing
this time.
- Now go find that library/project(s) in the list that was missing.
- If it is the DAO one scroll down to you get to Microsoft DAO 3.xx and
check it.
- If you're using Access 97 that should be DAO 3.51 Object Library.
- If you're using Access 2000, 2002, or 2003 that should be DAO 3.6 Object
Library.
- If more than one were missing, find the others and check them as well.
- Close the References box again.
- Now re-compile again. Debug--Compile.
- Hopefully you should not see any more compile errors.
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.