Results 1 to 15 of 15
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    'Lock-on-exit' text box? (2002)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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
    "Heading for the deep end"

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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)
    "Heading for the deep end"

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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.
    "Heading for the deep end"

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: 'Lock-on-exit' text box? (2002)

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

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: 'Lock-on-exit' text box? (2002)

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

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  14. #14
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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" <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/clever.gif border=0 alt=clever width=15 height=15> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>
    "Heading for the deep end"

  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: 'Lock-on-exit' text box? (2002)

    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
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •