Sort listbox (1 Viewer)

Z

zebrafoot

Member
Local time
Today, 19:51
Joined
May 15, 2020
Messages
65
Hello everyone.

Hoping this wasn't covered elsewhere.

I have a listbox in a database form, which shows all products in the database according to various search criteria. Currently, the rowsource is based on an underlying query, ordered by several different things (Supplier name, Product ID, etc etc). I want to be able to click buttons to re-sort differently. If I understand correctly, there isn't a simple listbox function that can do this and I should do it changing the datasource by SQL.

However, although the query with the "fixed" sort order is working fine, I can't work out how to convert it to a string that works in vba as it is very long:

SELECT tblProducts.ProductStatusID, tblManufacturer.ManufacturerName, tblSuppliers.SupplierName, tblProducts.ProductID, tblProducts.ProductTitle, tblProductStatus.ProductStatusCategory, tblProducts.DealerDiscount, tblProducts.ProductDateAdded, tblProducts.ProductLastModified, tblProducts.SupplierPartNumber, tblProducts.ProductPrice, tblSupplierCurrency.SupplierCurrency, Nz([CountofInventoryQuantity],0) AS NZQuantity, tblProducts.ProductDetailDescription, tblProducts.ProductSummaryDescription, tblProducts.ProductVatable, tblProducts.ProductNotes, tblProducts.ProductInventoryRequiresSerialNumber, tblProducts.ProductIsVirtualItem, tblProducts.ProductModifiedBy, tblProducts.ProductsSupplierQuoteNumber, tblSupplierCurrency.SupplierCurrencyID, tblProducts.ProductPriceIfNotSterling, tblProducts.ProductSupplierLocalPrice, tblProducts.ProductDeleted, qryInventoryCountForProductSearch.CountOfInventoryQuantity FROM (tblSupplierCurrency INNER JOIN tblSuppliers ON tblSupplierCurrency.SupplierCurrencyID = tblSuppliers.SupplierCurrencyID) INNER JOIN (tblProductStatus INNER JOIN (tblManufacturer RIGHT JOIN (tblProducts LEFT JOIN qryInventoryCountForProductSearch ON tblProducts.ProductID = qryInventoryCountForProductSearch.ProductID) ON tblManufacturer.ManufacturerID = tblProducts.ManufacturerID) ON tblProductStatus.ProductStatusID = tblProducts.ProductStatusID) ON tblSuppliers.SupplierID = tblProducts.SupplierID WHERE (((tblProducts.ProductStatusID) Like "*" & [Forms]![frmProductSearch].[Form]![txtProductStatus] & "*") AND ((tblManufacturer.ManufacturerName) Like "*" & [Forms]![frmProductSearch].[Form]![txtManufacturer] & "*") AND ((tblSuppliers.SupplierName) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplier] & "*") AND ((tblProducts.ProductID) Like "*" & [forms]![frmProductSearch].[Form]![txtProductID] & "*") AND ((tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch1] & "*" And (tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch2] & "*") AND ((tblProducts.ProductDateAdded) Between (Nz(([Forms]![frmProductSearch].[Form]![txtDate1]),1/1/1901)) And (Nz([Forms]![frmProductSearch].[Form]![txtDate2],#1/1/3000#))) AND ((tblProducts.SupplierPartNumber) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplierPartNumber] & "*") AND ((tblProducts.ProductDeleted)=False) AND ((tblProducts.SupplierID) Like "*" & [Forms]![frmProductSearch].[Form]![cboSupplierID] & "*")) OR (((tblProducts.ProductStatusID) Like "*" & [Forms]![frmProductSearch].[Form]![txtProductStatus] & "*") AND ((tblSuppliers.SupplierName) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplier] & "*") AND ((tblProducts.ProductID) Like "*" & [forms]![frmProductSearch].[Form]![txtProductID] & "*") AND ((tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch1] & "*" And (tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch2] & "*") AND ((tblProducts.ProductDateAdded) Between (Nz(([Forms]![frmProductSearch].[Form]![txtDate1]),1/1/1901)) And (Nz([Forms]![frmProductSearch].[Form]![txtDate2],#1/1/3000#))) AND ((tblProducts.SupplierPartNumber) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplierPartNumber] & "*") AND ((tblProducts.ProductDeleted)=False) AND ((tblProducts.SupplierID) Like "*" & [Forms]![frmProductSearch].[Form]![cboSupplierID] & "*") AND (([Forms]![frmProductSearch].[Form]![txtManufacturer]) Is Null)) ORDER BY tblProducts.ProductStatusID, tblSuppliers.SupplierName, tblProducts.ProductID;


I'm trying to specify the row source thus:

me.lstSearch.rowsource = mySQL

When the appropriate button is clicked, I want to use modified SQL with different ORDER BY.

However, due to the size of the code, I can't add it to a single line of vba. I've tried to do it incrementally (mySQL = "first bit", mySQL = mySQL & "second bit"). In this case, I'm getting a type mismatch error (although the same query worked fine).

Any pointers about how to approach this will be gratefully received.

Pete
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 11:51
Joined
Oct 29, 2018
Messages
21,491
Another approach is to use a QueryDef object, so you can modify the query's ORDER BY directly.
 
Z

zebrafoot

Member
Local time
Today, 19:51
Joined
May 15, 2020
Messages
65
theDBguy said:
Another approach is to use a QueryDef object, so you can modify the query's ORDER BY directly.
Click to expand...
I'm not familiar with this approach. Could you expand, please?
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:51
Joined
May 21, 2018
Messages
8,554
A quick solution would be instead of a listbox make a continuous subform that looks and acts like a listbox. Then you could simply set the Order By property of the subform.

Another quick solution is to sort the recordset. This may have some limitations, but would look like this.

Code: 
Dim rs As DAO.Recordset
  Dim rsNew As DAO.Recordset
  Set rs = Me.LstOne.Recordset
  rs.Sort = "companyName DESC"
 
  Set rsNew = rs.OpenRecordset()
  Set Me.List0ne.Recordset = rsNew
 
Z

zebrafoot

Member
Local time
Today, 19:51
Joined
May 15, 2020
Messages
65
MajP said:
A quick solution would be instead of a listbox make a continuous subform that looks and acts like a listbox. Then you could simply set the Order By property of the subform.

Another quick solution is to sort the recordset. This may have some limitations, but would look like this.

Code: 
Dim rs As DAO.Recordset
  Dim rsNew As DAO.Recordset
  Set rs = Me.LstOne.Recordset
  rs.Sort = "companyName DESC"

  Set rsNew = rs.OpenRecordset()
  Set Me.List0ne.Recordset = rsNew
Click to expand...
Thank you very much for this. It works for me, and it's also nice and simple to follow.
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 19:51
Joined
Jul 21, 2014
Messages
2,288
That's a lot of fields to use as source for a Listbox! Is it even readable on the screen?

The reason for your VBA syntax errors is because you (or rather Access) uses double quotes for string delimiters in all your WHERE clauses. You need to double them all up, or just use single quotes instead.

Here is your query in a more readable form:
SQL: 
SELECT
  tblProducts.ProductStatusID,
  tblManufacturer.ManufacturerName,
  tblSuppliers.SupplierName,
  tblProducts.ProductID,
  tblProducts.ProductTitle,
  tblProductStatus.ProductStatusCategory,
  tblProducts.DealerDiscount,
  tblProducts.ProductDateAdded,
  tblProducts.ProductLastModified,
  tblProducts.SupplierPartNumber,
  tblProducts.ProductPrice,
  tblSupplierCurrency.SupplierCurrency,
  Nz([CountofInventoryQuantity],0) AS NZQuantity,
  tblProducts.ProductDetailDescription,
  tblProducts.ProductSummaryDescription,
  tblProducts.ProductVatable,
  tblProducts.ProductNotes,
  tblProducts.ProductInventoryRequiresSerialNumber,
  tblProducts.ProductIsVirtualItem,
  tblProducts.ProductModifiedBy,
  tblProducts.ProductsSupplierQuoteNumber,
  tblSupplierCurrency.SupplierCurrencyID,
  tblProducts.ProductPriceIfNotSterling,
  tblProducts.ProductSupplierLocalPrice,
  tblProducts.ProductDeleted,
  qryInventoryCountForProductSearch.CountOfInventoryQuantity
FROM (
  tblSupplierCurrency
  INNER JOIN tblSuppliers
          ON tblSupplierCurrency.SupplierCurrencyID = tblSuppliers.SupplierCurrencyID
)
INNER JOIN (
  tblProductStatus
  INNER JOIN (
    tblManufacturer
    RIGHT JOIN (
      tblProducts
      LEFT JOIN qryInventoryCountForProductSearch
             ON tblProducts.ProductID = qryInventoryCountForProductSearch.ProductID
    )
            ON tblManufacturer.ManufacturerID = tblProducts.ManufacturerID
  )
          ON tblProductStatus.ProductStatusID = tblProducts.ProductStatusID
)
        ON tblSuppliers.SupplierID = tblProducts.SupplierID
WHERE (
  (
    (tblProducts.ProductStatusID) Like "*" & [Forms]![frmProductSearch].[Form]![txtProductStatus] & "*"
  )
  AND
  (
    (tblManufacturer.ManufacturerName) Like "*" & [Forms]![frmProductSearch].[Form]![txtManufacturer] & "*"
  )
  AND
  (
    (tblSuppliers.SupplierName) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplier] & "*"
  )
  AND
  (
    (tblProducts.ProductID) Like "*" & [forms]![frmProductSearch].[Form]![txtProductID] & "*"
  )
  AND
  (
    (tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch1] & "*"
    And
    (tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch2] & "*"
  )
  AND
  (
    (tblProducts.ProductDateAdded) Between (Nz(([Forms]![frmProductSearch].[Form]![txtDate1]),1/1/1901)) And (Nz([Forms]![frmProductSearch].[Form]![txtDate2],#1/1/3000#))
  )
  AND
  (
    (tblProducts.SupplierPartNumber) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplierPartNumber] & "*"
  )
  AND
  (
    (tblProducts.ProductDeleted)=False
  )
  AND
  (
    (tblProducts.SupplierID) Like "*" & [Forms]![frmProductSearch].[Form]![cboSupplierID] & "*"
  )
)
   OR
(
  (
    (tblProducts.ProductStatusID) Like "*" & [Forms]![frmProductSearch].[Form]![txtProductStatus] & "*"
  )
  AND
  (
    (tblSuppliers.SupplierName) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplier] & "*"
  )
  AND
  (
    (tblProducts.ProductID) Like "*" & [forms]![frmProductSearch].[Form]![txtProductID] & "*"
  )
  AND
  (
    (tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch1] & "*"
    And
    (tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch2] & "*"
  )
  AND
  (
    (tblProducts.ProductDateAdded) Between (Nz(([Forms]![frmProductSearch].[Form]![txtDate1]),1/1/1901)) And (Nz([Forms]![frmProductSearch].[Form]![txtDate2],#1/1/3000#))
  )
  AND
  (
    (tblProducts.SupplierPartNumber) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplierPartNumber] & "*"
  )
  AND
  (
    (tblProducts.ProductDeleted)=False
  )
  AND
  (
    (tblProducts.SupplierID) Like "*" & [Forms]![frmProductSearch].[Form]![cboSupplierID] & "*"
  )
  AND
  (
    ([Forms]![frmProductSearch].[Form]![txtManufacturer]) Is Null
  )
)
ORDER BY
  tblProducts.ProductStatusID,
  tblSuppliers.SupplierName,
  tblProducts.ProductID
;

Here is the same query in a more easy to understand form:
SQL: 
SELECT
  p.ProductStatusID,
  m.ManufacturerName,
  s.SupplierName,
  p.ProductID,
  p.ProductTitle,
  ps.ProductStatusCategory,
  p.DealerDiscount,
  p.ProductDateAdded,
  p.ProductLastModified,
  p.SupplierPartNumber,
  p.ProductPrice,
  sc.SupplierCurrency,
  Nz(ic.CountofInventoryQuantity,0) AS NZQuantity,
  p.ProductDetailDescription,
  p.ProductSummaryDescription,
  p.ProductVatable,
  p.ProductNotes,
  p.ProductInventoryRequiresSerialNumber,
  p.ProductIsVirtualItem,
  p.ProductModifiedBy,
  p.ProductsSupplierQuoteNumber,
  sc.SupplierCurrencyID,
  p.ProductPriceIfNotSterling,
  p.ProductSupplierLocalPrice,
  p.ProductDeleted,
  ic.CountOfInventoryQuantity
FROM (
  tblSupplierCurrency sc
  INNER JOIN tblSuppliers s
          ON sc.SupplierCurrencyID = s.SupplierCurrencyID
)
INNER JOIN (
  tblProductStatus ps
  INNER JOIN (
    tblManufacturer m
    RIGHT JOIN (
      tblProducts p
      LEFT JOIN qryInventoryCountForProductSearch ic
             ON p.ProductID = ic.ProductID
    )
            ON m.ManufacturerID = p.ManufacturerID
  )
          ON ps.ProductStatusID = p.ProductStatusID
)
        ON s.SupplierID = p.SupplierID
WHERE p.ProductStatusID Like '*' & [Forms]![frmProductSearch].[Form]![txtProductStatus] & '*'
  AND (
    m.ManufacturerName Like '*' & [Forms]![frmProductSearch].[Form]![txtManufacturer] & '*'
    OR
    [Forms]![frmProductSearch].[Form]![txtManufacturer] IS NULL
  )
  AND s.SupplierName Like '*' & [Forms]![frmProductSearch].[Form]![txtSupplier] & '*'
  AND p.ProductID Like '*' & [forms]![frmProductSearch].[Form]![txtProductID] & '*'
  AND p.ProductTitle Like '*' & [Forms]![frmProductSearch].[Form]![txtTitleSearch1] & '*'
  AND p.ProductTitle Like '*' & [Forms]![frmProductSearch].[Form]![txtTitleSearch2] & '*'
  AND p.ProductDateAdded Between (Nz(([Forms]![frmProductSearch].[Form]![txtDate1]),1/1/1901)) And (Nz([Forms]![frmProductSearch].[Form]![txtDate2],#1/1/3000#))
  AND p.SupplierPartNumber Like '*' & [Forms]![frmProductSearch].[Form]![txtSupplierPartNumber] & '*'
  AND p.ProductDeleted=False
  AND p.SupplierID Like '*' & [Forms]![frmProductSearch].[Form]![cboSupplierID] & '*'
ORDER BY
  p.ProductStatusID,
  s.SupplierName,
  p.ProductID
;

Your easiest path here would be to save the query without the ORDER BY clause.

Then use VBA to add an ORDER BY clause to the listbox RowSource, eg:
Code: 
Me.MyListbox.RowSource = "SELECT * FROM WhateverYouSavedTheQueryAs ORDER BY SupplierName DESC;""
 
Z

zebrafoot

Member
Local time
Today, 19:51
Joined
May 15, 2020
Messages
65
cheekybuddha said:
That's a lot of fields to use as source for a Listbox! Is it even readable on the screen?

The reason for your VBA syntax errors is because you (or rather Access) uses double quotes for string delimiters in all your WHERE clauses. You need to double them all up, or just use single quotes instead.

Here is your query in a more readable form:
SQL: 
SELECT
  tblProducts.ProductStatusID,
  tblManufacturer.ManufacturerName,
  tblSuppliers.SupplierName,
  tblProducts.ProductID,
  tblProducts.ProductTitle,
  tblProductStatus.ProductStatusCategory,
  tblProducts.DealerDiscount,
  tblProducts.ProductDateAdded,
  tblProducts.ProductLastModified,
  tblProducts.SupplierPartNumber,
  tblProducts.ProductPrice,
  tblSupplierCurrency.SupplierCurrency,
  Nz([CountofInventoryQuantity],0) AS NZQuantity,
  tblProducts.ProductDetailDescription,
  tblProducts.ProductSummaryDescription,
  tblProducts.ProductVatable,
  tblProducts.ProductNotes,
  tblProducts.ProductInventoryRequiresSerialNumber,
  tblProducts.ProductIsVirtualItem,
  tblProducts.ProductModifiedBy,
  tblProducts.ProductsSupplierQuoteNumber,
  tblSupplierCurrency.SupplierCurrencyID,
  tblProducts.ProductPriceIfNotSterling,
  tblProducts.ProductSupplierLocalPrice,
  tblProducts.ProductDeleted,
  qryInventoryCountForProductSearch.CountOfInventoryQuantity
FROM (
  tblSupplierCurrency
  INNER JOIN tblSuppliers
          ON tblSupplierCurrency.SupplierCurrencyID = tblSuppliers.SupplierCurrencyID
)
INNER JOIN (
  tblProductStatus
  INNER JOIN (
    tblManufacturer
    RIGHT JOIN (
      tblProducts
      LEFT JOIN qryInventoryCountForProductSearch
             ON tblProducts.ProductID = qryInventoryCountForProductSearch.ProductID
    )
            ON tblManufacturer.ManufacturerID = tblProducts.ManufacturerID
  )
          ON tblProductStatus.ProductStatusID = tblProducts.ProductStatusID
)
        ON tblSuppliers.SupplierID = tblProducts.SupplierID
WHERE (
  (
    (tblProducts.ProductStatusID) Like "*" & [Forms]![frmProductSearch].[Form]![txtProductStatus] & "*"
  )
  AND
  (
    (tblManufacturer.ManufacturerName) Like "*" & [Forms]![frmProductSearch].[Form]![txtManufacturer] & "*"
  )
  AND
  (
    (tblSuppliers.SupplierName) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplier] & "*"
  )
  AND
  (
    (tblProducts.ProductID) Like "*" & [forms]![frmProductSearch].[Form]![txtProductID] & "*"
  )
  AND
  (
    (tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch1] & "*"
    And
    (tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch2] & "*"
  )
  AND
  (
    (tblProducts.ProductDateAdded) Between (Nz(([Forms]![frmProductSearch].[Form]![txtDate1]),1/1/1901)) And (Nz([Forms]![frmProductSearch].[Form]![txtDate2],#1/1/3000#))
  )
  AND
  (
    (tblProducts.SupplierPartNumber) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplierPartNumber] & "*"
  )
  AND
  (
    (tblProducts.ProductDeleted)=False
  )
  AND
  (
    (tblProducts.SupplierID) Like "*" & [Forms]![frmProductSearch].[Form]![cboSupplierID] & "*"
  )
)
   OR
(
  (
    (tblProducts.ProductStatusID) Like "*" & [Forms]![frmProductSearch].[Form]![txtProductStatus] & "*"
  )
  AND
  (
    (tblSuppliers.SupplierName) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplier] & "*"
  )
  AND
  (
    (tblProducts.ProductID) Like "*" & [forms]![frmProductSearch].[Form]![txtProductID] & "*"
  )
  AND
  (
    (tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch1] & "*"
    And
    (tblProducts.ProductTitle) Like "*" & [Forms]![frmProductSearch].[Form]![txtTitleSearch2] & "*"
  )
  AND
  (
    (tblProducts.ProductDateAdded) Between (Nz(([Forms]![frmProductSearch].[Form]![txtDate1]),1/1/1901)) And (Nz([Forms]![frmProductSearch].[Form]![txtDate2],#1/1/3000#))
  )
  AND
  (
    (tblProducts.SupplierPartNumber) Like "*" & [Forms]![frmProductSearch].[Form]![txtSupplierPartNumber] & "*"
  )
  AND
  (
    (tblProducts.ProductDeleted)=False
  )
  AND
  (
    (tblProducts.SupplierID) Like "*" & [Forms]![frmProductSearch].[Form]![cboSupplierID] & "*"
  )
  AND
  (
    ([Forms]![frmProductSearch].[Form]![txtManufacturer]) Is Null
  )
)
ORDER BY
  tblProducts.ProductStatusID,
  tblSuppliers.SupplierName,
  tblProducts.ProductID
;

Here is the same query in a more easy to understand form:
SQL: 
SELECT
  p.ProductStatusID,
  m.ManufacturerName,
  s.SupplierName,
  p.ProductID,
  p.ProductTitle,
  ps.ProductStatusCategory,
  p.DealerDiscount,
  p.ProductDateAdded,
  p.ProductLastModified,
  p.SupplierPartNumber,
  p.ProductPrice,
  sc.SupplierCurrency,
  Nz(ic.CountofInventoryQuantity,0) AS NZQuantity,
  p.ProductDetailDescription,
  p.ProductSummaryDescription,
  p.ProductVatable,
  p.ProductNotes,
  p.ProductInventoryRequiresSerialNumber,
  p.ProductIsVirtualItem,
  p.ProductModifiedBy,
  p.ProductsSupplierQuoteNumber,
  sc.SupplierCurrencyID,
  p.ProductPriceIfNotSterling,
  p.ProductSupplierLocalPrice,
  p.ProductDeleted,
  ic.CountOfInventoryQuantity
FROM (
  tblSupplierCurrency sc
  INNER JOIN tblSuppliers s
          ON sc.SupplierCurrencyID = s.SupplierCurrencyID
)
INNER JOIN (
  tblProductStatus ps
  INNER JOIN (
    tblManufacturer m
    RIGHT JOIN (
      tblProducts p
      LEFT JOIN qryInventoryCountForProductSearch ic
             ON p.ProductID = ic.ProductID
    )
            ON m.ManufacturerID = p.ManufacturerID
  )
          ON ps.ProductStatusID = p.ProductStatusID
)
        ON s.SupplierID = p.SupplierID
WHERE p.ProductStatusID Like '*' & [Forms]![frmProductSearch].[Form]![txtProductStatus] & '*'
  AND (
    m.ManufacturerName Like '*' & [Forms]![frmProductSearch].[Form]![txtManufacturer] & '*'
    OR
    [Forms]![frmProductSearch].[Form]![txtManufacturer] IS NULL
  )
  AND s.SupplierName Like '*' & [Forms]![frmProductSearch].[Form]![txtSupplier] & '*'
  AND p.ProductID Like '*' & [forms]![frmProductSearch].[Form]![txtProductID] & '*'
  AND p.ProductTitle Like '*' & [Forms]![frmProductSearch].[Form]![txtTitleSearch1] & '*'
  AND p.ProductTitle Like '*' & [Forms]![frmProductSearch].[Form]![txtTitleSearch2] & '*'
  AND p.ProductDateAdded Between (Nz(([Forms]![frmProductSearch].[Form]![txtDate1]),1/1/1901)) And (Nz([Forms]![frmProductSearch].[Form]![txtDate2],#1/1/3000#))
  AND p.SupplierPartNumber Like '*' & [Forms]![frmProductSearch].[Form]![txtSupplierPartNumber] & '*'
  AND p.ProductDeleted=False
  AND p.SupplierID Like '*' & [Forms]![frmProductSearch].[Form]![cboSupplierID] & '*'
ORDER BY
  p.ProductStatusID,
  s.SupplierName,
  p.ProductID
;

Your easiest path here would be to save the query without the ORDER BY clause.

Then use VBA to add an ORDER BY clause to the listbox RowSource, eg:
Code: 
Me.MyListbox.RowSource = "SELECT * FROM WhateverYouSavedTheQueryAs ORDER BY SupplierName DESC;""
Click to expand...
Thank you for the explanation of the reason for the syntax error and for the above.

I appreciate there is a LOT of data in the listbox - my boss likes to be able to see everything at the same time! You will appreciate that the listbox font size has to be very small.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 19:51
Joined
Sep 21, 2011
Messages
14,350
zebrafoot said:
Thank you for the explanation of the reason for the syntax error and for the above.

I appreciate there is a LOT of data in the listbox - my boss likes to be able to see everything at the same time! You will appreciate that the listbox font size has to be very small.
Click to expand...
Like your query? :cool:
I did not even attempt to read that on my laptop. :)
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:51
Joined
May 21, 2018
Messages
8,554
I appreciate there is a LOT of data in the listbox - my boss likes to be able to see everything at the same time!
Click to expand...
You may find this concept useable then. Lets you have a lot of data at one time, but manage the view.

Show / Hide Columns Easily in Datasheet

Recently I had to have a lot of visible fields on the form because each column was part of a detailed calculation. In order to validate the data I had to do a lot of comparison between different columns. So the datasheet needed to present a lot of columns, but that is difficult to process all...
www.access-programmers.co.uk
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom