Link to home
Start Free TrialLog in
Avatar of KarenTaggart
KarenTaggart

asked on

Access 2007 Date-picker calendar not consistent in subforms

I have two subforms that have date fields. I have the "show date picker" set to "for dates". In one of the subforms, the date picker works populates the date field as it should. In the other, although the calendar does show up, no date populates in the date field. If I open this subform on its own, the date does populate - it's only when I open the form and try to use the date picker in that subform that it doesn't work.

This same symptom actually happened in the first subform when I first put it in the main form, then for no identifiable reason, began working.

This is the calendar that shows up iin 2007, not the ocx calendar control.
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Anytime you have troubles like this, it's a good idea to perform maintenance on your database.

First, make a backup.

Now Compact the database. Click File - Manage, Compact and Repair.

Now Compile your database. Open the VBA Editor, click Debug - Compile. Fix any errors you find, and continue doing this until the Compile menu option is diabled.

Now compact again.

If you're still having troubles, you might also build a new, blank database and import everything from the old db into the new.
Avatar of KarenTaggart
KarenTaggart

ASKER

I tried all of these suggestions, including importing everything into a new db, but still have the same issue.  It really puzzles me that it behaves "correctly" when the subform is opened by itself, but when it's part of a main form, it doesn't.  I tried copying the date field that began behaving correctly into the other subform (they both have the same table as recordsource) but it still doesn't work.
OK - more weirdness.  It only doesn't work if it's the last subform.  (There are several subforms on different tabs. And that explains why the first one suddenly began working right - I'd put a second subform on!)  As long as there's another subform AFTER the one I'm using, it works. Even if the after-subform is invisible.  I have no clue why this is glitching, but I guess I can work around it.
At least for me, it is near impossible to figure out what the issue might be without a sample db.
A "subform date not populating" could be any of a number of things, but on the surface, it just seems like wherever you are "populating" the date something is not quite right.
Either where you are "sending" it, is not correct, or the date is just not being stored.

If you feel like posting a sample, here are the guidelines:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
Thanks - I've compacted/repaired, compiled, removed any sensitive information and many of the tables/forms. The only startup option I have is to display the form that is giving me problems (frmTrips).

This database is to track specs and expenses for planes used by a company. On the frmTrips form, on the Fuel Purchases/Expenses tab, when you use the Access 2007 calendar date-picker in the first subform, the date you click on is populated into the date field, as expected.  In the second subform, if you do the same thing, no date is populated - nothing happens at all. Because the date is the 1st text box, the record doesn't even get inserted. (But this symptom happens on both new and existing records.)  If you add another form on the same tab (or a subsequent tab), the date picker calendar in both of these subforms does populate the date in the field as expected, populating the selected date.

Thanks for looking at this.  I appreciate your help.

 DateTest.accdb
One other thing - if you open either one of the subforms individually (not the main form), both the date field calendar date-pickers act as expected.  It's only when they are in the main form when the unexpected behavior happens. And it seems to depend on the order of the subforms on the main form - whichever one is last is the one that shows the symptom.
I opened your database (thanks for following Jeff's advice, by the way), and both subforms display the date picker. There are only two weird things to notice: 1) your hard-coded date formatting doesn't play nice with my own date formatting and 2) both date picker icons appear at the same time (one in each subform) when selecting the tab for the first time, something which doesn't normally happen, or at least shouldn't normally happen.

I'm using Access 2007, SP2.

(°v°)
My apologies, the second date picker indeed doesn't work... Looking into it a bit more... — (°v°)
I can only confirm what you already found out: the last subform (in the tab order) of a tab control has a problem with the date picker. I added a third subform displaying the query “SELECT Date() As Today;”, which I hid, the the first two subforms behaved normally. If I changed the tab order, the last one would always inherit the bug.

I have never encountered something like this. It is a genuine bug and my opinion of Access 2007 just went down another notch. I'll signal this to some other experts; maybe one of the MVPs will file a bug report.

(°v°)
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You are right! But still, how does hiding already hidden columns affect the date picker? Wouldn't you agree that this is an actual bug? — (°v°)
Interesting.  (That commenting out the On Load code makes the symptom disappear.)  But still... if you open the subform alone, even with the On Load code, the symptom doesn't happen.  I've gotten around this issue by putting a hidden subform on the main for as the "last" one.  Awkward and definitely not elegant, but it lets me get on with this project.
You really don't need the On Load event either. It's simpler to delete the controls you don't want to show rather than hiding the corresponding columns, especially at run-time. Note that the fields without a corresponding control are still available through code and as link fields.

This doesn't explain the bug, of course, but it's a little less awkward than adding a dummy subform.

(°v°)
On a whim, I changed the On Load event to On Open. For some reason, that took care of the symptom. I've used that same On Load hide-the-columns code for quite a while when displaying subforms containing columns I want to make sure aren't visible - some of them on this subform get populated in the Before Insert event with values from the parent form, so it's easiest to have them there.  The Open event happens before the Load event - which explains nothing about why making this change fixed anything - but now at least I don't need the dummy subform.

Thanks everyone for your help!
Thank you, but I can't take credit for thinking about the On Load event. It was LSM's comment http:#36204420 which lead you to your final workaround. Did you mean to accept his comment, perhaps?

(°v°)
Sorry - I did mean to accept the "On Load" comment.  Is there a way to reassign the points?  But also, thank you for your research and many responses.
You are welcome. To reassign the points, simply use the “request attention” link below your question and type “Please reopen, I accepted the wrong comment”. It happens all the time.

Cheers!
(°v°)
Note that I did my testing in 2010, so you are correct that this occurs in 2010 as well.

With your permission, I'll use the database you attached and send this to the Access team for their review. I looked to see if this bug has been reported before, but couldn't find anything.
LSMConsulting - I would be happy for you to send the sample file to the Access team.  Thank you!
The symptom was apparently caused by an actual bug in MS Access 2007 and 2010. This helped identify particulars that led to a work-around. (Mainly that removing the On Load event code that hid some columns caused the symptom to disappear. I moved that code from On Load to On Open and for some reason the symptom disappeared.)
I've posted this to the MVP lists and will report back with any results.
Thanks!
To any who tested this:

Could you please let me know the following:

Operating System, including Service Pack
Office/Access Version, including Service Pack

Access 2007 (12.0.6535.5005) SP2 MSO (12.0.6554.5001)
Windows 7 Professional, SP1
Same versions here, so no additional information. — (°v°)