Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    Quimper, France
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blocked within current record when updating record (MS Access 97)

    Hi all,

    I'm creating a form and wanted to have a calculated value in a numeric field, depending from the values selected in each of 2 lists of values.
    Form field value is updated when each of the combos has an Out of focus event. Value calculated is all right except I then can't quit my current record and go to another one!
    My form shows several records and the following actions have no effect:
    - clicking on another record's field in the form
    - clicking on the navigation bar's buttons

    Things were still normal when I had not begun entering these event-based functions or while I was trying to find the correct syntax for calling functions (another field gets the system date when created and/or updated)

    I removed the update procedure from the record update's form event to insert it at each of the lists' loose focus events. The calculation is still all right but I'm still stuck.

    Something must block somewhere. When I try to close the form, I have the following error message (sorry, translated from French, no help found in "online help"):
    "Microsoft Access possibly met an error when trying to save a record.
    "If you close this object now, the updates will be lost.
    "Do you still want to close the database object?

    The calculated value is stored in a non-user-editable text field of the form, which is linked to a numeric field of the table.

    Any help would be greatly appreciated,

    Henri Chorand
    Kemper DOC

  2. #2
    New Lounger
    Join Date
    Apr 2001
    Location
    Quimper, France
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blocked within current record when updating record (MS Access 97)

    Beginning of solution:
    I'm not stuck when I comment out the following:
    Private Sub Form_AfterUpdate()
    ' [MaJ] = Date
    End Sub
    So the problem lies with the bad syntax of the commented line OR the use of the Form_AfterUpdate() sub.
    My idea is only to have the system date written into a field when the record is updated.
    Probably this second post has an easier answer (so I hope) !

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Blocked within current record when updating record (MS Access 97)

    You can't set a control value in the form's afterupdate event without sending the form into a continuous loop. You can't move to another record because it keeps triggering the afterupdate event, which really belongs to the record and not the form.

    If the calculation is done on the comboboxes' LostFocus events, then why are you doing it again in the Form's AfterUpdate event? It shouldn't be necessary, and in fact, you would be better off using the comboboxes' AfterUpdate events so that the recalculation is done only when a value is changed. LostFocus happens even if the value is not changed, and it doesn't happen in the order you expect when you click on a commandbutton.
    Charlotte

  4. #4
    New Lounger
    Join Date
    Apr 2001
    Location
    Quimper, France
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blocked within current record when updating record (MS Access 97)

    Charlotte,
    I had a couple of very busy days before being able to answer you.
    Thank you for your answer. I definitely had missed something.
    I have followed your answer and it's OK now.
    My last question about this database is:
    I wanted to keep a record of database records' update, so I added a Date type field (only the date, not the tme) which I would like to automatically update with the system date when the record is updated.
    I put system date as default value in the form, so each new record gets the current date when created, which is fine.
    But I can't make Access update this date field whenever I edit the current record.
    I put the following line in the form's Before Update sub, but it does not work (the date field's value is NOT updated when I edit a record field, even if other (edited) fields are updated all right):
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    [MaJ] = Date
    End Sub

    Maybe it comes from the fact that the form's date field is non-editable by the user (which I thought was a good thing.)
    Could you please tell me what I missed? The user should not have to edit the date manually.
    Thanks in advance,

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Blocked within current record when updating record (MS Access 97)

    Do you mean the field is locked on the form or that it is not updatable? If it's merely locked, your code should work. If you set the control up with a formula as its control source, you can't change the value even from code.
    Charlotte

  6. #6
    New Lounger
    Join Date
    Apr 2001
    Location
    Quimper, France
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blocked within current record when updating record (MS Access 97)

    The control's properties are as follows:
    Enabled= Yes
    Locked= Yes
    Default value= Date()
    Control source= MaJ (name of date field in table structure, it's also the control's name)

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Blocked within current record when updating record (MS Access 97)

    Have you tried changing the controlname to something like txtMAJ?
    Charlotte

Posting Permissions

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