Dear Sir or Madame
I have a query and a form with some Basic cde behind it.
I want from the basic "execute" a query and travestate the result and
have been experimenting with QueryDef but have not got it working.
My first question is, is it the right way to use QueryDef to do this type of work?
My second is, if the answear is yes to the earlier question, what am I doing wrong,
I get the error message "Run-time error '3265', Item not found in this collection",
I assume it is something wrong with the parameter setup, that is, ReportGroupLike and DelayedDaysLike
Both Form_Main!ReportGroupLike and Form_Main!DelayedDaysLike have values when when the execution stops and I am hovering over them in the editor.
Code:
Dim dbsNorthwind As DAO.Database
Dim rstRs As DAO.Recordset
Dim rstQd As DAO.QueryDef
Dim strSQL As String
Dim QD1 As DAO.QueryDef
Set dbsNorthwind = CurrentDb
Set QD1 = dbsNorthwind.QueryDefs!FindDelayedDeliveries
QD1.Parameters!Forms!Main!ReportGroupLike = Form_Main!ReportGroupLike
QD1.Parameters!Forms!Main!DelayedDaysLike = Form_Main!DelayedDaysLike
Set rstRs = QD1.OpenRecordset
Query:
SELECT
I have a query and a form with some Basic cde behind it.
I want from the basic "execute" a query and travestate the result and
have been experimenting with QueryDef but have not got it working.
My first question is, is it the right way to use QueryDef to do this type of work?
My second is, if the answear is yes to the earlier question, what am I doing wrong,
I get the error message "Run-time error '3265', Item not found in this collection",
I assume it is something wrong with the parameter setup, that is, ReportGroupLike and DelayedDaysLike
Both Form_Main!ReportGroupLike and Form_Main!DelayedDaysLike have values when when the execution stops and I am hovering over them in the editor.
Code:
Dim dbsNorthwind As DAO.Database
Dim rstRs As DAO.Recordset
Dim rstQd As DAO.QueryDef
Dim strSQL As String
Dim QD1 As DAO.QueryDef
Set dbsNorthwind = CurrentDb
Set QD1 = dbsNorthwind.QueryDefs!FindDelayedDeliveries
QD1.Parameters!Forms!Main!ReportGroupLike = Form_Main!ReportGroupLike
QD1.Parameters!Forms!Main!DelayedDaysLike = Form_Main!DelayedDaysLike
Set rstRs = QD1.OpenRecordset
Query:
SELECT
pt.APM, pt.Site, pt.Application, pt.Platform, pt.PlatformRelease,
CInt(pt.FinalPlanned) AS PlannedDelivery,
st.AppInitiallyCommittedRel AS ActualDelivery,
IIf(Len(ActualDelivery)>Len(PlannedDelivery),(((ActualDeliveryPlannedDelivery)-47)),(ActualDelivery-PlannedDelivery)) AS [Delayed days],
pt.Phase, pt.Sponsor, pt.ReportGroup, pt.FocalPointId, pt.Supplier
FROM CInt(pt.FinalPlanned) AS PlannedDelivery,
st.AppInitiallyCommittedRel AS ActualDelivery,
IIf(Len(ActualDelivery)>Len(PlannedDelivery),(((ActualDeliveryPlannedDelivery)-47)),(ActualDelivery-PlannedDelivery)) AS [Delayed days],
pt.Phase, pt.Sponsor, pt.ReportGroup, pt.FocalPointId, pt.Supplier
Projects AS pt, FullLatestStatus AS st
WHERE
(((st.AppInitiallyCommittedRel) Is Not Null And
(st.AppInitiallyCommittedRel)>0) And
((pt.FinalPlanned) Is Not Null And
(pt.FinalPlanned)<>'' And
(pt.FinalPlanned)<>'0') And
((pt.ReportGroup) Like Forms!Main!ReportGroupLike) And
((CInt(pt.FinalPlanned)-st.AppInitiallyCommittedRel) < Forms!Main!DelayedDaysLike) And
(pt.Phase<>"On Hold") And
(pt.Application=st.Application) And
(pt.Platform=st.Platform) And
(pt.PlatformRelease=st.Release))
ORDER BY (st.AppInitiallyCommittedRel)>0) And
((pt.FinalPlanned) Is Not Null And
(pt.FinalPlanned)<>'' And
(pt.FinalPlanned)<>'0') And
((pt.ReportGroup) Like Forms!Main!ReportGroupLike) And
((CInt(pt.FinalPlanned)-st.AppInitiallyCommittedRel) < Forms!Main!DelayedDaysLike) And
(pt.Phase<>"On Hold") And
(pt.Application=st.Application) And
(pt.Platform=st.Platform) And
(pt.PlatformRelease=st.Release))
pt.Site, pt.APM, pt.Application, pt.Platform, pt.PlatformRelease, pt.Supplier;
