‘Lock-on-exit’ text box? (2002)

Topic

New Reply

Kind of a Newbie here so I hope my question isn’t too ignorant. I have created a text box for a date entry that uses an “On Got Focus” Machro to obtain the current time. Works great, except that it will, of course, change the time each time it is tabbed over by the operator. Is there a way to (perhaps with a “On Exit” or “On Lost Focus” machro) that I can “lock” the text box after the initial entry? There is NEVER a need to edit this entry once the original time has been determined. Thanks.

Reply | Quote

Replies

If you are logging the time that the record was created, set the default value for the field in the table to Now() and lock the textbox on the form permanently. This way, the time will be auto stamped when the record is created, can be viewed at any time but cannot be altered.

If this does what you want, there is no need for the code in the On got focus event.

Hope this helps

Reply | Quote

Thanks for your input Allen – much appreciated. That is the way I had it originally set up, but we found that the operators almost always completed an order and opened a new form. With the default of Now() the time they OPENED the form was entered into the time field, rather than the time they actually took the order. It can be several minutes (even hours at times) before they take another order. This makes the order entry “time” inaccurate. That was my reasoning in using the “On Got Focus” event to trigger the time entry – even though the form was open, an entry would not occur until they started taking the order. Any other thoughts?

Reply | Quote

OK I will assume that you have a form that is used for order entry as well as viewing or edting existing orders, and that the time text box is bound to JobTime.

When the order form is closed or moved to a new order, the ‘Update” event happens, which writes any changes back to the table. When a new order is entered, the form is in ‘DataEntry’ mode.

If you put the following code in the form’s before update event, then it will write the time that the order was finished and/or the form was closed.

Private Sub Form_BeforeUpdate()

if me.DataEntry = true then [JobTime] = Now()

End Sub

This says, if the order is a new one, then when you close, set the value of [Jobtime] to Now().

The DataEntry check will stop the value being changed if the order is edited later for some reason. As with my earlier suggestion, set the time textbox to locked so that the value can be seen later but not edited.

I hope this helps (and is right! I’ve only just started offering suggestions)

Reply | Quote

Woah! That was pretty scary! I got some kind of screen flash and the application closed.
I re-started and went back and deleted the entry from design view. This is what was entered:

Private Sub Form_BeforeUpdate()
If Me.DataEntry = True Then [Order Time] = Now()
End Sub

I really don’t have any experiance with VB so maybe the above entry has an error in it. Whatever, the app crashed when I ran it. All is back to the way it was before, but of course I still have the issue. Need food – sleep. Maybe a clear head in the morning will help. Very much appreciate your assist – if you have any other ideas (or can show me where I went wrong in entering the code) I’d very much appreciate it.

Reply | Quote

Humblest Apologies, I should have tried it out before offering the idea. I was on the right track though. The following has been tested (Acc 97):

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([Order Time]) Then [Order Time] = Now()
End Sub

Before the record is updated, the Time field is checked for a value. If nothing is there, (A new order), the date/time is written in. If the field has a value, then the record exists and has been edited, so the step is bypassed.

This also assumes that the field in the table is set to Date/Time Data type and that when you look at your form in design mode, Order Time is written in the text box.

I don’t know why your screen crashed. If the above crashes also, maybe there are coding differences between 97 and 2002 (Drool) and I would suggest that you ask for someone with 2002 to check it for you.

Reply | Quote

Well, I’m thinking I just don’t have enough knowledge to deal with this issue, or perhaps even the ability to follow directions. I copied your code to VB and ran it. This is what I got in the ‘Visual Basic Error Message’:

Run-time error ‘2465’:
Can’t find the field “|” referred to in your expression

I went back and looked at the expression and there is no “|” anywhere in it. I’m beginning to feel like Homer Simpson (can’t find the “any” key).

Anyway, I guess I’m back to square one. I do thank you for your help so far and offer my apologies for whatever I’m doing wrong at this point. It must be equally frustrating for you.

Reply | Quote

Sorry to jump in like this, but I know how frustrating this gets.
It looks as though you may have used the wrong key.
If IsNull([Order Time]) Then [Order Time] = Now()

