Results 1 to 7 of 7
  1. #1
    Miles R is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Mar 2019
    Posts
    161

    No current record after requery

    I have a problem on a form when I issue the Me.Requery command on a button click event on the form.

    Even though I then use DoCmd.GoToRecord , , acFirst
    the first time it tries to access the record e.g. Me.Recordset!CoinType
    it gives an error saying there is no current record (there is one record as shown by Me.Recordset.RecordCount)



    Note that this only happens when there is only one item in the record set (both before and after the requery). With multiple items it seems to work without a problem.

    Is the Requery confusing the database somehow?

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Is the record unsaved and still effectively "New" at the point of the error?
    You wouldn't normally refer to the forms recordset to access a field value, just either use the form control for that field (if it has one) or directly refer to the underlying field name if it isn't on the form?

    Also be wary of Me.Recordset.RecordCount, it may not return the correct value in some circumstances.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Miles R is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Mar 2019
    Posts
    161
    Actually, no data in the recordset is being changed, just queried. I am referring to the forms recordset from VBA. There is no form control for the fields in question.
    Can't see why rhe RecordCount should be wrong in this case, but will investigate.

  4. #4
    Miles R is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Mar 2019
    Posts
    161
    Further to the original post. These are lines that cause problems on a Single Form.
    Me.Requery
    DoCmd.GoToRecord , "", acLast
    MsgBox Me.Recordset.RecordCount ---- this gives a value of 1
    MsgBox Me.Recordset!CoinType ---- this gives error 3021, no current record

    Can't see that this is anything other than a bug.
    I have got round this now by removing the RecordSet from the form and using an SQL Query to load the data in the Form Load event. No problem when rerunning the query with a different filter.

    Curious that no-one has seen this error in these circumstances.

  5. #5
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What happens if you

    Debug.Print Me.CurrentRecord, Me.CoinType

    which should return the current forms record number and the underlying value from the form recordsource?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    Miles R is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Mar 2019
    Posts
    161
    Thanks for your reply Minty,
    When I do this, it gives the result 1, Halfcrown
    This is correct, but still gives the error 3021 when it gets to the Me.Recordset!CoinType line

    I have always been in the habit of using Me.Recordset!<field name> rather than Me.<field name>. Had not really thought about the latter and probably assumed at some time that they were equivalent.
    Last edited by Miles R; 03-09-2023 at 07:37 AM. Reason: Additional info

  7. #7
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I only thought it odd as I have never directly referenced the forms recordset like that, as it is available with the default form object.
    On the other hand, I have frequently used the RecordsetClone object.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-18-2018, 10:23 AM
  2. Remain on current record after requery
    By siwilsonchan in forum Access
    Replies: 1
    Last Post: 05-15-2018, 03:35 PM
  3. Replies: 11
    Last Post: 04-09-2016, 08:54 PM
  4. Requery Current Record ONLY
    By jle0003 in forum Queries
    Replies: 1
    Last Post: 10-17-2012, 12:45 PM
  5. Requery only one record on a form
    By mkallover in forum Forms
    Replies: 3
    Last Post: 01-26-2011, 09:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums