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

[ItsMe]

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.

I meant post #13 to be addressed to Bob. Bob mentioned the BeforUpdate thingy and wanted to reply to his comment.

[ssanfu]

Originally Posted by ssanfu
You posted while I was typing.

I should have been more specific..... I meant that for BobBridges...


I will strive to be more articulate in the future.

[ItsMe]

I should have been more specific..... I meant that for BobBridges...


I will strive to be more articulate in the future.

Funny stuff. I think I see a train coming.

[BobBridges]

I am not sure why you are still interested in the "BeforeUpdate" event.

Oh, that's easy. I was looking at BeforeUpdate originally because I wanted to check the information Before the record was Updated, and modify it if necessary. And I was even more interested in it after our conversation because—as I thought—you two were agreeing that it's the better way to go:

....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.

It was only a few minutes ago that I realized that the example ssanfu sent uses not BeforeUpdate (which I expected) but AfterUpdate. And I was just reading in the MSDN on the BeforeUpdate event and ran across this: "A run-time error will occur if you attempt to modify the data contained in the control that fired the BeforeUpdate event in the event's procedure." I was looking at that, broken-hearted, when I saw that you'd posted.

Ok, I reluctantly stipulate that I cannot use BeforeUpdate to look at a text box's contents and modify it (if necessary) before letting it be passed on to the table. I'd still like to combine the simplicity of a bound control, for the reasons you listed, with the ability to edit it before letting the table see it. I can make that work with the Change event. But the way I'm doing it now is unsatisfactory; and the only way around it that you guys know of, or at least that you've mentioned so far, is an unbound control for the date. Am I right so far?

If so, and if I decide to abandon the Change event, then we're talking about two controls, one unbound to receive the user's input and the other bound to make it simple to update the table. However, I don't want the user to see two controls; no need for him to be confused by it. (I'm the only user, but still.) Besides, it takes up extra room, and I like my forms to be compact. Below is a screen shot, by the way; the form we're talking about is on the bottom right.

So if I can, I want to have the unbound control showing to the user, and the bound control invisible; my code will edit the unbound data and then copy it to the bound control, and that'll go into the table, and everything's hunky-dory. Right?

But when displaying the source data in the first place, how does the date get into the unbound control? Is there an event that fires when the record is read that I can use to copy the data the other way? Remember, I said I'm ignorant.

[ItsMe]

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.

Like Steve mentioned previously. You do not need to display the timestamp. In the case of email, you may want a visual aid or a MsgBox telling the user all is well and the timestamp was successfully saved. Having some yes no fields or a pull down menu distinguishing one type of record from another could help the form determine how to show or hide the controls.

The reason I mentioned the unbound textbox in the first place was to give you a place to "paste" your copied email date and time before formatting it as a date and editing the record. So you will need to have this visible at least some of the time. However, you could hide the unbound textbox (textbox properties .visible = true) behind a control button and paste the following in the click event.

Me.txtUnbound.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, acPaste, , acMenuVer70
Me.cmdNewButton.SetFocus
MsgBox "Mission Accomplished"

[ItsMe]

I see you are using continuous forms so the show and hide thing is out. But your code can still determine what type of record it is currently working on and how to handle user input.

[BobBridges]

....when displaying the source data in the first place, how does the date get into the unbound control? Is there an event that fires when the record is read that I can use to copy the data the other way?

You do not need to display the timestamp.

There are some kind of weird cross-purpose assumptions going on here, something I must have described wrong. Of course I need to display the timestamp; that's most of the purpose of the form, to display the contact log. For each contact, this form shows when, whom, and who said what. But of course when I need to enter a new contact, it must be able to accept new data too.

Look, it sounds as though something I assumed was clear all along actually wasn't, so let me start over and spell out more; and please don't take it as an insult if I explain something you already understood. I have a form—a "bound" form, I think you said it should be called—that a) displays multiple records from a table, and b) receives new data to be inserted into the table. In the detail section of that form, there is a text box that holds the time stamp for each contact, and a memo field where I describe who said what. These two fields are bound to fields in the table, you understand; they occur on the form once for every record. I can show you a screen shot of the form if you think it would make it clearer.

When the form opens, it displays all the contacts I've had with a particular person. Since both controls are bound to the table, they're both automatically populated from its records. If I'm opening the form in order to review, then it has already served its purpose; I see the various "timestamps" (sorry, ssanfu) and what was said. But maybe I also want to record a new conversation with the same person. If that's the case, I jump to the bottom of the form, where the "new" record is, and type in a date/time, or paste it in from some other source, or just allow it to remain the default of Now(). I also, of course, record some note about what was said. Since these are bound controls, their values are inserted automatically into a new record in the table. It's also possible that I'll need to update the date/time field for a particular entry ("oh, wait, that was earlier in the day"); in that case I just type or paste over the old value, and since this is a bound control the new value automatically updates the old field in the table.

Now in some cases when I paste in a value, that value is not a valid date as Access understands it. I've written code to intercept and fix some of the most common departures from date/time validity before Access gets a look at the date. It works great when I'm pasting in a value; but when I happen to be typing manually it can be unsatisfactory because I'm using the Change event to do the editing. What I'd like is a) to write the code so that using the Change event will work more smoothly—that would probably involve the module "knowing" when I'm done typing—or b) to use a more convenient event, one that is triggered after I'm through entering the new data but before Access gets a look at it. It has to be after I'm through because if it happens while I'm still typing the field then it's no improvement on the Change event; and it has to be before Access sees it, because if Access sees such a date it'll generate an error message and it'll be too late for me to do any editing on the value. (I thought BeforeUpdate would be the way to go, for this purpose, but apparently not.)

Since I'm starting over, I'll ignore what we've said so far about unbound controls (at least at first): what I want to know is whether there's a way I can edit a bound control before Access sees it?

The answer is Yes, there is; but it's the Change event, which as I said is proving unsatisfactory. Is there a better way to indicate to my Change-event code when it's safe to edit the data? Or failing that, is there another way to edit a bound control before letting Access see it?

[ItsMe]

...... is there another way to edit a bound control before letting Access see it?

If you want to edit a bound control's properties via VBA you most certainly can. As you have experienced, it may not give you the results you are looking for.

You might find this of interest

https://www.accessforums.net/access/...tml#post189543