QueryDef

B

Brad

Thanks for taking the time to read my question.

I am trying to use a query as a recordset. I am using SQL instead of an
actual query because I need to pass a variable to it. I can't get past the
line

Set qdf = dbs.QueryDefs .....

The error I get is "Item not found in this collection." The error number is
3265.

What am I doing wrong?

Thanks,

Brad

Code:
--------------------------------------------------------------------------

Dim dbs As Database, rst As Recordset, qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("SELECT tblHealthServicesData.ID,
tblServiceProvider.ServiceProvider, tblHealthServicesData.BarnName,
tblHealthServicesData.[TotalDrivingTime(hours)],
tblHealthServicesData.TotalKms, tblHerdInformation.AccountingNumber,
tblServiceProvider.DrivingRate, tblHealthServicesData.BilledYN,
tblHealthServicesData.OverRideFree, tblHerdInformation.NumberOfFreeVisits,
tblFreeVisits.NumFreeVisits AS AllowableVisits,
tblActivitiesForInvoice.BillingStatus,
tblHealthServicesDataDetail.InvoiceActivityType,
tblHealthServicesDataDetail.[InvoiceTime(hours)],
tblHealthServicesDataDetail.Comments,
tblHealthServicesData.ReleaseForInvoicing, [tblHealthServicesDataDetail]![ID]
AS DetailID, tblHealthServicesDataDetail.ActivityDate " & _
"FROM (tblFreeVisits INNER JOIN (qryRecCount INNER
JOIN (tblHerdInformation INNER JOIN (tblHealthServicesData INNER JOIN
tblServiceProvider ON tblHealthServicesData.ServiceProvider =
tblServiceProvider.ServiceProviderCode) ON tblHerdInformation.BarnName =
tblHealthServicesData.BarnName) ON qryRecCount.tblHealthServicesDataID =
tblHealthServicesData.ID) ON tblFreeVisits.TypeOfFarm =
tblHerdInformation.StageOfProduction) INNER JOIN (tblHealthServicesDataDetail
INNER JOIN tblActivitiesForInvoice ON
tblHealthServicesDataDetail.InvoiceActivityType =
tblActivitiesForInvoice.InvoiceActivityType) ON tblHealthServicesData.ID =
tblHealthServicesDataDetail.tblHealthServicesDataID " & _
"WHERE (((tblHealthServicesData.ID) = " & RecID & ")
And ((tblHealthServicesData.BilledYN) = False) And
((tblActivitiesForInvoice.BillingStatus) <> 5) And
((tblHealthServicesData.ReleaseForInvoicing) = True)) " & _
"ORDER BY tblHealthServicesData.ID,
tblActivitiesForInvoice.BillingStatus;")


Set rst = qdf.OpenRecordset()
 
K

Klatuu

Brad, Here is an example of how to use SQL instead of a query.

strSQL = "SELECT tblPipeline80.Jan, " _
& "tblPipeline80.Feb, tblPipeline80.Mar, tblPipeline80.Apr,
" _
& "tblPipeline80.May, tblPipeline80.Jun, tblPipeline80.Jul,
" _
& "tblPipeline80.Aug, tblPipeline80.Sep, tblPipeline80.Oct,
" _
& "tblPipeline80.Nov, tblPipeline80.Dec " _
& "FROM tblPipeline80 " _
& "WHERE (((tblPipeline80.Curr_Year) = '" & strYear & "')" _
& "AND ((tblPipeline80.Curr_Month) = '" & strMonth & "') AND
" _
& "((tblPipeline80.ITM) = '" & strItm & "') AND " _
& "((tblPipeline80.Resource) = '" & strResource & "'));"
If strItmPM = "PM" Then
strSQL = Replace(strSQL, "tblPipeline80.ITM",
"tblPipeline80.Program_Manager")
End If
Set rstPipeline = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot,
dbReadOnly)
If rstPipeline.RecordCount = 0 Then
blnGotData = False
Else
blnGotData = True
rstPipeline.MoveLast
rstPipeline.MoveFirst
End If

You do not need a QueryDefs in your code.

Brad said:
Thanks for taking the time to read my question.

I am trying to use a query as a recordset. I am using SQL instead of an
actual query because I need to pass a variable to it. I can't get past the
line

Set qdf = dbs.QueryDefs .....

The error I get is "Item not found in this collection." The error number is
3265.

What am I doing wrong?

Thanks,

Brad

Code:
--------------------------------------------------------------------------

