Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Unanswered: form recordset not updated before oncurrent event called

    I am having the strangest problem.

    Given:

    A database table with field "str1".
    A form bound to this table.
    A control on the form, named "str1", that is bound to the "str1" field in the table.

    Take the following oncurrent event:

    Private Sub Form_Current()
    MsgBox "str1 control = " & Me.str1
    MsgBox "str1 rs = " & Me.Recordset("str1")
    End Sub

    When the form is first loaded, the oncurrent event is called, and execution occurs as expected, the control str1 and the 'recordset("str1")' have the same values. However, when you navigate to the next record, the two values differ. The str1 control is set to the str1 field of the new record,while 'recordset("str1")' is set to the str1 field of the prior record.

    Is this correct execution? Can anybody explain why 'recordset("str1")' isn't updated with the values of the current record?

    Thanks so much

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    This is a correct sequence of events / updating.

    The Recordset object is not refreshed until after the OnCurrent event.
    It is a clone of the data.

  3. #3
    Join Date
    Oct 2003
    Posts
    8

    dang.....

    Thanks for the response.

    Is there anyway I can programmatically access the new record from the oncurrent event?

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: dang.....

    Originally posted by schmoe
    Thanks for the response.

    Is there anyway I can programmatically access the new record from the oncurrent event?
    Actually, as of Access 2000, there is a Form recordset also that actually is the forms recordset. Changes to one affect the other and vice versa. I used the same code in one of my forms and it brought up the same info for each.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  5. #5
    Join Date
    Oct 2003
    Posts
    8
    "Actually, as of Access 2000, there is a Form recordset also that actually is the forms recordset. Changes to one affect the other and vice versa. I used the same code in one of my forms and it brought up the same info for each."


    This would make sense. You need the ability to set unbound controls based on current recordset values.

    I too am using Access 2000. Unfortunately its not working for me. Did you use form.recordset("fieldname"), or is there a better way? Did you click next record a few times on the form?

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    basicmek - what was your exact code?


    schmoe -

    why not just do this

    unbound_control = Me.str1

  7. #7
    Join Date
    Oct 2003
    Posts
    8

    What I was really trying to do...

    I don't like how Access automatically updates the database when you enter values onto bound form controls. I'd much rather you enter in all your updates and click a "Save" button.

    So I have this semi bound form, where the form is bound but the controls are not. The unbound controls are to be set in the oncurrent event from the form's recordset. To do as you suggest - set unbound controls based on bound controls - would require each field on the form to have two controls - one invisible bound control and one visible unbound control. I'd rather not have to do that.

    The best work around I can think of is to disable Access's navigation buttons and add my own navigation buttons. Any better suggestions?

  8. #8
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Sorry - try Me.str_1 = Me!str1
    with Me.str_1 being unbound and [str1] being a field in the recordset.


    You'll also have to trap for the form being on a new record.
    Last edited by Rockey; 02-22-04 at 20:39.

  9. #9
    Join Date
    Oct 2003
    Posts
    8

    YEAAAAAAAAAA!

    "Sorry - try Me.str_1 = Me!str1
    with Me.str_1 being unbound and [str1] being a field in the recordset.

    You'll also have to trap for the form being on a new record."


    *Alarms sounding*
    *Spotlights flying*
    *Sound of coins rushing out of slot machines*

    Winner winner winner!!!

    Thanks soooo much!!!!

    Access is one strange beast.....

  10. #10
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by Rockey
    basicmek - what was your exact code?


    schmoe -

    why not just do this

    unbound_control = Me.str1
    Rockey:

    I simply used the same code as was posted but with a field on my form.

    I used a message box to display each of the values in the On Current event.

    MsgBox Me.Field
    MsgBox Me.Recordset("Field")

    Navigated fine and values stayed the same.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  11. #11
    Join Date
    Oct 2003
    Posts
    8

    just curious

    basicmek,

    Did you click 'next' several times? It works well on the first load. Its after you click 'next' when the problem reveals itself.
    What version of Access are you using. I'm using Access 2000.

    Thanks for effort...

  12. #12
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Thumbs up Re: just curious

    Originally posted by schmoe
    basicmek,

    Did you click 'next' several times? It works well on the first load. Its after you click 'next' when the problem reveals itself.
    What version of Access are you using. I'm using Access 2000.

    Thanks for effort...
    Yes I clicked quite a number of times actually. I have just migrated to Access 2003 at home but am still using XP at work. When I was using 2000, I mostly continued to use RecordsetClone because I wasn't aware of the change. Could have been an early version bug but the books that I have didn't mention any issues with the earlier versions.

    Looks like you resolved your problems though. That's the important thing.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  13. #13
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    I'm using Access 2000 also. Maybe a bug??

  14. #14
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by Rockey
    I'm using Access 2000 also. Maybe a bug??
    Wish I still had 2000 loaded. Kind of feels like comparing apples to oranges when questions like this come up. You might try MSDN to see if there is anything there.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  15. #15
    Join Date
    Mar 2008
    Posts
    1

    Beware you don't have unbound txt boxes called the same as fieldnames

    Something to watch out for in all of this is that you don't have your text box controls having the same names as your field names if they are unbound.

    No problem if you are using bound fields and the conventional access navigation controls.

    But if you are using the OnCurrent event in Access 2000, make sure your text box controls have different names than the fields themselves... otherwise that will muddy the waters and make what's going on even harder to understand.

    Once I had this sorted out, I found that
    Me!txtADDRESS_1.Value = Me.ADDRESS_1
    works fine in the OnCurrent event,

    whereas:
    Me!txtADDRESS_1.Value = Me.Recordset.Fields("ADDRESS_1").Value
    gives the previously visited record value

    in (MS Access 2000 SR1)

Posting Permissions

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