Limiting records in a list box - UNBOUND

[byurow]

I'm stumped! I have a list box that gets populated through a query. The query pulls data from 7 tables! I have not been able to come up with a query that will limit the data! So, I was wondering if it can be done through the form.

What I have done is create 2 queries. The first query pulls the data from 5 of the 7 tables and limits the data to only give me the "open" records (based on whether or not 1 of 2 fields is NOT Null. The code is as follows:

SELECT tblBatchNo.BatchNoID, tblBatchNo.BatchNo, tblProduct.ProdID, tblProduct.ProdCode, tblSKU.SKUNo, tblSKUFillLines.FilledUnits, tblSKUFillLines.FilledGals
FROM ((tblProduct INNER JOIN (tblBatchNo INNER JOIN tblBatchNoProduct ON tblBatchNo.BatchNoID = tblBatchNoProduct.BatchNoID) ON tblProduct.ProdID = tblBatchNoProduct.ProdID) INNER JOIN tblSKU ON tblProduct.ProdID = tblSKU.ProdID) INNER JOIN tblSKUFillLines ON tblSKU.SKUID = tblSKUFillLines.SKUID
WHERE (((tblSKUFillLines.FilledUnits) Is Null)) OR (((tblSKUFillLines.FilledGals) Is Null));

I then take that query and pull in the other 2 tables. It is from this query that I wanted to limit the data to only the most current record for each "batch". Since I was not able to find a method of doing this (the queries list couldn't help either - although they tried and gave some good advice) I simply left all of the records in (total of 9 records where I should only see the most current 3). The second's query is as follows:

SELECT DISTINCT tblProductPriorityCode.BatchPriorityCodeID, tblProductPriorityCode.PriorityCodeID, tblPriorityCode.PriorityCode, tblProductPriorityCode.DaysAvailable, QryOpenBatches.BatchNoID, QryOpenBatches.BatchNo, QryOpenBatches.ProdID, QryOpenBatches.ProdCode
FROM tblPriorityCode INNER JOIN (QryOpenBatches INNER JOIN tblProductPriorityCode ON QryOpenBatches.BatchNoID = tblProductPriorityCode.BatchNoID) ON tblPriorityCode.PriorityCodeID = tblProductPriorityCode.PriorityCodeID
ORDER BY tblProductPriorityCode.PriorityCodeID;

Ok, now to the form. My code works great for bringing in the data as it currently stands in the query. Is it possible to add a DMax or something to limit the data? I want the Max value from tblProductPriorityCode.PriorityCodeID.

My forms code is as follows:

Dim qdfPriorityCodes As QueryDef
Dim rsPriorityCodes As Recordset
Dim blnNumPriorityCodes As Boolean

On Error Resume Next
Set qdfPriorityCodes = CurrentDb.QueryDefs("QryOpenBatchesForPriority&quot

Set rsPriorityCodes = qdfPriorityCodes.OpenRecordset
blnNumPriorityCodes = rsPriorityCodes.BOF And rsPriorityCodes.EOF
Me.LstPriorityCodes.RowSource = Empty
DoCmd.Hourglass True

Do While Not rsPriorityCodes.EOF
Me.LstPriorityCodes.RowSource = Me.LstPriorityCodes.RowSource & """" & rsPriorityCodes!BatchPriorityCodeID & """;" _
& """" & rsPriorityCodes!PriorityCode & """;" _
& """" & rsPriorityCodes!DaysAvailable & """;" _
& """" & rsPriorityCodes!BatchNo & """;" _
& """" & rsPriorityCodes!ProdCode & """;" _
& """" & rsPriorityCodes!BatchNoID & """;" _
& """" & rsPriorityCodes!PriorityCodeID & """;" _
& """" & rsPriorityCodes!ProdID & """;"

rsPriorityCodes.MoveNext
Loop

rsPriorityCodes.Close

Set rsPriorityCodes = Nothing

DoCmd.Hourglass False

Sorry to be so wordy, but I hope all the information will help you help me!

Thanks!

Brenda

 

[byurow]

Never mind! I was working the queries backwards! I was trying to get the unique value after I linked all of the tables - duh!!!!!!

Sorry if I wasted your time!

Thanks anyway!

Brenda