Dim dbs As Database, rst As Recordset, qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("SELECT tblHealthServicesData.ID,
tblServiceProvider.ServiceProvider, tblHealthServicesData.BarnName,
tblHealthServicesData.[TotalDrivingTime(hours)],
tblHealthServicesData.TotalKms, tblHerdInformation.AccountingNumber,
tblServiceProvider.DrivingRate, tblHealthServicesData.BilledYN,
tblHealthServicesData.OverRideFree, tblHerdInformation.NumberOfFreeVisits,
tblFreeVisits.NumFreeVisits AS AllowableVisits,
tblActivitiesForInvoice.BillingStatus,
tblHealthServicesDataDetail.InvoiceActivityType,
tblHealthServicesDataDetail.[InvoiceTime(hours)],
tblHealthServicesDataDetail.Comments,
tblHealthServicesData.ReleaseForInvoicing, [tblHealthServicesDataDetail]![ID]
AS DetailID, tblHealthServicesDataDetail.ActivityDate " & _
"FROM (tblFreeVisits INNER JOIN (qryRecCount INNER
JOIN (tblHerdInformation INNER JOIN (tblHealthServicesData INNER JOIN
tblServiceProvider ON tblHealthServicesData.ServiceProvider =
tblServiceProvider.ServiceProviderCode) ON tblHerdInformation.BarnName =
tblHealthServicesData.BarnName) ON qryRecCount.tblHealthServicesDataID =
tblHealthServicesData.ID) ON tblFreeVisits.TypeOfFarm =
tblHerdInformation.StageOfProduction) INNER JOIN (tblHealthServicesDataDetail
INNER JOIN tblActivitiesForInvoice ON
tblHealthServicesDataDetail.InvoiceActivityType =
tblActivitiesForInvoice.InvoiceActivityType) ON tblHealthServicesData.ID =
tblHealthServicesDataDetail.tblHealthServicesDataID " & _
"WHERE (((tblHealthServicesData.ID) = " & RecID & ")
And ((tblHealthServicesData.BilledYN) = False) And
((tblActivitiesForInvoice.BillingStatus) <> 5) And
((tblHealthServicesData.ReleaseForInvoicing) = True)) " & _
"ORDER BY tblHealthServicesData.ID,
tblActivitiesForInvoice.BillingStatus;")


Set rst = qdf.OpenRecordset()
Click to expand...
 
B

Brad

Thanks!

I'll give it a shot

Brad

Klatuu said:
Brad, Here is an example of how to use SQL instead of a query.

strSQL = "SELECT tblPipeline80.Jan, " _
& "tblPipeline80.Feb, tblPipeline80.Mar, tblPipeline80.Apr,
" _
& "tblPipeline80.May, tblPipeline80.Jun, tblPipeline80.Jul,
" _
& "tblPipeline80.Aug, tblPipeline80.Sep, tblPipeline80.Oct,
" _
& "tblPipeline80.Nov, tblPipeline80.Dec " _
& "FROM tblPipeline80 " _
& "WHERE (((tblPipeline80.Curr_Year) = '" & strYear & "')" _
& "AND ((tblPipeline80.Curr_Month) = '" & strMonth & "') AND
" _
& "((tblPipeline80.ITM) = '" & strItm & "') AND " _
& "((tblPipeline80.Resource) = '" & strResource & "'));"
If strItmPM = "PM" Then
strSQL = Replace(strSQL, "tblPipeline80.ITM",
"tblPipeline80.Program_Manager")
End If
Set rstPipeline = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot,
dbReadOnly)
If rstPipeline.RecordCount = 0 Then
blnGotData = False
Else
blnGotData = True
rstPipeline.MoveLast
rstPipeline.MoveFirst
End If

You do not need a QueryDefs in your code.

Brad said:
Thanks for taking the time to read my question.

I am trying to use a query as a recordset. I am using SQL instead of an
actual query because I need to pass a variable to it. I can't get past the
line

Set qdf = dbs.QueryDefs .....

The error I get is "Item not found in this collection." The error number is
3265.

What am I doing wrong?

Thanks,

Brad

Code:
--------------------------------------------------------------------------

