Opening a recordset with parameters

[darees1]

I am trying (and failing) to open a query which contains a parameter
(named ‘myprop’) and export the results to Excel. The problem seems to
be defining and opening the recordset with parameters. Any help would
be greatly appreciated. Here is the code:


Option Compare Database

Public Sub BillingExtract()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Dim App As Excel.Application
Dim WB1, WB2 As Excel.Workbook
Dim WS1, WS2 As Excel.Worksheet


Set db = CurrentDb
Set qdf = db.QueryDefs("Qry_Recent_Reads")
qdf.Parameters("[myprop]").Value = "AD"


'establish link to excel
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = Workbooks.Add
objWkb.Sheets("Sheet1").Name = "ECS Upload"
Set objSht = objWkb.Worksheets(1)
objSht.Visible = xlSheetVisible

'export query results to Excel
Set rs = qdf.OpenRecordset()

intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow,
intMaxCol)).CopyFromRecordset rs
End With
End If

Set rs = Nothing
Set db = Nothing
Set qdf = Nothing

End Sub

 

[John W. Vinson/MVP]

I am trying (and failing) to open a query which contains a parameter
(named ‘myprop’) and export the results to Excel. The problem seems to
be defining and opening the recordset with parameters. Any help would
be greatly appreciated. Here is the code:

What is going wrong? Do you get an error message, no data, wrong
data,...? Could you also post the SQL view of the query itself?

 

[darees1]

What is going wrong? Do you get an error message, no data, wrong
data,...? Could you also post the SQL view of the query itself?

I'm not getting any data. When the routine is running, the record
count shows zero. This is the SQL code:

SELECT R.Meter_ID, R.Date, tblMeters.Meter_ID AS Meter_ID, *
FROM (tblECS INNER JOIN tblMeters ON tblECS.ECS_Ref =
tblMeters.ECS_Ref) INNER JOIN tblReadings AS R ON tblMeters.Meter_ID =
R.Meter_ID
WHERE (((R.Date)=(select Max(Date) from tblReadings r1 where
r1.meter_ID=r.meter_ID)) AND ((tblMeters.Meter_ID)=[myprop]))
ORDER BY R.Meter_ID;

 

[John W. Vinson/MVP]

What is going wrong? Do you get an error message, no data, wrong
data,...? Could you also post the SQL view of the query itself?

I'm not getting any data. When the routine is running, the record
count shows zero. This is the SQL code:

SELECT R.Meter_ID, R.Date, tblMeters.Meter_ID AS Meter_ID, *
FROM (tblECS INNER JOIN tblMeters ON tblECS.ECS_Ref =
tblMeters.ECS_Ref) INNER JOIN tblReadings AS R ON tblMeters.Meter_ID =
R.Meter_ID
WHERE (((R.Date)=(select Max(Date) from tblReadings r1 where
r1.meter_ID=r.meter_ID)) AND ((tblMeters.Meter_ID)=[myprop]))
ORDER BY R.Meter_ID;

One possible problem is that you're using the reserved word Date as a
fieldname. Date() is a builtin function returning today's date, and
Access may be using that rather than the field named date. Try editing
the query so that you use [Date] in all places where Date appears.

If you just run the query from the query window, does it prompt for
myprop? Does it return the expected answer if you type an id value in
response?
i

 

[darees1]

I'm not getting any data. When the routine is running, the record
count shows zero. This is the SQL code:

SELECT R.Meter_ID, R.Date, tblMeters.Meter_ID AS Meter_ID, *
FROM (tblECS INNER JOIN tblMeters ON tblECS.ECS_Ref =
tblMeters.ECS_Ref) INNER JOIN tblReadings AS R ON tblMeters.Meter_ID =
R.Meter_ID
WHERE (((R.Date)=(select Max(Date) from tblReadings r1 where
r1.meter_ID=r.meter_ID)) AND ((tblMeters.Meter_ID)=[myprop]))
ORDER BY R.Meter_ID;

One possible problem is that you're using the reserved word Date as a
fieldname. Date() is a builtin function returning today's date, and
Access may be using that rather than the field named date. Try editing
the query so that you use [Date] in all places where Date appears.

If you just run the query from the query window, does it prompt for
myprop? Does it return the expected answer if you type an id value in
response?
i

--

John W. Vinson/MVP- Hide quoted text -

- Show quoted text -

Yes, it does give the results I am expecting. I originally wrote the
query without any parameters and it was fine. The problem I have is
defining the parameters and then opening the recordset with the
parameters in.