Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Location
    California
    Posts
    15

    Unanswered: Need to properly render a record in a subform

    I have a form that contains a subform (datasheet style).

    In the Subform I have the following fields in the following order.

    StatsDate, Case#, Category, Sub-Cat, Comments, Amount, Hrs, WorkerID.

    The StatsDate field is linked as a Child Field.

    The Case# field is a required field.

    I am having trouble when any user begins typing in a Case# and either leaves the Case# field half written and walks away for about an hour OR when a user starts typing in a Case# then backs it out and leaves the cursor in the Case# field and leaves it that way for extended periods of time. These two types of situations cause the Back End Access DB to lock and when the next user (a different user on a different PC) an hour later tries to add a record they get the error "ComputerName has locked the record". Then I have to go to that specific computer (in the error) and have them enter a proper Case# and have them click some where else to render the record. Below is the vba I have in the subform. Can you help me determine the best vba for my situation, possibly to render the record as a new case number is being entered OR anything else you might have that accomodate my environment? I am not good at all with vba so if you can reply by modifying the vba below would be awesome!

    Your help is greatly appreciated.



    Option Compare Database
    Option Explicit

    Private Sub Case__AfterUpdate()
    Me.Dirty = False
    End Sub

    Private Sub Category_AfterUpdate()
    Me.Sub_Cat = Null
    Me.Sub_Cat.Requery

    End Sub


    I am using Access 2003 SP3

    Iram/mcp
    Iram/mcp

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    This link may help you in implementing a solution: How to detect User Idle Time or Inactivity in Access 2000
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Sep 2010
    Location
    California
    Posts
    15

    Rendering a record

    Thanks for the link. That solution would be useful if I wanted Access to close on the users after a specific amount of time however since the users need to have this app up and running when adding a record we need a different solution.
    The main problem is when some starts typing in a case# then hits the backspace until the case# is left blank, which means that my vba rendering code does not execute because they haven't left the field. How can I change the vba so that the new record is saved upon typing anything into case# field?



    Iram/mp
    Iram/mcp

  4. #4
    Join Date
    Mar 2007
    Posts
    277
    Actually, the link would allow you to wake up and do anything you want, including closing the db if that is what you wanted to do. The change event of a TextBox control fires *every* time the user strikes the keyboard while in the control. That should allow you to modify a global variable that could be watched by the timer event.
    Last edited by RuralGuy; 09-18-10 at 20:45.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A solution, may be a bit radical, would consist in saving the record after every keystroke in one or several fields:
    Code:
    Private Sub Customer_Change()
    
        Me.Dirty = False
        
    End Sub
    Have a nice day!

  6. #6
    Join Date
    Mar 2007
    Posts
    277
    Not very elegant and really a Kludge but probably effective in this case. I tend not to recommend those type of solutions. It is of course the OP's project and therefore their decision as to how to solve the issue.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

Posting Permissions

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