Linked or unlinked date control? and what event to edit?

[BobBridges]

What's the best way to display a table's date values in a form, while still allowing a user to enter non-date values into the same field for input?

One of my Access tables is a contact log with a timestamp field. Up 'til now, in the form that displays the table I've linked the timestamp control to the table's timestamp field, and defined the control as plain text, format "yyyy-mm-dd\ hh:nn", with a default value of Now() for new records. That works when I'm logging phone calls right away. But when I'm logging emails, I cut and paste the date from the email; and that value is often something not wholly date-like, for example "Sent: Fri 2013-09-13 10:49". I've taught my program to spot and remove "Sent:" and "Fri", leaving just the part of the date that Access can handle; this allows me to cut the original timestamp a little more carelessly, you see, which is convenient. It works ok.



But what's the best way to do it? I want the program to intercept the input data and clean it up, if necessary, before Access notices that it's not a valid date and spits it back out; for that I've been using the Change event, and it works great when I'm pasting. But when I'm typing in the value manually, the Change event triggers with each keystroke, so I've had to have it check each keystroke and make sure I've typed enough characters before it tries to edit the date; and often that doesn't work too well.

I was looking for a better event, this evening, and I remember why it can't be BeforeUpdate: That event turns the date over to the control (I suppose it's the control) before my program can clean it up. I'm prepared now for you all to tell me "you doofus, you can't make it a date field if it might not be a date field; you'll have to unlink the control, clean it up and then put it into a date field". I'm prepared to hear that, yes—but I'm not sure how to do it. If it's not linked to the timestamp field in the table, how does it get out to the control when records are being displayed? Of if that isn't the answer, what is?

[ssanfu]

As far as I am concerned, a "timestamp" field should NOT be editable. The field is to be able to determine WHEN the record was created. If you can edit the field, what good is it as a time stamp??

In fact, the timestamp field should generally not be visible.

If you want a field that will handle dates/times for emails and phone calls, add another field (maybe a text field??).

the Change event triggers with each keystroke

Shouldn't be using the change event...

My $0.02....

[ssanfu]

Thinking more about the "How", if you had a text field, you could paste in email date. Using the form beforeupdate event, if the text box was Null, the code could put the timestamp data into the text box. If the text box was not Null, the code could edit (clean up) what you pasted in from the email.

Now my $.04 worth....

[BobBridges]

If by "timestamp" you mean the date and time the record is created, then this is not a timestamp; it is, as I said, the date and time that the contact occurred. I have no problem calling it something else; pick a term that pleases you.

I was starting to go in the direction you're pointing me, but I'm not sure how all the parts should hook together. What I did before—and I freely admit I didn't know what I was doing, so I just cobbled something together that sort of worked—was a text box with these characteristics:

Format: "yyyy-mm-dd\ hh:mm"
Control Source: Time (the corresponding field in the table)
Text Format: Plain Text
Input Mask: -nothing-
Default Value: Now()
Validation Rule: -nothing-
Event: On Change, I edited the control's .Text property to be sure that pasted-in text had a better chance of being a valid date. BeforeUpdate wasn't working for me, because Access complains about invalid date format before that event is triggered. I surmise (still not knowing what I'm doing) that this is because I have a Format set; even though the new record isn't being sent to the table yet, the form itself gets a look at the Format before the BeforeUpdate event is triggered.

("Wait", you say, "how could you be changing the text box's Text property in the Change event? That would trigger Change yet again, causing it to loop until it runs out of stack space." Yes, it does; so as a kludgy workaround I created a Boolean flag to keep the Change procedure from recursing. I'm not proud of it; I just couldn't think of a better way, at the time. Now I'm ready to learn a better way.)

Now, you're saying I should use BeforeUpdate to edit the text, and that's fine with me—makes more sense, in fact. But which of these properties must I change to make that work? In particular, a) can I still use Control Source: Time (and if not, how do I get it from and to that field)?, and b) do I write text back to the text box, or a value, or what? I can experiment with this, but that's how I got into this fix in the first place, some years ago.

[BobBridges]

I just experimented: I erased the text box's Format property, and I'm editing the input text using BeforeUpdate. But when it comes time for my VBA code to set the control's Text or Value property—I tried both—I get an error message: "Run-time error '2115': The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field." I've tried assigning various valid and invalid date strings, to both properties, and the message seems to be the same every time.

I believe you when you say it's better to do it using BeforeUpdate. I just don't know how to make it work. What do I need to change? What (exactly) is causing the problem?

[ItsMe]

If by "timestamp" you mean the date and time the record is created, then this is not a timestamp; it is, as I said, the date and time that the contact occurred.

I can not understand where the conflict lies. If the difference between your email timestamps and phone timestamps are so extreme you can not declare them as Date data types before inserting them into the same column then you need two columns.

Try using an unbound textbox that temporarily stores the info, email or otherwise. After the user input is formatted how you want it, you can forward the variable to your ContactTime column within your table. You can use Boolean fields within your table to distinguish incoming versus outgoing and email verses phone communications. These Boolean values can help your form determine what and how to display your controls and labels as well as how to handle the string within the unbound textbox. The string can be parsed and formatted at anytime.

If you are using the copy paste method you could also use a control button to execute VBA to paste whatever text you last copied directly into your ContatTime field. The VBA could paste it into a hidden unbound textbox where another line of code would parse and format.

[BobBridges]

One of my Access tables is a contact log with a timestamp field....I've linked the timestamp control to the table's timestamp field, and defined the control as plain text, format "yyyy-mm-dd\ hh:nn", with a default value of Now() for new records. That works when I'm logging phone calls right away. But when I'm logging emails, I cut and paste the date from the email; and that value is often something not wholly date-like, for example "Sent: Fri 2013-09-13 10:49". I've taught my program to spot and remove "Sent:" and "Fri", leaving just the part of the date that Access can handle....

If the difference between your email timestamps and phone timestamps are so extreme you cannot declare them as Date data types before inserting them into the same column then you need two columns.

Hi, again, ItsMe. I think you read ssanfu's discomfort over my use of the term "timestamp" and confused two different issues. When I log a phone call, I typically either let the default value of Now() stand for the time, or type in a date and time manually; in that case no issue arises on the date format, unless of course I commit a typo.

But when I log emails, I usually cut and paste the Sent date from the email. An email date and time typically reads something like this: "Sent: Fri, Sep 20, 2013, 00:14 AM". So when this database was first getting started, I used to select just the part of the date that Access could understand as a date—"Sep 20, 2013, 00:14 AM"—and paste that into my form. Access recognized that as a date and time, stored it, and displayed "2013-09-20 00:14:00", per my preferred format. All was well.

But I got fancy: Since I do this a lot, for ease of use I wanted to cut the whole line from the email and make my program smart enough to find and discard the words "Sent:" and "Fri,". I did it, too, and the database is that much easier to use. Good so far.

But in order to do it, I used a sort of kludge. Now I want to figure out a better way. So in my first post I described a bit of what I've been doing, and in my second I went into more detail, and although it works, I'm hoping someone can tell me that there's a much easier, or more flexible, or anyway better way of detecting that my date field contains a non-standard date, and editing it, before Access notices and complains.

[ItsMe]

I wasn't trying to duel on the timestamp and create semantics. I think I understand.

Say you get a phone call. There is not a need to type something in the date field. Move to the next record. Now you get an email. No automation to retrieve the date data. Copy paste. Well, you have text and need to paste into what would otherwise be a date field.

So ignore what you quoted me posting for a while and focus on the rest of the post I provided in post #6. Ultimately, if you can't get it to work by holding your value in the form's memory and formatting you will need two columns/fields. I choose the former.

You are beating your self up with the bound textbox. It is BOUND to a data type! You told access to bind it so leave it at that and create a new tool to do the parsing and formatting to match what you previously told Access to do, i.e. bind the textbox control to a specific field with a specific data type.

