Autofill date range form (1 Viewer)

[leafsrock100]

I have two user-inputted date range forms. One controls the inventory while the other control the sales report.

When I input the sales report date range, which is linked to the inventory, it asked me to input a date range from the inventory form in the form of an Access warning box.

Is there anyway to autofill/match the begin date of the inventory report form to the begin date of the sales report form?

 

[vbaInet]

Use a textbox instead of the prompt and in the query criteria for both reports you would reference the textbox like this:

[Forms]![NameOfForm]![NameOfTextbox]

 

[leafsrock100]

I use a textbox for both of the date range forms already. The sales report won't query without the "Enter Parameter Value" box from coming up

i.e. it asks for the following:

"Forms!Filtered Inventory Summary Date Range!BeginDate"

when I click the preview button. I think this is because the sales report needs data from another query called "Beginning inventory" that needs the "begin date" from the inventory date range form

 

[vbaInet]

Notice how mine was written, with square brackets (i.e. []). If the names contain spaces or special characters then you must enclose them in square brackets:

[Forms]![Filtered Inventory Summary Date Range]![BeginDate]

 

[leafsrock100]

I have that written your way in my "beginning inventory" query though. When I click to open that query, I get the same box that pops up

The SQL statement for that query is:

SELECT [Inventory Transactions].ProductID, Sum(nz([QtyReceived],0)-nz([QtyShipped],0)-nz([QtyShrinkage],0)) AS [Beginning Inventory], IIf([Beginning Inventory]=0,0,Format(Sum([RawUnitPrice]*[QtyReceived])/Sum([QtyReceived]),"$0.00")) AS BegUnitPrice, Units.UnitType
FROM [Inventory Transactions] INNER JOIN Units ON [Inventory Transactions].UnitID = Units.UnitID
WHERE ((([Inventory Transactions].TransactionDate)<=[Forms]![Filtered Inventory Summary Date Range]![BeginDate]))
GROUP BY [Inventory Transactions].ProductID, Units.UnitType;

Don't know if that helps or not

 

[vbaInet]

The form must be open before you run the query

 

[leafsrock100]

So there is no way I can open my sales report without have the inventory summary date range form open and the "begindate" text box filled out?

 

[vbaInet]

Nope! Create a (smaller) separate form to handle this which you would use for your report only.

 

[leafsrock100]

Thanks for the helpful advice!

 

[vbaInet]

You're welcome.