One Active X Calendar Control for many date feilds 2

[Nicos67]

Hello All,

Have recived some excellnet help from Andreas on this but I am stuck with following problem:

With Andy's help I have set up a command button that on tab opens up Calendar control - I then select date - it fills date field - I then tab - and it closes Calendar.

Problem is I wart to use this exact function for all my date feilds across a main form and 6 sub -forms.

Presently I can only use this function for one date field -I want to use the same calendar in the same position on the main form to complete all date fields across main and sub-forms.

Please help

Nicos

 

[TheAceMan1]

How are ya Nicos67 . . . .

[blue]I have set up a command button that [purple]on tab[/purple] opens up Calendar control - I then select date - it fills date field - I then [purple]tab[/purple] - and it closes Calendar.[/blue]

Be more specific about [purple]tab![/purple]

Also when you open the Calendar, are you talking a form or what?

We can help ya, but these questions would help us do that better . . . . .

See Ya! . . . . . .

 

[Nicos67]

Apologies Aceman

It works like this
User completes data fields as follows :

>>Name >>Tab to
>>Address >>Tab to
>>DOB once on DOB field - Calendar Control opens automatically
>> User select date ..tab - takes uswer back to DOB FIELD

and so on to next field code is as follows - courtesy of Andreas:

I have created a button named ShowCal, a textbox named "myDate" and a Calendar 9.0 control named "myCalendar":

'OnClick event of the button:
Sub ShowCal_Click()
Me.myCalendar.Visible=True
End Sub

In order to update your date field and make the calendar disappear after date choice, you need to put some code in the "LostFocus" event of the Calendar:

Private Sub myCalendar_LostFocus()
Me!myDate = Me!myCalendar
Me.myDate.SetFocus
Me.myCalendar.Visible = False
End Sub

Problem is I have mulitipe (15) data fields - and would like the same Calendar in the same poistion on main form to be used to complete each of the 15 date fields - which are spraed across one main form and 6 sub forms - presently code is omly set up for one date field (DOB)

Hope this is of some help
Aceman

 

[Gazonice]

Hi,

I use the calendar available from this web site:

http://www22.brinkster.com/accessory/forms/005.shtml

Easier to use, install, and deploy - it's not an activeX component either.

Comes in a demo database.

Regards,

Garry

 

[TheAceMan1]

Hay Nicos67 . . . .

What you need is a way to hold a reference to the control of interest, no matter what form its on. This way the Calendar knows exactly where to put the date. The only problem is setting the reference with Main Form and subForms involved . . . . . . So lets do it! (Don't forget to backup the DB before making changes)

1) In the [blue]Declarations Section[/blue] of a module in the module window, setup a public variable with the following line:

[blue]Public hldDate As Control[/blue]

This will retain the actual [blue]Object Reference[/blue] to the Date control of interest.

2) In the same module as the variable above, add the following routine. This is the routine that will be called by all your Date controls (from [blue]Got Focus[/blue] event):

[blue]Public Sub GetCalDate(frmName As String, ctlName As String)
   Dim frmUse As Form, frmCurrent As Form, Main As String
   
   Main = "[purple]YourMainFormName[/purple]"
   
   [green]'Set current Form Object[/green]
   If frmName = Main Then
      Set frmCurrent = Forms(Main)          [green]'MainForm[/green]
   Else
      Set frmUse = Forms(Main)
      Set frmCurrent = frmUse(frmName).Form [green]'subForm[/green]
   End If
      
   [green]'Set/Hold the Date Control Object[/green]
   Set hldDate = frmCurrent(ctlName)
   
   [green]'Open the Calendar - Assuming its on the Main Form![/green]
   Forms(Main)!myCalendar.Visible = True
   
End Sub[/blue]

Compile and save . . . .

3) In the [blue]Got Focus[/blue] event for [blue]all[/blue] your [blue]Date Controls[/blue] of interest, add the following code:

[blue]   Call GetCalDate("[purple]Main or subForm Name[/purple]","[purple]Date Control Name[/purple]")[/blue]

