Row Numbers In Join Query

[PC User]

I've have used the usual code format for creating a column of row numbers for my query, but it doesn't seem to work.

Sequence: DCount("InventoryID","Chemical Location","InventoryID <=" & [InventoryID])

I got this error

The Microsoft Office Access database engine cannot find the input table or query 'Chemical Location'. Make sure it exists and that its name is spelled correctly.

So I tried this:

Sequence: DCount("InventoryID","qexpFacilityInventory","InventoryID <=" & [InventoryID])

And I got this error:

The expression you entered as a query paramenter produced this error: Business Emergency Plans can't find the form 'frmMain' referred to in a macro expression or Visual Basic code

I think my code doesn't work because its a join query; so I'm searching for an answer. I have attached an image of the query builder in Access 2007 to show my problem. Also I'm showing the SQL code. Can someone help me with putting row numbers in a Join Query "qexpFacilityInventory"?

SELECT tblChemicalInventory.[Business Name], tblChemicalInventory.[Item Number], tblChemicalInventory.[Chemical Location], tblChemicalInventory.[Chemical Name], tblChemicalInventory.[Common Name], tblChemicalInventory.[Map ], tblChemicalInventory.[Grid ], tblChemicalInventory.[Largest Container], tblChemicalInventory.[Average Daily Amount], tblChemicalInventory.[Maximum Daily Amount], tblChemicalInventory.Units, tblChemicalInventory.[Storage Container], tblChemicalInventory.[Usage Purpose], tblChemicalInventory.[Annual Waste Amount], tblChemicalInventory.STATUS AS Reviewer, tblChemicalInventory.STATUS AS [Modified Yes/No], tblChemicalInventory.STATUS AS Comments
FROM tblChemicalInventory INNER JOIN tblChemicalProperties ON tblChemicalInventory.ChemicalID = tblChemicalProperties.ChemicalID
WHERE (((tblChemicalInventory.BusinessID)=[Forms]![frmMain]![ctlGenericSubform].[Form]![BusinessID]));

Thanks,
PC

 

[PC User]

For those of you who can use this information, someone has helped me figure it out.

Sequence: (SELECT Count(Index.InventoryID) AS TempID
FROM tblChemicalInventory AS Index
WHERE ((Index.InventoryID <= tblChemicalInventory.InventoryID) And (Index.BusinessID = [Forms]![frmMain]![ctlGenericSubform].[Form]![BusinessID])))