Date Picker in 2007 Problem in 2002

[cpStar]

I have developed a db in Access 2002. I have a command button that will bring up a calendar form which the user selects a date & it'll put it in a txt box. I have a lot of code behind this calendar form. The problem is, those users that have Access 2007 an icon is placed in front of my command button & they will click it & use the 2007 calendar instead of mine, thereby not activating my code. Is there any way to prevent this feature from appearing? My db is an MDE file, if that makes any difference.

Thanks for any suggestions.

[mshmyob]

There is a property under the format tab called "Show Date Picker" - set it to NEVER and it will not show for date formated text controls.

Since you have just the MDE I don't believe you will be able to do it. You will need the original source code.

cheers,

[cpStar]

Thanks for your suggestion. However isn't that property you're referring to in the 2007 version? You can't change that for the Access program can you? Isn't it db related? I did try that several weeks ago, but if I remember correctly I think I had to create a blank db before I could change that option, so when I tried my 2002 MDE db it had no effect on it. The only db that would effect is the one you create in 2007, correct? btw, I'm the developer so I have the source code .... before delivering to my client I create an MDE file.

I've done a lot of searching on this, but have come up with nothing. So any help you can give me, I certainly appreciate!

[mshmyob]

I will create a 2002 database with the features you mention and then play with it in 2007 and see what I can come up with. I am wondering if through code if you iterate through all forms and change the property I mentioned if it will work.

I will try some things and get back to you.

cheers,

[mshmyob]

Actually can you open your source in AC2007 and make the changes to the form controls like I said erlier and then save it in AC2002 format (from Ac2007) and see if that works.

cheers,

[cpStar]

Unfortunately, I don't have 2007, however I could try that at my client. I'm not that familar with 2007 .. does it allow you to convert it to 2002 & then save it as an MDE? From my prev experience with different versions, if you want to create an MDE you have to convert it to that current version, not lower.

Thanks again for all your time & help with this, mshmyob.

[mshmyob]

Yes, with AC2007 you can save as AC2002 and create a AC2002 MDE file.

The biggest problem I have found with saving to an earlier version is if you create an app in AC2007 and use macros they don't get saved in the lower versions. You will not be doing this however.

cheers,

@cpStar

[cpStar]

That's good to hear! I'll give it a try & let you know how I make out. The only problem I see with this is when I need to make changes at my office & save on my Access 2002 pc I'll lose that setting so will have to go back into Access 2007 & add that each & every time there's a change. But if this is the only way to resolve the problem, guess I don't have a choice. Too bad MS didn't think of this & have this feature turned off by default, or at least a way to turn this feature off from the actual program not individual db.

Thanks again, mshmyob, for your quick response!!

[mshmyob]

Yes it is a known issue and unfortunatley there is no global setting in AC2007 for this. It is done at the control level.

Over the weekend I will try and throw some code together to iterate through your forms and turn it off via vba. If I get it working I will upload it so you can try it and let me know if it works since I can't test it (I only have 2007 installed now).

cheers,

@cpStar

[cpStar]

I believe it's working!!! I was able to find the property I needed to use in code. On my PC with 2002, I put this code in the txtbox's OnEnter event:
Me!txtTestDate.ShowDatePicker = False.

I wasn't sure if it would compile w/o errors, but it worked fine. I then converted it to 2002 & then made an mde file with no errors. I put it on their server & it works fine. So it appears I won't have to do this each & every time there's a change.

If there is a better way to do this, let me know, otherwise I think it's all set. THANK YOU SO MUCH!! This has been such a pain in my neck as it's been causing data entry problems. Users would use the 2007 calendar even though I've brought it to their attention several times to be sure they're using my calendar & not 2007's.

Thanks again mshmyob!!

[mshmyob]

Here is some code I just thru together that will iterate through all your forms and make the changes automatically for you.

Put the code in say the main menu or any form that has no meaning (you can create a new one). You can put it in the on open event or behinnd a button click - whatever.

Dim accObj As AccessObject
Dim strFrmName As String
Dim fForm As Access.Form
Dim ctl As Access.Control
 
' cycle through all the forms in your project
For Each accObj In CurrentProject.AllForms
 
' get the name of a form in your project
strFrmName = accObj.Name
 
 
 
If strFrmName <> "frmMain" Then 'ignore the form where this code is in - you cannot change the form you are in
' open your first form of your project
DoCmd.OpenForm strFrmName, acDesign, WindowMode:=acHidden
Set fForm = Forms(accObj.Name)
 
'cycle through all the controls on each form and change only the parameters you want
For Each ctl In fForm.Controls
    ' this will skip over any control that is not a text control since it won't have a ShowDatePicker property
    On Error Resume Next
    ' change the ShowDatePicker Property
    ctl.ShowDatePicker = False
Next
 
'close the form and you will be prompted for changes
'DoCmd.Close acForm, strFrmName
' or close the form and save without prompting
DoCmd.Close acForm, strFrmName, acSaveYes
Else
'do nothing
End If
 
Next
 

Notice I called this from a form called frmMain and I ignore that form (line 14) - it may work without it but I haven't done much testing. It will cycle through all your forms and change all TEXT controls to SHOWDATEPICKER=False

cheers,

@cpStar

[cpStar]

Thanks for your code, but I was hoping my last message got to you before you spent any more time on it. I should have also explained I don't have that many date txtboxes so I had already added that 1 line of code to all of them. However, I am keeping your code as I know I'll use it for other things.

Thanks so much, mshmyob, for all your help & patience with this. It was greatly appreciated !!

[mshmyob]

You're welcome. The code was not a waste of my time since I can use it in a modified manner for something I am working on but was not ready to look into yet.

cheers,


@cpStar

[cpStar]

I'm glad it wasn't a waste of time. I feel better now. :)

Have a great weekend!!