For now this should be the only code in the [blue]Got Focus[/blue] events. Also rem out or remove any code in the [blue] On Enter[/blue] events. Its possible any code there could preempt the [blue]Got Focus[/blue].

4) In your [blue]myCalendar_LostFocus[/blue] event, make the following changes it [purple]purple[/purple]:

[blue]Private Sub myCalendar_LostFocus()
   [purple]hldDate[/purple] = Me!myCalendar
   [purple]hldDate[/purple].SetFocus
   Me[purple]![/purple]myCalendar.Visible = False
End Sub[/blue]

Compile & save & give it a whirl . . . . .

See Ya! . . . . . .

 

[Nicos67]

Hi Aceman - Thanks for help - need a bit more - I have inserted code as instructed but debug error comes up with following code and it will not allow user to enter date into any of date fields


Private Sub myCalendar_LostFocus()

hldDate = Me!myCalendar
hldDate.SetFocus
Me!myCalendar.Visible = False

End Sub

Thanks again

 

[scriverb]

A very similar thread has been answered here and deals with the same requirements:

thread702-830743

Post back with any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[TheAceMan1]

Nicos67 . . . .

Does it work if you click a [blue]Date Field[/blue]?

See Ya! . . . . . .

 

[Nicos67]

Aceman - I found this bit of code at fontstuff re - multipe date filds using one calendar but I cant seem to make it work on the 2nd/3rd etc.. date field on MouseDown -
nb:
2nd date field is called - Date_of_Assessment
3rd date field is called - Date_of_Admission


Please help:

Code:

Option Compare Database
Option Explicit
Dim cboOriginator As ComboBox




Private Sub myCalendar_Click()
Pat_DOB.Value = myCalendar.Value
Pat_DOB.SetFocus
myCalendar.Visible = False
Set cboOriginator = Nothing
End Sub





Private Sub Pat_DOB_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Set cboOriginator = Pat_DOB
myCalendar.Visible = True
myCalendar.SetFocus
If Not IsNull(Pat_DOB) Then
myCalendar.Value = Pat_DOB.Value
Else
myCalendar.Value = Date
End If
End Sub


Thanks

Nicos

 

[PHV]

Reply on previous post:
Private Sub myCalendar_LostFocus()
[highlight]Set [/highlight]hldDate = Me!myCalendar
hldDate.SetFocus
Me!myCalendar.Visible = False
End Sub

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

Nicos67 . . . .

Kinda hectic here at work today. Read your post. There were some errors in the code, so I rewrote all (I did not allow for setting focus on subform).

Anyway, can't get it to you until this evening. Besides I have a question & info on operations . . . .

See ya then ;-)

See Ya! . . . . . .

 

[TheAceMan1]

Hay Nicos67 . . .

I know your waiting patiently. I've got the new code completed and fully tested! But I've gotta go to bed before I fall down somewhere. So I'll post tomorrow (will try to make it before lunch). Sorry for the delay.

I tried your [blue]Key Down[/blue] suggestion (I believe a good choice) and fought with it for some time. It appears its not going to work properly for the Calendar Control (doesn't pickup important keys . . . ESC, Enter, Tab . . . . ect). However, I found it worked for most other controls.

I've concluded: [blue]Key Down/Up is not a good choice for the Calendar Control[/blue]

Glad I gave those events a run for their money. It wouldn't have been so bad if I weren't a stickler for notes. Spent quite a bit of time logging testing results.

In anycase I'll see ya tomorrow. I'm sure you'll find it was worth the wait . . . . .

See Ya! . . . . . .

 

[Nicos67]

Thanks for your time Aceman - much apprecvaited

Speak to you soon

Nicos

 

[TheAceMan1]

OK Nicos67 . . . . Are ya ready!

[purple]Don't forget to backup the DB[/purple]. Also, where you see [purple]Purple?[/purple], the descriptive text has to be supplied by you. Just be careful and check for typo's.

1) Delete all the previous code given.

2) In the Declarations Section of a module in the module window, enter the following Public Variables & Constant:

[blue]Public curFrmName As String, curCtlName As String
Public Const frmMainName As String = "[purple][b]YourMainFormName?[/b][/purple]"[/blue]

3) In the same module above, add the following routine. This routine stores form & date names , as well as bring the Calendar out of hiding. [blue]The routine also allows for Passing Thru[/blue] if an selection is not made.

[blue]Public Sub OpenCal(frmName As String, ctlName As String)
   Dim frm As Form
   
   Set frm = Forms(frmMainName)
   
   [green]'If statement keeps On Focus Event from looping![/green]
   If Not frm!myCalendar.Visible Then
      'Hold Names & Open Calendar
      curFrmName = frmName
      curCtlName = ctlName
      Forms(frmMainName)!myCalendar.Visible = True
      
      [green]'Allows user to continue without changing date if
      'no selection is made! Maybe user wants to bypass![/green]
      Forms(frmMainName)!myCalendar.ValueIsNull = True
      
      Forms(frmMainName)!myCalendar.SetFocus
   End If
   
End Sub[/blue]

4) In the [blue]GotFocus[/blue] event for all your Date Controls of interest, add the following code (this is the main activation line, allowing execution by [blue]Tab[/blue] or [blue]Mouse-Click[/blue]):

[blue]   Call OpenCal("[purple][b]Your Form or subForm Name?[/b][/purple]", "[purple][b]YourDateControlName?[/b][/purple]")[/blue]

Next, in the [blue]Lost Focus[/blue] event of your Calendar Control, add the following line:

[blue]   Me.TimerInterval = 50[/blue]

When the Calendar loses focus the [blue]TimerInterval[/blue] allows the event to complete. [blue]This avoids the problem of hiding the Calendar while it has the focus![/blue] When the timer runs out, the [blue]On Timer[/blue] event takes over, transfering the Calendar Selected Date, setting the focus to the Date Control, and hiding the Calendar.

In the Main Forms On Timer event, add the following code:

[blue]   Dim frm As Form, frmUse As Form, subFrm As Control
   
   [green]'Setup Destination Objects for Focus & Date Transfer[/green]
   If curFrmName = frmMainName Then
      [green]'Main Form[/green]
      Set frmUse = Forms(frmMainName)
      frmUse.SetFocus
   Else
      [green]'SubForm[/green]
      Set frm = Forms(frmMainName)
      Set frmUse = frm(curFrmName).Form
      Set subFrm = frm(curFrmName)
      frm.SetFocus
      subFrm.SetFocus
   End If
   
   [green]'Restore focus to origional control![/green]
   frmUse(curCtlName).SetFocus
   
   [green]'Detect Calendar ByPass[/green]
   If Not IsEmpty(Me!myCalendar) Then
      frmUse(curCtlName) = Me!myCalendar.Value
   End If
   
   [green]'Hide Calendar & Stop Timer[/green]
   Me!myCalendar.Visible = False
   Me.TimerInterval = 0[/blue]

Just one more setting. In the Calendar Control properties (other tab at the bottom), make sure [blue]ValueIsNull[/blue] property is set to [blue]No[/blue].

Cheers!

See Ya! . . . . . .

 

[Nicos67]

Aceman,

Thanks for all your time/patience code is fanatastic

Thanks again

Nicos

 

[scriverb]

I see that TheAceMan1 has done a great job of helping you out here. I just wanted to add one thing that may help you in the future. You can use the following code to identify the last control that had the focus and send your Date pick back to that control as well as the focus using this code:

Dim ctlPrevious As Control
Set ctlPrevious = Screen.PreviousControl
ctlPrevous = [i]calendardatevalue[/i]
ctlPrevious.Name.SetFocus

I haven't worked the details of crossing over to a subform but I am sure that can be done also.

May help you in the future.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[TheAceMan1]

Thanks Bob . . . .

 

[hymn]

Interesting way of using the calendar I persoally use a pop up form and a command button just tried to use "on focus" instead of the button workks like a dream
thanks have a star from me

Hope this helps
Hymn