Strange Date Picker Behavior

[sxschech]

Upgraded to Access 2016. I have a text box on a subform (datasheet view) and set the Property sheet of the textbox to "Show Date Picker" For Dates.

If I manually type in a date, things work as expected...the record indicator shows that the row is in edit mode such that if I press the escape key, it will cancel the edit. However, if I click on the little icon next to the textbox, after I select the date, one of the following happens:

Very first time a box popped up saying there was an error and program is terminated (Access disappears. (was random so haven't been able to get the error message)

After first time clicking date picker either does: Date would show up in a textbox (memo field) of a second subform (single form view) replacing all the text with the picked date. Record would not show the edit icon and it would not change the date in the actual textbox where the date is located and associated with the date picker.

Or: crashes program, without providing an error message (Access just disappears)

I tried Compact and Repair as well as Decompile, Compile, Compact and Repair.

For now, I will turn off date picker. Anyone else experienced such issue?

 

[Ranman256]

upgrades tend to behave oddly, especially with old db using newer access.
Mine would crash on something else that was minor, but not date picker.
Try deleting the text box, and load in a new one. This way you use the new tools.
You can also try creating a new 2016 db, then import all objects again.
Ive yet to find a single cure for this, but sometimes the above items help.

 

[missinglinq]

Although we usually think of Forms, or entire Databases, when we speak of corruption, individual Controls, such as Textboxes, can and do become corrupted. The test and the fix are one and the same; deleting the Control then re-creating it...as Ranman256 sugested! If it was corrupt, you've verified the fact and solved the problem! If not, you've only lost a minute or two!

Linq ;0)>

 

[The_Doc_Man]

A question for my colleagues. His text box is in datasheet view. Does a date picker even WORK in that context? I thought that the date picker depended on being in form view. Of course, I could be totally off base here. Can you set the properties of a box in datasheet view? Because there is where you would set the properties of when to show the picker.

 

[missinglinq]

Yes and yes.

Linq ;0)>

 

[sxschech]

Thanks for your suggestions. Tried them and no luck.
1. Deleted textbox and created new text box
2. Created new blank database in Access 2016, imported tables, compact and repair, imported queries compact and repair, imported main form, compact and repair, imported reports, compact and repair, imported modules, compact and repair. Created both subforms from New Form Design and copied and pasted vba (Ctrl+C/Ctrl+V) from old sub forms. (Did not import the old subforms or their code) Added References, turns out DAO reference does not work and according to a search found that should use Microsoft Office 15.0 Access database engine Object Library. Debug Compile, Closed db, Performed a decompile/compile and then another compact and repair.
3. Opened Form for the first time tried the date picker and it still obliterates the data in second subform after clicking on a date.

Unfortunately, neither the 2 minute revision nor an hour and 1/2 on the rebuild seems to have sorted this out.

I have no idea under what logic access is using to paste and completely replace data in another subform, which isn't even storing dates rather than putting the date in the textbox the date picker is associated with. Subforms and text boxes are bound to separate tables and linked to mainform in the properties.

 

[static]

Sounds like a corrupted memo field.

When memo fields get corrupted their contents can span multiple fields.

If the memo spans non text fields Access usually crashes, but if it only spans text fields it can just get confused and carry on working but with odd side effects.

Back up your database then change the memo to text.

Avoid using memo fields, especially on networks.

 

[JHB]

Upgraded to Access 2016. I have a text box on a subform (datasheet view) and set the Property sheet of the textbox to "Show Date Picker" For Dates.

From what version are you upgraded from?
Did it run without problem in the old version?
I don't have version 2016, so are you able to downgrade it to version 2010 then you could post your database with some sample data?

 

[isladogs]

Despite your painstaking care when rebuilding, AFAIAA you didn't mention doing an Office repair or reinstall. Suggest you do so if you haven't already.

I have both A2010 & A2016 on the same PC. Happy to test if you want to upload the relevant part of your database

 

[sxschech]

Thanks for all your advice and suggestions. I tried the Office Repair and waited through the 156 MS updates (coincidence?). Problem persisted. I also fired up the old computer and it has the same problem (at least when using the newest iteration after fixing the missing refs). Perhaps problem was there before and wasn't aware of it because at the time, I didn't have the second subform. That was added in order to track additional data that doesn't fit (apply) to the main form; supplemental data that is only captured for a specific category. I also tried changing the memos to short text. Note: The form and underlying table where the date picker is being used does not contain memo fields. The date picker worked as expected only after I deleted the two bound textboxes on the second subform. Also deleted table and form and recreated (did not add memo). Same result. So must have to do with how the subform is linked with main form and second subform? As long as don't have bound fields on the second subform all works as expected. If there are bound fields, then it puts the date in there. I'll see if I can put in some non proprietary data to take up the kind offer of those who would like to take a look and see if there is another reason. I think it must have to do with the form subform parent child thing?

 

[sxschech]