Dim dbs As Database, rst As Recordset, qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("SELECT tblHealthServicesData.ID,
tblServiceProvider.ServiceProvider, tblHealthServicesData.BarnName,
tblHealthServicesData.[TotalDrivingTime(hours)],
tblHealthServicesData.TotalKms, tblHerdInformation.AccountingNumber,
tblServiceProvider.DrivingRate, tblHealthServicesData.BilledYN,
tblHealthServicesData.OverRideFree, tblHerdInformation.NumberOfFreeVisits,
tblFreeVisits.NumFreeVisits AS AllowableVisits,
tblActivitiesForInvoice.BillingStatus,
tblHealthServicesDataDetail.InvoiceActivityType,
tblHealthServicesDataDetail.[InvoiceTime(hours)],
tblHealthServicesDataDetail.Comments,
tblHealthServicesData.ReleaseForInvoicing, [tblHealthServicesDataDetail]![ID]
AS DetailID, tblHealthServicesDataDetail.ActivityDate " & _
"FROM (tblFreeVisits INNER JOIN (qryRecCount INNER
JOIN (tblHerdInformation INNER JOIN (tblHealthServicesData INNER JOIN
tblServiceProvider ON tblHealthServicesData.ServiceProvider =
tblServiceProvider.ServiceProviderCode) ON tblHerdInformation.BarnName =
tblHealthServicesData.BarnName) ON qryRecCount.tblHealthServicesDataID =
tblHealthServicesData.ID) ON tblFreeVisits.TypeOfFarm =
tblHerdInformation.StageOfProduction) INNER JOIN (tblHealthServicesDataDetail
INNER JOIN tblActivitiesForInvoice ON
tblHealthServicesDataDetail.InvoiceActivityType =
tblActivitiesForInvoice.InvoiceActivityType) ON tblHealthServicesData.ID =
tblHealthServicesDataDetail.tblHealthServicesDataID " & _
"WHERE (((tblHealthServicesData.ID) = " & RecID & ")
And ((tblHealthServicesData.BilledYN) = False) And
((tblActivitiesForInvoice.BillingStatus) <> 5) And
((tblHealthServicesData.ReleaseForInvoicing) = True)) " & _
"ORDER BY tblHealthServicesData.ID,
tblActivitiesForInvoice.BillingStatus;")


Set rst = qdf.OpenRecordset()
Click to expand...
Click to expand...
 
M

Marshall Barton

Brad said:
I am trying to use a query as a recordset. I am using SQL instead of an
actual query because I need to pass a variable to it. I can't get past the
line

Set qdf = dbs.QueryDefs .....

The error I get is "Item not found in this collection." The error number is
3265.

Code:
--------------------------------------------------------------------------
Dim dbs As Database, rst As Recordset, qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("SELECT tblHealthServicesData.ID,
tblServiceProvider.ServiceProvider, tblHealthServicesData.BarnName,
tblHealthServicesData.[TotalDrivingTime(hours)],
tblHealthServicesData.TotalKms, tblHerdInformation.AccountingNumber,
tblServiceProvider.DrivingRate, tblHealthServicesData.BilledYN,
tblHealthServicesData.OverRideFree, tblHerdInformation.NumberOfFreeVisits,
tblFreeVisits.NumFreeVisits AS AllowableVisits,
tblActivitiesForInvoice.BillingStatus,
tblHealthServicesDataDetail.InvoiceActivityType,
tblHealthServicesDataDetail.[InvoiceTime(hours)],
tblHealthServicesDataDetail.Comments,
tblHealthServicesData.ReleaseForInvoicing, [tblHealthServicesDataDetail]![ID]
AS DetailID, tblHealthServicesDataDetail.ActivityDate " & _
"FROM (tblFreeVisits INNER JOIN (qryRecCount INNER
JOIN (tblHerdInformation INNER JOIN (tblHealthServicesData INNER JOIN
tblServiceProvider ON tblHealthServicesData.ServiceProvider =
tblServiceProvider.ServiceProviderCode) ON tblHerdInformation.BarnName =
tblHealthServicesData.BarnName) ON qryRecCount.tblHealthServicesDataID =
tblHealthServicesData.ID) ON tblFreeVisits.TypeOfFarm =
tblHerdInformation.StageOfProduction) INNER JOIN (tblHealthServicesDataDetail
INNER JOIN tblActivitiesForInvoice ON
tblHealthServicesDataDetail.InvoiceActivityType =
tblActivitiesForInvoice.InvoiceActivityType) ON tblHealthServicesData.ID =
tblHealthServicesDataDetail.tblHealthServicesDataID " & _
"WHERE (((tblHealthServicesData.ID) = " & RecID & ")
And ((tblHealthServicesData.BilledYN) = False) And
((tblActivitiesForInvoice.BillingStatus) <> 5) And
((tblHealthServicesData.ReleaseForInvoicing) = True)) " & _
"ORDER BY tblHealthServicesData.ID,
tblActivitiesForInvoice.BillingStatus;")

Set rst = qdf.OpenRecordset()
Click to expand...


A QueryDef is an object with an SQL property. You are
trying to use it as if it was the SQL statement. The error
is because QueryDefs needs the name of an existing saved
query, not an SQL statement. From what I can deduce in your
code, you do not have a reason to use a QueryDef at all.
Just open the recordset on the SQL string:

Dim dbs As Database, rst As Recordset, strSQL As String
Set dbs = CurrentDb()
strSQL ="SELECT tblHealthServicesData.ID, " & _
" . . . " & _
"tblActivitiesForInvoice.BillingStatus;"
Set rst = dbs.OpenRecordset(strSQL)

Alternatively, if the SQL statement were saved as a query,
with RecID as a parameter, then you do need to use the
QueryDef to open the recordset:

Dim dbs As Database, rst As Recordset, qdf As QueryDef
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("nameofquery")
Set qdf.Parameters("RecID") = Me.RecID
Set rst = qdf.OpenRecordset
 
Top