Query in Listbox

[jerrywolf]

Hi All,

I've been banging my head against the wall all day today trying to figure out what should be super simple.

I have a products form, we are a manufacturing company, with a listbox to show recent inventory transactions. This is based on a query which shows all transactions with the current part id, and that all works well and fine.

The problem is, I would like to limit this query to show only the last 10 transactions in the listbox and not make it editable, ie not enabled. I set the show only in query design view to 10 and it says in the sql statement select top 10, however, the listbox consistently shows all related records. What am I missing?

Thanks

 

[pbaldy]

What's the SQL?

 

[jerrywolf]

What's the SQL?

SELECT TOP 10 tbl_Transactions.TransactionDate AS [Date], tbl_Transactions.QtyIn AS [In], tbl_Transactions.QtyOut AS Out, tbl_Transactions.Notes FROM tbl_Parts INNER JOIN tbl_Transactions ON tbl_Parts.PartId = tbl_Transactions.PartId WHERE (((tbl_Parts.PartId)=[Forms]![frm_ScannedMainPage]![PartId])) ORDER BY tbl_Transactions.TransactionDate DESC;

 

[spikepl]

Top 10 grabs all the records that fit the last 10 values of your OrderBY clause! You probably expected 10 records... To get that, add something that would allow the SQL to discern between the records

 

[jerrywolf]

Hmmm ok i'm a little lost, what would you recommend?

Thanks

 

[spikepl]

Attach a screenshot (porbably needs to be zipped) of fully expanded tables involved in this query in the relations window

 

[jerrywolf]

here ya go

 

[spikepl]

Try

ORDER BY tbl_Transactions.TransactionDate DESC, tbl_Transactions.TransactionID DESC

 

[jerrywolf]

Try

ORDER BY tbl_Transactions.TransactionDate DESC, tbl_Transactions.TransactionID DESC

Thanks!! it worked perfectly!