Results 1 to 9 of 9
  1. #1
    SwampDude is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    5

    Database converted (97 to 2003 to 2013): ReQuery does not work unless OrderBy is performed first.

    WINDOWS version: 7 Pro 64bit


    ACCESS version: 365 (I didn't see this as an option when picking the Access version)


    The "frontend database" (interface) is stored in a folder on the C: drive. The linked "backend database" (data) is in a folder on a server. All functions are on one table in the backend. No relationships.


    In the frontend there's a form with 3 fields for search criteria with a "SEARCH" button that triggers an Requery in the form's source query. There are also 3 buttons that sort the data and search results by 3 different fields using OrderBy. All buttons have a Beep command that occurs after results are complete.


    Everything was working in Access 97. After converting the both the frontend and backend MDBs to ACCDBs, the issue started. (97 to 2003 to 2013)


    If I type in the search criteria field(s) and click the SEARCH button, nothing happens except the Beep. But if I click any of the 3 sorting buttons first, I can then click the Search button and it works. Closing and opening the search form doesn't make any difference.


    Any ideas will be greatly appreciated.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.

    Any chance you would post the FE and BE for analysis?
    Make copies of the FE/BE, remove all but maybe 10 - 20 records, change any sensitive data, do a Compact and Repair, then compress (Zip) before attaching.

  3. #3
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, you might have a Reference problem and maybe some outdated code but the Reference problem will cause strange behavior. Have a look at...
    https://www.access-diva.com/d5.html (see bottom of page)

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    It could simply be code that is now not being recognised as "correct" syntax.

    Access VBA has become more picky through the versions and certain things that would work in an old version now won't.
    What is the code behind the search / filtering commands?
    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 ↓↓

  5. #5
    SwampDude is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    5
    Quote Originally Posted by ssanfu View Post
    Any chance you would post the FE and BE for analysis?
    Make copies of the FE/BE, remove all but maybe 10 - 20 records, change any sensitive data, do a Compact and Repair, then compress (Zip) before attaching.
    Thanks for your reply. Since it's being used by a law firm, EVERYTHING is sensitive. It'll take some time to change the records. If I don't find a solution, I'll do that.

  6. #6
    SwampDude is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    5
    Quote Originally Posted by GinaWhipp View Post
    Hmm, you might have a Reference problem and maybe some outdated code but the Reference problem will cause strange behavior. Have a look at...
    https://www.access-diva.com/d5.html (see bottom of page)
    Thanks. As per your link, I disabled "Microsoft Visual Basic for Applications Extensibility [vbe6.dll] 5.3" (the only one I found in the disable list) and compiled. Didn't make a difference.

  7. #7
    SwampDude is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    5
    Quote Originally Posted by Minty View Post
    It could simply be code that is now not being recognised as "correct" syntax.

    Access VBA has become more picky through the versions and certain things that would work in an old version now won't.
    What is the code behind the search / filtering commands?
    Here's all the code behind the form relative to the form loading, search and sorting. The Search button triggers cmdSearch. The 3 sort buttons trigger cmdSortByFileNo , cmdSortByClient , cmdSortByCaseDate

    ======== CODE BEGINS ==========
    Option Compare Database
    Option Explicit

    Private Sub Form_Load()
    ' Clear any sorting that may have been set by user
    Forms![frmSearch]![sfrmSearch].Form.OrderBy = ""
    Me!lblSortOrder.Caption = "(Currently sorted by File#)"
    End Sub

    Private Sub cmdSortByFileNo_Click()
    On Error GoTo Err_cmdSortByFileNo_Click

    Call ChangeSort("FileNo DESC,FileNoExt DESC, CaseDate DESC", "File#")

    Exit_cmdSortByFileNo_Click:
    Exit Sub

    Err_cmdSortByFileNo_Click:
    MsgBox Err.Description
    Resume Exit_cmdSortByFileNo_Click

    End Sub


    Private Sub cmdSortByClient_Click()
    On Error GoTo Err_cmdSortByClient_Click

    Call ChangeSort("Client,FileNumber DESC,CaseDate DESC", "Client")

    Exit_cmdSortByClient_Click:
    Exit Sub

    Err_cmdSortByClient_Click:
    MsgBox Err.Description
    Resume Exit_cmdSortByClient_Click

    End Sub

    Private Sub cmdSortByCaseDate_Click()
    On Error GoTo Err_cmdSortByCaseDate_Click

    Call ChangeSort("CaseDate DESC,FileNumber DESC,Client", "Case Date")

    Exit_cmdSortByCaseDate_Click:
    Exit Sub

    Err_cmdSortByCaseDate_Click:
    MsgBox Err.Description
    Resume Exit_cmdSortByCaseDate_Click

    End Sub

    Private Sub cmdSearch_Click()
    On Error GoTo Err_cmdSearch_Click

    Me![sfrmSearch].Form.Requery
    Beep

    Exit_cmdSearch_Click:
    Exit Sub

    Err_cmdSearch_Click:
    MsgBox Err.Description
    Resume Exit_cmdSearch_Click

    End Sub


    ' ==== SUBROUTINES ====

    Private Sub ChangeSort(strSortFields As String, strCaption As String)
    Forms![frmSearch]![sfrmSearch].Form.OrderBy = strSortFields
    Forms![frmSearch]![sfrmSearch].Form.OrderByOn = True
    Beep
    Me!lblSortOrder.Caption = "(Currently sorted by " & strCaption & ")"
    End Sub
    ======== CODE ENDS ==========

  8. #8
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    In the change sort routine you refer to the subform using this syntax
    Forms![frmSearch]![sfrmSearch].Form.OrderBy

    but in the search button you are using
    Me![sfrmSearch].Form.Requery

    Wondering if that makes any difference?
    I am guessing that the underlying query has reference to the search control in it otherwise I can't see how it would work?


    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 ↓↓

  9. #9
    SwampDude is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    5
    Quote Originally Posted by Minty View Post
    In the change sort routine you refer to the subform using this syntax
    Forms![frmSearch]![sfrmSearch].Form.OrderBy

    but in the search button you are using
    Me![sfrmSearch].Form.Requery

    Wondering if that makes any difference?
    I am guessing that the underlying query has reference to the search control in it otherwise I can't see how it would work?
    Your thought inspired me to try something. When I disabled Forms![frmSearch]![sfrmSearch].Form.OrderBy = "" under
    Private Sub Form_Load()
    the SEARCH button worked immediately. Strange. Access 97 had no problem with it. I think I may have a workaround, but it would be nice to know why the newer Access reponds differently. Thanks for the reply!

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

Similar Threads

  1. Requery Subform doesn't work
    By cokiere in forum Access
    Replies: 8
    Last Post: 04-11-2019, 03:15 AM
  2. How did this requery work?
    By Access_Novice in forum Programming
    Replies: 1
    Last Post: 01-04-2014, 07:30 PM
  3. Replies: 12
    Last Post: 07-25-2013, 12:49 PM
  4. Replies: 4
    Last Post: 12-31-2012, 01:10 PM
  5. Access database will not work in .Net 2003
    By Brilene in forum Access
    Replies: 0
    Last Post: 03-18-2011, 03:41 PM

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