Still working on the same problem as my earlier post today. Trying to avoid calling the query optimizer so many times in a VBA loop by using a parameter query. Tried two versions of a stored query: one with hard-wired criteria and one with parameters. Double-clicking this query from the database window generates a message that 699 rows will be updated. I cancel the actual updating, leaving the records unchanged.
UPDATE TimeLine INNER JOIN qryGPSPosSelectedWithoutATIIDfk ON TimeLine.MachineID = qryGPSPosSelectedWithoutATIIDfk.MachineID
SET qryGPSPosSelectedWithoutATIIDfk.ATIIDfk = timeline.Recnum
WHERE (((qryGPSPosSelectedWithoutATIIDfk.TimeTag)>=[starttime] And (qryGPSPosSelectedWithoutATIIDfk.TimeTag)=[starttime] And (qryGPSPosSelectedWithoutATIIDfk.TimeTag)<[stoptime])
AND ((qryGPSPosSelectedWithoutATIIDfk.MachineID)=[GPSMachineID])
AND ((TimeLine.MachineID)=[ATIMachineID])
AND ((TimeLine.ZoneID)=[CurrentZoneID])
AND ((Int([TimeLine].[Starttime]))=[DayNo]));
Running this VBA snippet against the same parameter query and with the correct values in the different variables also updates zero records
Set qdf = db.QueryDefs("qupdGPSPosWithATIIDdynamic") ' this is the same parameter query
qdf.Parameters("GPSMachineID") = (!MachineID)
qdf.Parameters("ATIMachineID") = (!MachineID)
qdf.Parameters("CurrentZoneID") = (!ZoneID)
qdf.Parameters("DayNo") = (!DayNo)
qdf.Execute
debug qdf.recordsaffected
Can anyone tell me where I am making the mistake, 'cuz I can't see it! Thanks.