I have attached a copy of the file with the data stripped down. In case someone may see what may be causing date picker to not appear to work as expected. The default form will appear once the file is opened. To see the issue:

Round 1 (No Date Picker)
1. Click the drop down for Acronym (combo on top gray area of form) and select the first item "C"

2. Click on any ActivityDate (blank or with an existing date) and notice that the Objectives and Major Subjects are displayed.

3. Change the date (row is now in edit mode) and then after date is changed all is fine

Round 2 (Use Date Picker)
1. Switch to design view of form and change property of ActivityDate for "Show Date Picker" from Never to For Dates.

2. Close and Save Form and switch back to normal view

3. Click the drop down for Acronym (combo on top gray area of form) and select the first item "C"

4. Click on any ActivityDate (blank or with an existing date) and notice that the Objectives and Major Subjects are displayed.

5. Change the date (row does not appear to be in edit mode) and then after date is changed the text in the Objectives now shows the date rather than the objectives text and the activity date field is unchanged. Additionally, it can not be undone by pressing escape key since the pencil icon does not show.

Round3 (optional)
1. Go to Design View
2. Delete text boxes for Objectives and Major Subjects
3. Return to Normal View
4. Try changing date using date picker, it works as expected.

 

[isladogs]

I've had a look at this & believe the issue is somewhere in the code for your subforms frmProducts_SubObjectives and frmProducts_SubStatus

First of all do you have any lookup fields in your table designs?
If so, these could well be the cause of your issues

Secondly the data displayed in the Objectives & MajorSubjects textboxes of your subform frmProducts_SubObjectives make no sense to me

The form is based on the table tblObjectives. There is ONE record for product codes C6OD17 & C7OD17 & no records for C8OD17

When I click on a record in the other subform frmProducts_SubStatus it should show the record corresponding to that product code (if any) BUT it shows TWO different records for C6OD17 & C7OD17. One is real but where does the second record shown come from in each case?

I also think the master/child fields used for frmProducts_SubObjectives may be an issue.

So I haven't got an answer to your date picker issue. But if you can sort that lot out, I wouldn't be surprised if the rest drops into place

 

[sxschech]

Thank you for taking a look at this. There are no look up fields in the table design, unless Access created them without my knowing.

We are tracking two types of Events (Courses -"4" and Conferences - "5"). Courses contain all the data that are provided from an online extract csv. However, for conferences, the online extract contains data for the conference as a whole, while the data I will be tracking need to be done on the topic level. So in the sample for a conference, I provided 3 topics out of 10 topics from one conference title. C6, C7, C8. The objectives and major subjects only appear once for each topic and are "bullet points" which are copied and pasted into the text boxes from an external file (word or pdf). I manually remove the bullet symbols so each objective contains plain text with hard returns which is probably why you didn't notice that there was more information in the field for each record. If you open the zoom box, you can see that the field contains multiple lines of data (which is why I pasted test data indicating Line 1 and Line 2). Currently Topic 8 C8 did not have data so I did not put in test data which is why it is blank. The data can be added any time by pasting or manually keying it into the box when it becomes available. Since I would like to see the objectives for a given topic and can't derive it from the EventCode, and because I don't care which activity is the active row in the frmProducts_SubStatus form, I tried to link by Product Code so that if I click a row that contains C6, it will show the appropriate Objectives in the other subform frmProducts_SubObjectives.

Hope this makes things slightly clearer, but of course understand if doesn't help to understand the set up.

 

[JHB]

Something strange is going on in your forms/database, it seems that the last "subform" inserted in your "mainform" keep the focus.
So open the main form in design view, delete the sub form frmProducts_SubStatus, close the main form and save it.
Then open the main form in design view again, insert the sub form frmProducts_SubStatus, close the main form and save it, then it should work.

Ps: I've seen nearly the same behaviour for long time ago, (10-12 years).

 

[isladogs]

Hi JHB

Interestingly, that was one of the things I tried & it still behaved oddly afterwards!

However, I've tried yours & it does now seem to work

 

[JHB]

Hi JHB

Interestingly, that was one of the things I tried & it still behaved oddly afterwards!

Hi Colin.
I just downloaded it again and did as I wrote (delete/add) - it works.
Did you save the form after you deleted the sub form?

 

[isladogs]

Hi Colin.
I just downloaded it again and did as I wrote (delete/add) - it works.
Did you save the form after you deleted the sub form?

I certainly thought I had at the time but it was late last night so perhaps I forgot?
I also decompiled, recompiled, compacted ...

Anyway, it definitely seems to be fine now

 

[sxschech]

Thanks JHB and Colin, your suggestion solved it. Followed steps as below:

• opened frmProducts in design view
• deleted frmProducts_SubStatus
• closed and saved the form
• compact and repair
• open frmProducts in design view
• added frmProducts_SubStatus
• compiled
• save
• close access
• decompile
• compile
• compact and repair
• close access
• reopen
• test using date picker and it works as expected