How do I use recordsets in a report?

[Guest]

I'm trying to use recordsets in my report but I get an error
message--Run-time error '2448' on the following code at the Line Me!CityCode
= rst!CityCode

Dim rst As Recordset
Dim dbs As Database
Dim strSQL As String
Dim strRate As String
Dim strRoute As String
Dim strDriver As String
Dim strlocation As String
Dim intCounter As Integer



Set dbs = CurrentDb
strSQL = "SELECT CityCode, RouteNumber, Driver, SalesCounter,
LocationCode, UnitType, OrderDate, UnitNumber, AmountCollected, CounterNumber
FROM [Location ID] WHERE CityCode='" & [Forms]![Report Date Dialog]![City] &
"' AND (OrderDate >=#" & [Forms]![Report Date Dialog]![BeginningDate] & "#
And OrderDate <=#" & [Forms]![Report Date Dialog]![EndingDate] & "# And
AmountCollected <> 0 And CounterNumber <> 0) "

Set rst = dbs.OpenRecordset(strSQL)

If Not rst.EOF Then
Do While Not rst.EOF
strlocation = rst!LocationCode
inCounter = 1
Do While rst!LocationCode = strlocation
Me![CityCode] = rst![CityCode]
Me!RouteNumber = rst!RouteNumber
Me!Driver = rst!Driver
Me!SalesCounter = rst!SalesCounter
Me!LocationCode = rst!LocationCode
Me!UnitType = rst!UnitType
Me!OrderDate = rst!OrderDate
Me!UnitNumber = rst!UnitNumber
Me!AmountCollected = rst!AmountCollected
Me!CounterNumber = rst!CounterNumber
intTotalCounter = intTotalCounter - rst!CounterNumber
rst.MoveNext
If rst.EOF Then
Exit Do
End If
Loop
Loop
End If
End Sub

 

[Brendan Reynolds]

Does your report contain a control named 'CityCode'? If so, what type of
control is it? In which event procedure are you executing this code?

 

[Guest]

In the Open event I have a form with dates which then calls the procedure
Calculate_ SalesCounter which is where this code is. I have it as an unbound
text box.

Does your report contain a control named 'CityCode'? If so, what type of
control is it? In which event procedure are you executing this code?

--
Brendan Reynolds
Access MVP

I'm trying to use recordsets in my report but I get an error
message--Run-time error '2448' on the following code at the Line
Me!CityCode
= rst!CityCode

Dim rst As Recordset
Dim dbs As Database
Dim strSQL As String
Dim strRate As String
Dim strRoute As String
Dim strDriver As String
Dim strlocation As String
Dim intCounter As Integer



Set dbs = CurrentDb
strSQL = "SELECT CityCode, RouteNumber, Driver, SalesCounter,
LocationCode, UnitType, OrderDate, UnitNumber, AmountCollected,
CounterNumber
FROM [Location ID] WHERE CityCode='" & [Forms]![Report Date Dialog]![City]
&
"' AND (OrderDate >=#" & [Forms]![Report Date Dialog]![BeginningDate] & "#
And OrderDate <=#" & [Forms]![Report Date Dialog]![EndingDate] & "# And
AmountCollected <> 0 And CounterNumber <> 0) "

Set rst = dbs.OpenRecordset(strSQL)

If Not rst.EOF Then
Do While Not rst.EOF
strlocation = rst!LocationCode
inCounter = 1
Do While rst!LocationCode = strlocation
Me![CityCode] = rst![CityCode]
Me!RouteNumber = rst!RouteNumber
Me!Driver = rst!Driver
Me!SalesCounter = rst!SalesCounter
Me!LocationCode = rst!LocationCode
Me!UnitType = rst!UnitType
Me!OrderDate = rst!OrderDate
Me!UnitNumber = rst!UnitNumber
Me!AmountCollected = rst!AmountCollected
Me!CounterNumber = rst!CounterNumber
intTotalCounter = intTotalCounter - rst!CounterNumber
rst.MoveNext
If rst.EOF Then
Exit Do
End If
Loop
Loop
End If
End Sub

 

[Brendan Reynolds]

I'm sorry, I'm afraid I'm unable to understand your description of the
problem.

--
Brendan Reynolds
Access MVP

In the Open event I have a form with dates which then calls the procedure
Calculate_ SalesCounter which is where this code is. I have it as an
unbound
text box.

Does your report contain a control named 'CityCode'? If so, what type of
control is it? In which event procedure are you executing this code?

--
Brendan Reynolds
Access MVP

I'm trying to use recordsets in my report but I get an error
message--Run-time error '2448' on the following code at the Line
Me!CityCode
= rst!CityCode

Dim rst As Recordset
Dim dbs As Database
Dim strSQL As String
Dim strRate As String
Dim strRoute As String
Dim strDriver As String
Dim strlocation As String
Dim intCounter As Integer



Set dbs = CurrentDb
strSQL = "SELECT CityCode, RouteNumber, Driver, SalesCounter,
LocationCode, UnitType, OrderDate, UnitNumber, AmountCollected,
CounterNumber
FROM [Location ID] WHERE CityCode='" & [Forms]![Report Date
Dialog]![City]
&
"' AND (OrderDate >=#" & [Forms]![Report Date Dialog]![BeginningDate] &
"#
And OrderDate <=#" & [Forms]![Report Date Dialog]![EndingDate] & "# And
AmountCollected <> 0 And CounterNumber <> 0) "

Set rst = dbs.OpenRecordset(strSQL)

If Not rst.EOF Then
Do While Not rst.EOF
strlocation = rst!LocationCode
inCounter = 1
Do While rst!LocationCode = strlocation
Me![CityCode] = rst![CityCode]
Me!RouteNumber = rst!RouteNumber
Me!Driver = rst!Driver
Me!SalesCounter = rst!SalesCounter
Me!LocationCode = rst!LocationCode
Me!UnitType = rst!UnitType
Me!OrderDate = rst!OrderDate
Me!UnitNumber = rst!UnitNumber
Me!AmountCollected = rst!AmountCollected
Me!CounterNumber = rst!CounterNumber
intTotalCounter = intTotalCounter - rst!CounterNumber
rst.MoveNext
If rst.EOF Then
Exit Do
End If
Loop
Loop
End If
End Sub

 

[Stefan Hoffmann]

hi,

I'm trying to use recordsets in my report but I get an error
message--Run-time error '2448' on the following code at the Line Me!CityCode
= rst!CityCode

You are probably using the wrong event.

Take a look at the OH. You can't use all report events to modify its
controls.


mfG
--> stefan <--