The bolded brackets are the key to the right of the letter P on the keyboard.

If this doesn’t help, I do apologize for butting in!!

Like I said, this can get frustrating, but once you get it working, it’ll be worth it !!

Good Luck!!

Michael

Reply | Quote

Thanks Michael but, yes, I do have the correct brackets entered (although that’s just the type of mistake I’m capable of making). Please also see my recent request concerning VB – appreciate any and all input!

Bryan

Reply | Quote

I think things have gotten off track here – I believe you are adding a new record with the form. If that’s the case, you want to use the Before Insert event to set the field value. In that case you simply set the code with no If statement or anything. We do this regularly with both a time stamp, and the userid of the person who entered the record. We also regularly log who last changed a record with similar code in the Before Update event. There is another alternative to this, and that is to set a default value in the table itself. You can simply set the default value for the field in the table to Now() and all new records will receive that value unless someone actually puts in a different value. And the lock on the form control Alan suggests is appropriate – you might also want to make it not enabled as well so users can’t even tab into it or click into it. Hope this gets things restarted.

Reply | Quote

I just KNEW I should have been dropping breadcrumbs along the way!! I’m totally lost now.

I actually have three forms originating from the same query (calculated fields) which is based on a single table. They are:

1. Order Entry (which displays a single new order – data entry only – with ‘Order Time’ locked)
2. Order Detail (which displays a single order selected from a dispatch screen (form) – editable – with ‘Order Time’ unlocked)
3. Order History (which displays all orders – editable – with ‘Order Time’ unlocked)

I now realize that ‘Order Time’ should probably be locked on all screens if I can get the time entry issue corrected. As I mentioned in one of my earlier posts, I did have the default set to Now() in the Table, but it entered a time at the moment the form was “opened”, rather than the time the operator began to actually enter an order. Geesh, I think I’m getting dizzy!

Now I’m not sure if I’m supposed to be entering conditions or values in the field, or the form. When you say to use the Before Insert event, do you mean in the field or the form? You say to set the code with no If statement or anything. How to I set a code for this event if I don’t make any entry? It’s 8:48 here – too early to start drinking?

Also, if you would be so kind, please see my other post on VB books – any input would be greatly appreciated!

Bryan (I think)

Reply | Quote

Sorry – I should have been a bit more explicit. I was thinking of the behavior of SQL Server, where the time is set at the time the table is saved, so if you need exact time, the answer is to use the Before Insert event of the form, and put a simple line of code in the event procedure that says

me![Timestamp] = Now

where the me![Timestamp] expression refers to the control that displays the field. This will cause the field to be set before each record is saved. If you are using Access security, you can use a similar expression “me![WhoEntered] = CurrentUser()” to capture the identity of the user who created the new record. (If you don’t have security active, it will always say “Admin” as that user is how everyone run Access in that situation.) Hope this clarifies things some – but bear up as it is now time for lunch soon and you could slip out to the local bar.

Reply | Quote

Praise the Lord and pass the tequila – we got a winner!!

This works great! I locked the field in the history and detail views, as well as the new order entry form. Now the order entry form opens without the time, the operator can spend all day staring at the screen if they want, but the time won’t be captured until they have completed the form. As the only other time the form is visible is when they look at the history or detail view (where the cell is locked), the time can never be changed.

I’m going to have to lump you in with the United Way my friend – “I don’t know you, but I love you”. Thank you so much for your help on this and for your advise on the VB self-teach issue. Your selfless response to a desperate amatuer is most worthy of high praise. If ever I should get to the point of actually having some value as a contibutor, I shall not forget my debt.

Gratefully,
Bryan

Reply | Quote

I’m glad you were able to get your problem fixed. I’m sorry if I confused things for you at all. Where would we be without people like Charlotte and Wendell, just to name a few?

“All Hail to the Access Gurus”

Reply | Quote

No worries mate! If you’re ever in the states let me know, I’ve got a cool Fosters for ya!

Seriously, every suggestion is welcomed and a valued learning tool. At this point in my venture down Access Madness Lane I appreciate every effort by those who are willing. No apology wanted, requred, or even appropriate.

Bryan

Reply | Quote