How do I use recordsets in a report? JC 11.09.2006 21:58:02 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

Re: How do I use recordsets in a report? "Brendan Reynolds" <brenreyn[ at ]discussions.microsoft.com> 12.09.2006 10:09:13 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 "JC" <JC[ at ]discussions.microsoft.com> wrote in message news:70A1DCA4-6798-4979-9C7D-9F8066A41F11[ at ]microsoft.com... [Quoted Text] > 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

Re: How do I use recordsets in a report? JC 13.09.2006 19:34:01 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. "Brendan Reynolds" wrote: [Quoted Text] > 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 > > "JC" <JC[ at ]discussions.microsoft.com> wrote in message > news:70A1DCA4-6798-4979-9C7D-9F8066A41F11[ at ]microsoft.com... > > 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 > > >

Re: How do I use recordsets in a report? "Brendan Reynolds" <brenreyn[ at ]discussions.microsoft.com> 14.09.2006 09:53:18 I'm sorry, I'm afraid I'm unable to understand your description of the problem. -- Brendan Reynolds Access MVP "JC" <JC[ at ]discussions.microsoft.com> wrote in message news:41F3D037-295C-4D0A-8D0A-6426B4FB014D[ at ]microsoft.com... [Quoted Text] > 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. > > "Brendan Reynolds" wrote: > >> 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 >> >> "JC" <JC[ at ]discussions.microsoft.com> wrote in message >> news:70A1DCA4-6798-4979-9C7D-9F8066A41F11[ at ]microsoft.com... >> > 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 >> >> >>

Re: How do I use recordsets in a report? Stefan Hoffmann <stefan.hoffmann[ at ]explido.de> 14.09.2006 10:12:37 hi, JC wrote: [Quoted Text] > 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 <--