I am supporting an Access 2016 application for a delivery company. The following bit of SQL is executed each day for every vehicle in the fleet. And it works (almost) all the time.
But on occasion for no apparent reason I get the “Enter Parameter Value” prompt for [Forms]![frmShowDailySchedule]![txtEndDate]
INSERT INTO tblDailyLoadingList (
Vehicle,
ProductID,
ProductName,
DeliveryDate,
seqp )"
SELECT
tblTempOrders.Vehicle,
tblTempOrders.ProductID,
tblTempOrders.ProductName,
[Forms]![frmShowDailySchedule]![txtEndDate] AS Expr1,
tblProducts.[seqp:]"
FROM (tblTempOrders
INNER JOIN tblProducts
ON tblTempOrders.ProductID = tblProducts.ProductID)
INNER JOIN tblEggStockType
ON tblProducts.EggStockType = tblEggStockType.EggStockTypeID"
GROUP BY tblTempOrders.Vehicle,
tblTempOrders.ProductID,
tblTempOrders.ProductName,
tblEggStockType.SortOrder,
tblProducts.[seqp:];"
If I insert a MSGBOX for [Forms]![frmShowDailySchedule]![txtEndDate] just before entering the SQL it shows that there is a valid date in the item.
So I cannot understand why occasionally this happens. Once it does happen for a particular vehicle it keeps happening. But swop to another vehicle and it works OK