Oh, and if you get all that to work. Put the timestamp default value at the table level.

[ssanfu]

Sorry for my misunderstanding - some terms have specific meanings. "TimeStamp", to me, means when a record is created, regardless of what ether dates are entered in other fields. This field is normally locked/hidden and unavailable to be edited for auditing purposes.

I was actually refering to the FROM before update event to edit the text string, converting it to a date.

The change event fires every time you type a character.... not a good event to use.

So I created an example dB (A2000 format). The actual date field defaults to today's date/time. You can paste in the string from an email - it must begin with "Sent:" and it will be converted to a date/time.

I used the control after update event so you can see it there is a valid date entered.

Is this closer to what you want???

[BobBridges]

Try using an unbound textbox that temporarily stores the info....After the user input is formatted how you want it, you can forward the variable to your ContactTime column within your table....You are beating your self up with the bound textbox. It is BOUND to a data type! You told access to bind it so leave it at that and create a new tool to do the parsing and formatting to match what you previously told Access to do, i.e. bind the textbox control to a specific field with a specific data type.

This is where my ignorance comes in. I gather that if a form's control C is "bound" to a table's field F, that means when reading existing records Access copies F's value to C; and when updating or creating records, Access copies C's value to F. But that's all I know. That "bound" has other implications I don't doubt, and it's not knowing what those implications might be, I think, that is causing my confusion and the trouble I'm having in trying to make my form work better.

I think you're giving me good advice; I just don't know how to implement it. How (for example) can I get a value from my unbound control into a field in my table? I imagine that's so simple a question that you're having trouble believing I don't know; but the problem is that bound controls is the only way I've ever done it before.

It's one of the disadvantages of learning everything I know from reading the references: There are unexpected and perhaps inexplicable gaps in my knowledge.

Wait—is it just an INSERT command? I've read about those.

If you are using the copy paste method you could also use a control button to execute VBA to paste whatever text you last copied directly into your ContatTime field. The VBA could paste it into a hidden unbound textbox where another line of code would parse and format.

I didn't think of that. I've done it in Excel, and in VBS, so I understand the concept of getting my program either to put something in the Clipboard or to get it out.

But in this case I don't know that it'll save me anything. I would certainly attach a keyboard shortcut to the button, so that I wouldn't have to reach for the mouse to copy it in; and I already have a keyboard shortcut to paste something, ie <Ctrl-V> or <Shift-Ins>. Although I guess in the form, such a shortcut would at least keep me from having to point at the correct text box first.

No need to type anything into the date field at all; if it's left blank, Access can fill it in when creating the record....Oh, and if you get all that to work, put the timestamp default value at the table level.

Hmm. The table field is default=Now(). Are you saying I don't need to make the form control default=Now() as well? It's a thought. I don't see that it hurts anything to have the value already in there, where I can see it (does it?).

---

I definitely don't need an extra field in the table; I've considered making it a free-form text field that could contain non-date items, but so far have rejected that idea, so until I change my mind this field will always have a valid date in it.

But I don't want an extra textbox in the form, either, if I don't have to. Are you saying a) that I have to, or b) that I might have to?

[ItsMe]

I am going to look at Steve's example and see what his thoughts are. As for the Now() default table, having it at the form level sounds redundant. If your form creates a new record the table will insert the time the record was created by the form (without the form telling the table what the default value is).

[BobBridges]

Ssanfu, that's approximately what I'm doing already, except I'm trying to do it with a bound control simply because I didn't know any better a few years ago. More specifically, it looks like this:

' Edit the timestamp to remove "Sent:" and the day of the week from in front.
Private Sub Timestamp_Change()
  If bEditingDate Then
    bEditingDate = False
  Else
    vt = Timestamp.Text
    pc = InStr(vt, ":")
    If pc = 0 Then Exit Sub 'user hasn't entered a time yet
    If Len(vt) - pc < 2 Then Exit Sub 'not done entering MM yet
    If InStr(vt, " ") = 0 Then vt = Format(Date, "yyyy-mm-dd") & " " & vt
    bEditingDate = True
    Timestamp.Text = EditDate(vt)
    End If
  End Sub

