Inserting time into Access 2010 query field when a charater is entered in that field

[MPXJohn]

Hi all, Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently. Thanks.

[John_G]

If you are entering data directly into the query, you can't make it do that, because that would require event code, which queries don't have.

Entering data directly into the query is bad practice anyway - you should be using a form, because it gives you a lot more control over what you can do.

With a form, you can use the On Change event of a textbox to accomplish what you want to do. Just put me![textboxname] = time() in the On change event for the textbox.

[MPXJohn]

I am trying the offered solution without success. I created a form from my query. When I put in the recommended code I receive an error that says "The object doesn't contain the automation object me!"

[John_G]

Where did you put that code - can you post it, please?

"Me!" is a reference to "the current form", and it can only be used in code contained in the form. In your case, the code would be in the On Change event Procedures for one or both of the of the text boxes on the form.

[MPXJohn]

Here is where I put the code. Event tab under properties. On Change. Although I am not sure this attachment worked properly.
Time.zip

[John_G]

The code is fine, but you need to put it into an Event Procedure, instead of directly in the propeties list.

- On the property sheet, there is a down-arrow on the right side of the On Change line. Click the down-arrow, and select "Event Procedure" from the list.
- Click the ... button to open the VBA code window; you will see something like this:

Private Sub Time_In/wire_Change()

End Sub

- Enter your code line me![Time In/Wire] = time() between those two lines.
- Close the code window, save the form changes and give it a try.

Just a note in passing - you should avoid using special characters (such as "/") in field names; they can sometimes confuse Access, especially if you dont have the square brackets around them.

[MPXJohn]

Excellent! That worked. Here is another issue with it though that I never thought of. What if I need to go back in and correct that time that is in there. It doesn't seem to want to allow that. It actually changes it to the current time because I am in the field.

BTW. Thanks for the tip on the special characters. I'll pas it along.

[John_G]

What if I need to go back in and correct that time that is in there. It doesn't seem to want to allow that. It actually changes it to the current time because I am in the field.

Yes, that is because the On Change event fires each time you type a character, because each one is a change.

A solution would be to only put the time in if the previous value was null. For this there is a very convenient property called "Oldvalue", which contains the textbox value before you made any changes. If that value is Null, there was nothing in the textbox, so put the time in:

  If IsNull(Time_In/wire.OldValue) Then
    Time_In/wire = Time()
  End If

[MPXJohn]

Tried this code and received a compile error. Do I need use this code in addition to the previous? Do I insert it directly after me![Time In/Wire] = time()?

[John_G]

Sorry - I wasn't very clear there. The three lines of code I showed you replace the one line of code you had before.

The time value is only automatically inserted if the textbox was empty to start with.

The square brackets are required because you have a space in the textbox name.

Private Sub Time_In/wire_Change()
  If IsNull([Time In/wire].OldValue) Then
    [Time In/wire] = Time()
  End If
End Sub

[MPXJohn]

Awesome, Thanks for the help. Works like charm & I keep learning.