Source Control data binding to ActiveX control property

[Christopher Glaeser]

A third-party ActiveX control ctDropDrop has an integer Value property that
is the number of days from 1/1/1900, and my table has a field ScheduleDate
with type Date. Can I use a form's Source Control to bind Value to
ScheduleDate, perhaps using conversion routines, or do I use event
procedures to copy the date from/to the form text box and ActiveX property
value? I'm guessing the answer is the latter, but want to make sure I'm
using the simple approach.

Best,
Christopher

 

[MacDermott]

The form doesn't have a ControlSource property, but the ActiveX control
does.
And yes, that's what you'd use if you want to bind the control to a
(calculated) field in your query.
However, bear in mind that calculated fields are not updatable.
So you can use this technique to display your data in the ActiveX control,
but not to edit it.

I'd hazard a guess, since you're using this ActiveX control, that you're
wanting to be able to edit the value.
For that, as you surmised, you're better off capturing whatever event the
control raises (perhaps an AfterUpdate?) and running code to synchronize the
value in the control with a textbox bound to your date/time field. (Note
that the textbox doesn't have to be visible.)

HTH

 

[Christopher Glaeser]

So you can use this technique [ControlSource] to display your data in the

ActiveX control,
but not to edit it.

Oh, that explains why I get an error when attempting to change the date via
the ActiveX control. OK, scrap that alternative. I will use events.

I'd hazard a guess, since you're using this ActiveX control, that you're
wanting to be able to edit the value.

Yes, you are right.

For that, as you surmised, you're better off capturing whatever event the
control raises (perhaps an AfterUpdate?) and running code to synchronize
the
value in the control with a textbox bound to your date/time field. (Note
that the textbox doesn't have to be visible.)

At first, I thought using the events would be reletively easy. Use the
Form_Current event to copy the value of the form field to the control, and
use the Control_Change event to copy the value back. However, after a
couple hours playing with the control, I realize there are other issues to
deal with that include ...

- On the initial copy of the form value to the control, that fires that
Control_Change event, which then copies the value back to the form. To
resolve this issue, I compare the values of the form and the control, and if
they are the same, I don't do a copy.

- The form value could be null. Not sure the bast way to resolve this. The
Control Value is an integer that counts days starting 1/1/1900. Perhaps I
could use -1 to mean null.

- Also need to handle Undo.

I'm guessing there is a template or common coding technique for solving this
problem, probably done in 20 lines of VBA and three or four events. I have
a copy of Alison Balter's book. I'll check for an example. Any suggestions
or pointers will be greatly appreciated.

Best,
Christopher

PS: According to the control documentation, the control has two names for
the value, Text and Value. Text is a string similar to Date, as in
"1/1/2005", and Value is an integer counting days from 1/1/1900. Many of
the properties, including Value, are known to the VB autocompletion, but
Text is not know to VB autocompletion. In other words, I can type
Me.ctDropDate. and Value will be listed, but not Text. Why would that be?

 

[MacDermott]

I'm not sure what you're doing with the Control_Change event here.

I'd set the value of the control to the value of the textbox on the
Form_Current event.
After a change, I'd use the control's After_Update to write the value back
to the textbox.
True, this doesn't fire until you leave the control, but the advantage is it
should only fire after the control has been updated from the keyboard - not
if it's updated programmatically.
At least that's the way native Access controls work.
Which does bring up the question, since it sounds as if your 3rd
party software is some sort of calendar control, why you're not using the MS
Calendar Control or Date Picker?

As for why some of the properties appear in Intellisense (MS name for what
you're calling autocompletion), that would depend on how the control was
written - something we don't have a lot of insight into from here.
It does suggest, however, that the control may not have been written with
Access in mind -
rather a red flag, since not all ActiveX controls work reliably with
Access.

HTH

 

[Christopher Glaeser]

I'd set the value of the control to the value of the textbox on the

Form_Current event.
After a change, I'd use the control's After_Update to write the value back
to the textbox.

OK thanks, I did that, and things work pretty well. Now how should I
implement Undo. As best I can tell, Form_Undo is called before the Undo is
performed, so it is too early to just copy the value from the form back to
the control, and I don't see a Form_AfterUndo. So, if I'm right about the
event timing, then I need to use another cache for the old value. The
properties of the control include Value and OldValue, but there is no
documentation for OldValue. I'm guessing OldValue is a place holder for me
to set in Form_Current, and then reference in Form_Undo to reset the
control? Does that sound right? I'll give it a try.

At least that's the way native Access controls work.
Which does bring up the question, since it sounds as if your 3rd
party software is some sort of calendar control, why you're not using the
MS
Calendar Control or Date Picker?

Good question. I am using the DBI Tech contorl for a multi-day calendar
(it's pretty slick). Since I am already using one of their controls, I
thought it might simplify installation on our other machines with one set of
controls to track.

Best,
Christopher

 

[Christopher Glaeser]

I'm guessing OldValue is a place holder for me to set in Form_Current, and

then reference in Form_Undo to reset the control? Does that sound right?
I'll give it a try.

Nope, that did not work. Apparently, OldValue is read only. So, perhaps
the control maintains it, but without documentation, I don't know when it is
set. So, I created my own old value myOldValue. I set it in Form_Current,
and then referenced it in Form_Undo. With minimal testing, it seems to be
working.

Best,
Christopher

 

[Brendan Reynolds]

In the Before Update event, OldValue contains the old value of the control,
before it was changed. After the Before Update event, OldValue returns the
same value as the Value property. If you want to make any use of the value
of OldValue after the Before Update event, you will need to write code to
store it, for example, in a module-level variable.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[MacDermott]

That would be the BeforeUpdate event of the form, not the control, wouldn't
it?
And isn't Undo in general only available before the record has been saved?

It would seem that an Undo would restore all the record's OldValues,
including the one in the textbox.
So the only problem would be to get the 3rd Party control updated with the
Old Value as well.

The question then becomes -
what is invoking this Undo?
If it's an Undo button, it's easy to add code to update the 3rd Pary
Control.
If it's being invoked by using the Esc key or some other shortcut, it
will be a little more work, but still quite doable.

- Turtle

 

[Brendan Reynolds]

I've given up on Undo events. In my experience, pretty much anything I've
ever wanted to do with an Undo event would require a Before Undo event and
an After Undo event. A single event is, in my experience, not useful. I
played around with trying to keep track of how often the Esc key had been
pressed, but that gets really messy. If I really *must* update the state of
a control after an Undo operation, I use the Timer event. For example, if I
wanted to ensure that the Value property of an ActiveX control was kept
synchronised with a text box on a form, I would set the TimerInterval
property to 250 and add code something like the following to the Timer event
procedure:

If Me!TheActiveXControl.Value <> Me!TheTextBox.Value Then
Me!TheActiveXControl.Value = Me!TheTextBox.Value
End If

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Christopher Glaeser]

It would seem that an Undo would restore all the record's OldValues,

including the one in the textbox.
So the only problem would be to get the 3rd Party control updated with the
Old Value as well.

Correct. And without documentation for the control OldValue, I don't trust
it to have what I need. I'm guessing the control does not know why Value is
changed (it could be Form_Current, or it could be the user clicking on a
series of updates). So, I'm guessing OldValue is updated every time Value
is changed, which is not very helpful. What I need is the OldValue when
Form_Current fired, and not the values between then and Form_Undo. So, I
created my own variable myOldValue that is set in Form_Current, and
referenced in Form_Undo (which I think is really Form_BeforeUndo as best I
can tell, though it is named Undo).

The question then becomes -
what is invoking this Undo?
If it's an Undo button, it's easy to add code to update the 3rd Pary
Control.

That's what I did. It seems to work in simple cases, though this is my
first Access project, so I've probably overlooked something.

If it's being invoked by using the Esc key or some other shortcut, it
will be a little more work, but still quite doable.

I think it works with both Edit | Undo and Ctl-Z. When Form_Undo is called,
I just reset the control value to myOldValue. Here is my code thus far.
The control value counts days from 12/30/1899, so the conversion to Access
dates is a bit tedious to include support for Null dates, but that issue
aside, Form_Current copies the form value to the control value,
control_Update copies the control value to the form value, and Form_Undo
resets the control value back to the forms value at the Form_Current event.
Comments graciosly welcomed.

Dim myOldValue As Variant

Private Sub Form_Current()
' Copy the form value to the control value if they differ
If IsNull(Me.ScheduleDate) Then
If Me.ctDropDate9.Value <> 1 Then
Me.ctDropDate9.Value = 1
End If
ElseIf Me.ctDropDate9.Value <> DateDiff("d", "12/30/1899",
Me.ScheduleDate) Then
Me.ctDropDate9.Value = DateDiff("d", "12/30/1899", Me.ScheduleDate)
End If
' Save the value in the event of Form_Undo
myOldValue = Me.ctDropDate9.Value
End Sub

Private Sub Form_Undo(Cancel As Integer)
' Restore the control the the old value
Me.ctDropDate9.Value = myOldValue
End Sub

Private Sub ctDropDate9_Updated(Code As Integer)
' Copy the control value to the form value if they differ
If IsNull(Me.ScheduleDate) Then
If Me.ctDropDate9.Value <> 1 Then
Me.ScheduleDate = DateAdd("d", Me.ctDropDate9.Value,
"12/30/1899")
End If
Else
If Me.ctDropDate9.Value = 1 Then
Me.ScheduleDate = ""
Else
If Me.ctDropDate9.Value <> DateDiff("d", "12/30/1899",
Me.ScheduleDate) Then
Me.ScheduleDate = DateAdd("d", Me.ctDropDate9.Value,
"12/30/1899")
End If
End If
End If
End Sub

 

[MacDermott]

In a sense, that all looks good enough -
certainly for a "first-timer".

The following comments are a bit of "icing on the cake", but may be useful
as you go forward:
1. Since ctDropDate9 is unbound, I wouldn't expect its OldValue property to
be meaningful.
However, ScheduleDate is bound to the underlying recordset, so its OldValue
should always contain the value last derived from that recordset. Thus in
your Form_Undo event, you could use
ctDropDate9.value=ScheduleDate.OldValue
and not have to use an extra variable to store that information.

2. When you reference a date as a constant in code (12/30/1899), you're
better off using # as a delimiter than ".
Since your code is working, it appears that Access can make the conversion,
but # indicates a date/time value, while " indicates a string value, so
you're making Access work harder than it needs to.

3. Probably the reason that the developers of the DropDate control chose to
base their .Value property on 12/30/1899 is that Access uses the same
formula.
Try evaluating this in the immediate window:
CInt(#12/30/1899#)
You'll get 0.
Access stores dates and times as an integer representing the number of days
after 12/30/1899 (plus a decimal representing the fraction of a day given by
the hours, minutes, and seconds).
Armed with this knowledge, you can probably simplify your code
considerably...

HTH
- Turtle