This is unnecessarily complicated, I now realize, because I didn't know some things back when I wrote it around 2005. For example, the Boolean flag is necessary to keep the routine from recursing every time I update Timestamp.Text. The documentation said that the Change event isn't triggered by VBA, but I discovered that to be untrue; so I added bEditingDate to kill the recursion. Just this week, however, I discovered that in the 2010 documentation there is a distinction: Changing the Text property triggers the Change event, but changing the Value property in VBA does not. If that turns out to be true, I can simplify this routine even if I stick with Change (although I hope I don't have to).

EditDate is a function I put in a common module because another form uses it too; it's responsible for removing both "Sent:" and the day of the week, if either or both appear.

Those other bits are necessary exactly because I'm using the Change event rather than BeforeUpdate; in case I'm typing in a date and time manually, the code isn't allowed to proceed until I've filled in a colon and two more characters (hour and minutes, you see). But that's a kludge; I'd much rather use BeforeUpdate, especially now that you two have confirmed that it's the better way to go.

As for the Now() default, having it at the form level sounds redundant. If your form creates a new record the table will insert the time the record was created by the form (without the form telling the table what the default value is).

"Redundant", I see what you mean now. I liked the idea of seeing what date would be used, before I hit <Enter>; but my programmer's soul is filled with horror at unnecessary redundancy (which term, come to think of it, is itself redundant), so maybe I'll do that after all.

Ok, you two have clarified my mind considerably. I think I can at last put my questions about bound controls cogently—in a separate post.

[ItsMe]

I am not sure why you are still interrested in the "BeforeUpdate" event. You have a nice working example with error trapping that updates the value of field "OGradDate". You want code like this when things work "out of sight" and behind the scene.

The code offered to you by Steve is in the AfterUpdate event of an unbound textbox. AFTER you update the unbound textbox the event fires and the VBA is executed. You can place the code in another event if you choose but, I doubt it will ever work in the BeforeUpdate event.

[ssanfu]

You posted while I was typing.

Actually, I used the text box control after update event. After editing the text, I pushed it into the date value into the Contact date/time control.

"Redundant", I see what you mean now.

Have the default value at either the table level or the control lever, but not both. Wither way, when a new record is created, the date/time will be visible.






What I was typing.....

I gather that if a form's control C is "bound" to a table's field F, that means when reading existing records Access copies F's value to C;

A table stores data. (kinda like a hard drive)
If you have two tables that are related, and you want to see records from both tables, you create a query - a "virtual table". It looks/acts like a table but is not a table.

A form is an object that can have (contain) other objects (controls).
A form can be bound or unbound.
A form that is unbound does not have a record source. It can contain controls, but those controls will be unbound.
An unbound form (with unbound controls) is kind of like RAM. You can add data to the controls, but when you close the form (ie turn the power off), the data disappears (like data in RAM).

If a form is bound, it has a record source, either a table or a query. Controls on a bound form can also be bound or unbound. If a control is bound, it is "tied" (bound) to a field in the record source (the table/query). The control will display whatever data is stored in that field. If a control is unbound, you can enter data into it, but it disappears when the form is closed.

If you have an unbound form and unbound controls, you can still view/add/edit/delete data in tables/queries, but you have to write ALL of the code to manage the data. Access takes care of all that for bound forms and controls. That is why the majority of forms are bound.

Crystal Long has a good site. It is geared more to wards the beginner (but I keep going back and reading it - learn/refresh every time).

Access Basics http://www.accessmvp.com/Strive4Peace/

[ssanfu]

"OGradDate"

I modified my test database, used a form with controls, created the code, then deleted everything else. The names are mine - not meant for BobBridges to change his object names.

The dB was an example..... just an example...