Results 1 to 4 of 4
  1. #1
    acm007 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    12

    Question VBA Search Priority Code

    Hi,

    I'm trying to sort in two ways:
    1st by DateofManufacture
    2nd by LedgerNumber (A field in my database)

    It is a search database, meaning that if I setup the Search Criteria in the Query, it won't work after I hit the search button. So I need to set it up through VBA.


    Here is the Code the code that I currently have:


    strSQL = "SELECT DISTINCTROW N_T_BatchFinalSpecs.* FROM N_T_BatchFinalSpecs " & _
    "WHERE " & strSQL & _
    " Order By DateofManufacture DESC;"
    Me.RecordSource = strSQL
    Me.Requery
    Me.Repaint
    Me.AllowEdits = True
    Exit Sub

    I need it to be displayed first by decreasing DateofManufacture, then by decreasing LedgerNumber.

    Can anyone help me?
    Attached Thumbnails Attached Thumbnails 001.JPG   003.jpg  

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This part
    Code:
    strSQL = "SELECT DISTINCTROW N_T_BatchFinalSpecs.* FROM N_T_BatchFinalSpecs " & _
    "WHERE " & strSQL & _
    Doesn't look right. I see this being interpreted as
    SELECT DISTINCTROW N_T_BatchFinalSpecs.* FROM N_T_BatchFinalSpecs
    WHERE SELECT DISTINCTROW N_T_BatchFinalSpecs.* FROM N_T_BatchFinalSpecs...
    since you're adding the already defined value held by strSql.

    Suggest you break the sql construct into as many parts as there are different:
    strSelPart = "SELECT... WHERE "
    strWherePart "criteria here "
    If condition1 Then strSortPart = "ORDER BY one field"
    Else
    strSortPart = "ORDER BY other field"
    strSql = strSelpart & strWherePart & strSortPart
    Watch that you construct the parts properly, ensuring the concatenation has spaces where they should be.

    BTW, you shouldn't need the repaint.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It looks like you have the query working to select all records ordered by DateofManufacture DESC and LedgerNumber DESC.

    I would use the form filter property to reduce/filter the records displayed.

    You can build the filter "on-the-fly", then set the form filter property. IMO, this is much easier than changing the record source every time you change the record parameters.

    For an article about search forms, see
    "Search criteria" by Allen Browne at http://www.allenbrowne.com/ser-62.html

    The code is at http://www.allenbrowne.com/ser-62code.html

    I use this method all of the time.

  4. #4
    acm007 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    12
    Awesome, Thank you very much for the help guys!

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

Similar Threads

  1. Union - Priority
    By T1969 in forum Access
    Replies: 12
    Last Post: 02-11-2017, 06:45 AM
  2. Replies: 6
    Last Post: 10-12-2016, 07:27 AM
  3. priority oder of commands
    By Jen0dorf in forum Access
    Replies: 3
    Last Post: 01-23-2016, 01:14 PM
  4. Replies: 3
    Last Post: 01-04-2015, 06:09 PM
  5. Using a Priority and updating other records
    By Perceptus in forum Modules
    Replies: 6
    Last Post: 07-08-2014, 11:18 AM

Tags for this Thread

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