Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    9

    Unanswered: Addnew challenge - new record doesn't sort properly any more

    Using Access 2002 in Access 2000 format

    I have written some code that copies a record (using dlookup) and plunks the fields into a new record using addnew. In the process, one of the primary key fields (I am using 5 different primary key fields to achieve record uniqueness) is incremented by one to ensure uniqueness. The code is executed when I click a button in one of my forms. I do close off the recordset and use Me. Requery.

    Using addnew, the new record used to show up in its proper sorted location when scrolling through the records in the form. Recently during populating the records, created new records end up at the end of the rest of the records! If I look at the table in datasheet view, the new record is sorted correctly. If I go back to the form, the record is at the end.

    The only way to get the new record to show in its proper sorted order is by getting out of Access 2002 and getting back in. I also have noted that if I have at least 7 new records created, the 8th and following new records ARE in the proper sorted order.

    On rare occassions I have noted that the new record (e.g., #1.i.1.1.1 shows up just after #12.i.0.0.0).

    Does anyone have some ideas? Any assistance would be most appreciated. Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133
    Is the form based on the table or on a query?
    SteveH

  3. #3
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    It may be due to the sorting method access uses,
    1
    10
    2
    3
    4
    45
    456
    46

    It sorts by the first character, then the second, etc. Due to the fact that you are using a text field.

  4. #4
    Join Date
    Mar 2004
    Posts
    9
    Originally posted by SteveH
    Is the form based on the table or on a query?
    The form is based on two tables.

  5. #5
    Join Date
    Mar 2004
    Posts
    9
    Originally posted by Saila
    It may be due to the sorting method access uses,
    1
    10
    2
    3
    4
    45
    456
    46

    It sorts by the first character, then the second, etc. Due to the fact that you are using a text field.
    The first field I use is an integer. The VBA code for dlookup is configured for integer and the variable I use to initially plunk the contents of that filed is an integer. I do have some other fields that are text but they do not factor into this problem. Yeah the 1 .. 10 can be a pain but I preface the 10 with an alpha to get it to sort.

    The records are in the proper order when viewed on the form when I get out of Access and then restart Access.

  6. #6
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    have you tried using refresh as well as or in place of requery? it shouldn't make a difference, but microsoft products do sometimes have these glitches.

  7. #7
    Join Date
    Mar 2004
    Posts
    9
    Originally posted by Saila
    have you tried using refresh as well as or in place of requery? it shouldn't make a difference, but microsoft products do sometimes have these glitches.
    Tried both ways:

    - with Me.Refresh added after closing recordset and Me.Requery = no change

    - if Me.Refresh is only used, you can't see the change unless you get out of the form. The documentation indicates that refresh does not affect new entries (e.g. the one from the addnew)

    Thanks for the info. I continue to flip thorugh "Beginning Access 2002 VBA" for some indication of my potential programming error. I did have to define the recordset as a DAO.Recordset to get the dlookup to work.

  8. #8
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    I have no idea what the problem is, if you close the form and then go back in, is it sorted correctly? or only when you quit access?

  9. #9
    Join Date
    Mar 2004
    Posts
    9
    Originally posted by Saila
    I have no idea what the problem is, if you close the form and then go back in, is it sorted correctly? or only when you quit access?
    No...the sort stays the same. The only way to get it to sort correctly is to get out of Access 2002 and get back in.

    I am currently thinking that the problem may be due to the relationships among the tables. I will do some more thinking and poking around.

  10. #10
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    The relationships shouldn't affect sort order, but I may be wrong

Posting Permissions

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