By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,325 Members | 1,920 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,325 IT Pros & Developers. It's quick & easy.

DtPicker - weird behaviour

Expert 5K+
P: 8,434
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.
Nov 29 '06 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,661
Try setting the .Visible property (Boolean).
This should have no effect on the control at all.
Nov 30 '06 #2

Expert 5K+
P: 8,434
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).
Nov 30 '06 #3

Expert 5K+
P: 8,434
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? ;)
Nov 30 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 30 '06 #5

Expert 5K+
P: 8,434
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).
Nov 30 '06 #6

Expert 5K+
P: 8,434
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...
Expand|Select|Wrap|Line Numbers
  1. dtSubFrom.Visible = chkSubmitted.Value
  2. dtSubTo.Visible = chkSubmitted.Value
Due to the abovementioned insanity, here's the code I ended up with...
Expand|Select|Wrap|Line Numbers
  1.   If chkSubmitted.Value Then
  2.     ' dtSubFrom.BorderColor = vbGreen
  3.     ' dtSubTo.BorderColor = vbGreen
  4.     dtSubFrom.Visible = True
  5.     dtSubTo.Visible = True
  6.     dtSubFrom.CustomFormat = dtSubFrom.CustomFormat
  7.     dtSubTo.CustomFormat = dtSubTo.CustomFormat
  8.   Else
  9.     ' dtSubFrom.BorderColor = vbRed
  10.     ' dtSubTo.BorderColor = vbRed
  11.     dtSubFrom.Visible = False
  12.     dtSubTo.Visible = False
  13.   End If
  14.  
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!
Dec 1 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Why do these things always happen to you ? :D


Mary
Dec 1 '06 #8

Expert 5K+
P: 8,434
Why do these things always happen to you ? :D
I can't help wondering the same thing.
Dec 1 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
I can't help wondering the same thing.
Have you upset anyone lately with the power to put a hex on you ?
Dec 1 '06 #10

Expert 5K+
P: 8,434
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...
Dec 1 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 1 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.