Value from a function

[Guest_imported]

I have a database where I will be using several different forms. On each of the forms I will need to have a calendar that users can select the date. Instead of having several calendar controls I want to have one calander form that opens allows them to select the date and closes returning the date they selected. I want to create a function that I can call. The problem is I can not decipher how to get the function to wait for the user to select the date from the calander form.

Function Selectdate() As Date
DoCmd.Openform "Calander"
'here is where I am lost
Selectdate = Forms!Calander!Calander01.value
End Function

I am open to suggestions on the best way to accomplish this task

 

[RickSpr]

You need to make the Calendar form modal. Set its Modal and PopUp properties to Yes. Then, once you open the form, the user can't do anything within Access until the form closes.

Just be very sure you leave the user a way to close the form (including escaping from any validation error message you may have). Otherwise you may wind up having to kill the Access task on the task list in order to break the loop. Rick Sprague

 

[Guest_imported]

My issue is not in getting the users to use the form the problem is that in the function above as soon as the form opens it returns the value from where it was called without giving an opportunity to select a date from the calander form that is open. I need to have some sort of a delay that waits until the user clicks on the calander form.

 

[RickSpr]

Did you try what I suggested? If you make the calendar form modal, then it retains control of Access until it is closed. The code that opened it will stay suspended on the OpenForm call until the calendar form is closed.

I realize that in my earlier post I emphasized the effect of modality on the user interface, which probably confused you. But the reason the user can't do anything elsewhere in Access is that the rest of Access is suspended by the modal form. I'm pretty sure that's what you need. Give it a try. Rick Sprague

 

[Guest_imported]

Rick, I appreciate your attention on this so far however
I do have the forms modal as yes and pop up as yes.
The calender form has the calender control and a command button to close the form. As soon as I call the above function it returns a default date. I does not wait for the calender to be checked.
Create a form named Calender, place a calender control on it and a command button to close the form. Name the calender control calender1. In the calendar controls on change event place this code.
Debug.Print me.Calender1.value
set the forms modal and pop up to yes
now create this function


then from debug window call the function
Function Selectdate() As Date
DoCmd.Openform "Calender"
'here is where I am lost
Selectdate = Forms!Calender!Calender1.value
End Function

debug.print selectdate

you will see that instead of waiting for you to select the date it immediately returns a default date. You can then click on the calander control to see that the value it will return a value but too late.
Again any advice you can give on this would be appreciated

 

[RickSpr]

You need to specify that the calendar form is opened in dialog mode ("acdialog" as 6th argument to OpenForm). The modal property only takes effect for dialog windows.

Also, I think you'll find you have a new problem. You won't continue executing the code after OpenForm until the calendar form closes. But once it's closed, the control you're trying to access no longer exists, so you can't retrieve the value.

There are 2 common solutions: (1) Have the calendar form save the chosen date in a global variable, or (2) When the user selects to "close" the calendar form, the form just hides itself, then after the invoking code has extracted the date value, the invoking code actually closes the form. (When a modal form hides itself, its modality ends and the code that invoked it resumes.) Rick Sprague

 

[Guest_imported]

Thanks for the help Rick it now works like a champ
The final product looks like

Function Selectdate() As Date
DoCmd.OpenForm "calendar", , , , , acDialog
Selectdate = Forms!Calendar!Calendar01.value
DoCmd.Close acForm, "Calendar"
End Function

Then on the forms close button
Me.forms.Visible = False


To bring up the calendar all I need to do is put in

Me.Mydatefieldsname = Selectdate()

And the calendar opens