Top n query using a parameter passed from a form

[mdgibbs88]

Hello All,

I have a table that contains an inventory of boxes (BoxNumber is Auto-Numbered) when added to the table. This is an effort to create a FIFO system for the boxes.

When an invoice is created, I want to update "n" number of boxes from the inventory with a new status.

Of course this works to grab the boxes using a static number in the Select:

SELECT TOP 6 RunningInventory.BoxNumber, RunningInventory.Product_ID, RunningInventory.RemovedFromInventory,
RunningInventory.Price
FROM RunningInventory
WHERE RunningInventory.Product_ID=31 and RunningInventory.RemovedFromInventory = False
ORDER BY RunningInventory.BoxNumber, RunningInventory.RemovedFromInventory DESC;

But I want to replace the 6 with a variable that is passed from the form, so I can Update the RemovedFromInventory field to True for the oldest boxes from the inventory and add the unique prices of those specific boxes to the invoice.

Thanks in advance for any help! Mark

 

[jleach]

I think you'd want to build the query via VBA and open the recordset accordingly.

Something like this:

Const SQL_TEMPLATE = "SELECT TOP [[Amount]] ... (rest of your query) ...;"
Dim sql As String
sql = Replace(SQL_TEMPLATE, "[[Amount]]", Me!TopAmount.Value)

Me.SomeDisplay.Recordsource = sql
'or...
Dim rs As DAO.Recordset
Set rs = currentDb.OpenRecordset(sql, dbOpenSnapshot)
'do stuff with your recordset

Something like that?

Or you can save the query with TOP 6 and do something like this:

Dim sql As String
sql = CurrentDb.QueryDefs("QueryName").SQL
sql = Replace(sql, "TOP 6", "TOP " & Me.YourControl.Value)
' then use the recordset accordingly

 

[Pat Hartman]

No element of the SQL expression can be paramaterized. Only data values can be substituted.

You would need to create the SQL string using VBA