DtPicker - weird behaviour

[Killer42]

Hi all.

Short version: DtPicker goes strange when .Enabled changed in VBA code, looking for solution or good workaround.

Looooong version:
Those of you who have been following various threads here will know that I have a fairly large Access 2003 database (actually split over multiple backend databases and a front-end), which users search by entering criteria on a form.

Among these criteria are three date/time fields. For each, I have a pair of date/time picker controls in which the user chooses a from and to value, to limit the search to a date/time range. Initially I was simply checking the values and generating WHERE clauses based on whether they had been changed from their default values. However, this is proving more complex than I like, due to relationships (real-world, not database) between the three pieces of chronological information. So I’ve placed a checkbox next to each pair, with the intention of requiring the user to “turn on” each criterion they want to use. (Normally the user won’t be interested in these criteria).

Here’s my problem, though. When the checkbox is clicked and I turn off the .Enabled property of a date/time picker, it loses it’s custom format (which shows date and month down to the minute) and switched back to showing just the short date. Worse still (much worse), when enabled again, it turns into a blank box, and the date/time entry field appears at the top-left corner of the form. Turning .Visible property on or off does seem to work, but I would prefer to leave the control visually disabled rather than hide it completely. If I do that, I'll have to put something else there to indicate what the checkbox is for (since unlike VB6, Access helpfully hides the label along with the control.)

I don’t know what we can do about this, but figured I’d ask anyway.

On the other hand, does anyone have a good suggestion on another way to visually enable/disable the date/time picker controls when the user hits the corresponding checkbox? For the moment I have placed a red border around each one, and change it to green to indicate it’s enabled. Doesn’t look so good. :( I tried turning the .Locked property on/off, but it doesn’t seem to have any effect at all. Can’t find a ForeColor or BackColor property. Tried setting the CalendarBackColor (I think) but it only affected the popup calendar.

[NeoPa]

Try setting the .Visible property (Boolean).
This should have no effect on the control at all.

[Killer42]

Try setting the .Visible property (Boolean).
This should have no effect on the control at all.

Thanks NeoPa.

However, I have tried that. There are two problems, one minor and one which I consider a show-stopper.

• When hidden, you have a checkbox sitting there for no apparent reason.
  This is the minor nuisance, it just means I would need to put something else on the form to indicate that this particular option is turned off.
• When I make it visible again, it loses the custom format and goes back to the ugly short date format.
  This is a real problem of course, since it is (A) in Yank format, and (B) meant to include the time.

I've noticed quite a bit of weird behaviour like this from the dtpicker in Access. It seems to lose the custom format "at the drop of a hat". Almost anything I do causes it to revert to the American short date format. (At least I think that's what it was - haven't checked for a while).

[Killer42]

Try setting the .Visible property (Boolean).
This should have no effect on the control at all.

Seems a bit pointless then, wouldn't you say? ;)

[MMcCarthy]

If the control is locked the user can't change the value in the control.

You said you are using a custom format. Try setting it on the table instead of the control. This may solve your problem.

Mary

[Killer42]

If the control is locked the user can't change the value in the control.
You said you are using a custom format. Try setting it on the table instead of the control. This may solve your problem.

It's all unbound. The value is only being used in code to set up a query, once the user clicks "Go".

And the weird thing is, the .Locked option doesn't stop the user changing the value, as I had thought it would. Perhaps it only applies when bound to a field?

Thanks, anyway. I'm thinking I might give up on it for now, and just use either the silly-looking borders, or set .Visible on and off, and try to re-set the custom format each time (blah).

[Killer42]

Well, it's official - my date/timepicker control has gone insane.

I am now turning the .Visible property on and off when the user clicks the corresponding checkbox. Here's the code I intended to use in my checkbox click sub...

dtSubFrom.Visible = chkSubmitted.Value
dtSubTo.Visible = chkSubmitted.Value

Due to the abovementioned insanity, here's the code I ended up with...

  If chkSubmitted.Value Then
    ' dtSubFrom.BorderColor = vbGreen
    ' dtSubTo.BorderColor = vbGreen
    dtSubFrom.Visible = True
    dtSubTo.Visible = True
    dtSubFrom.CustomFormat = dtSubFrom.CustomFormat
    dtSubTo.CustomFormat = dtSubTo.CustomFormat
  Else
    ' dtSubFrom.BorderColor = vbRed
    ' dtSubTo.BorderColor = vbRed
    dtSubFrom.Visible = False
    dtSubTo.Visible = False
  End If
 

That's right - after making the control visible again, I have to restore the custom format to the same value which is already in it :(. Turning on .Visible (or possibly turning it off, who knows - or cares) switches it to displaying short date format (not American, I was mistaken). Setting the custom format overcomes the problem, even though the property already has the right value.

This is close enough - I give up!

[MMcCarthy]

Why do these things always happen to you ? :D


Mary

[Killer42]

Why do these things always happen to you ? :D

I can't help wondering the same thing.

[MMcCarthy]

I can't help wondering the same thing.

Have you upset anyone lately with the power to put a hex on you ?

[Killer42]

Have you upset anyone lately with the power to put a hex on you ?

Well, only Mary.

Oh no, did I actually type that? Quick, where's the delete...

[MMcCarthy]

Well, only Mary.

Oh no, did I actually type that? Quick, where's the delete...

I WILL have my revenge! Watch out.

That's both you and Adrian with a black mark.

Hmm, have to consult my spellbook. Eye of Newt, leg of frog, now what was that other incredient for giving someone boils in unpleasant places. Never mind it will come back to me eventually.

Mary