1) I'm using MS Office XP, VBA code in Access form.
2) I have 2 tables: A and B.

3) I run a [do while] loop to insert (insert into table....) 2000 records
to table A. Then I close my recordset (rs.close).

4) Then I create a 'transform' SQL like this:
lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
[InvDate]"
-- set RS=new adodb.recordset
--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic

Let's say the correct number of records is 60 records after running the
[transform] statement.

But when I place a Breakpoint at the line [lnRecordCount=RS.RecordCount],
the count is always less than 1, i.e. 60 -1 = 59 records.

I've done alot of testing on summary commands like transform, distinct,
group by. And I noticed that they (most of the time) return I record less.

However if I place a Msgbox between the two SQL statements like this, it
works:

###
a)
do while not rs.eof()
insert into tableA .....
(loop 2000 records)
loop
rs.close

'I need to add this message box.
'Only then I get the correct record count of 60.

Msgbox "Press any key to continue..."

lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
[InvDate]"
-- set RS=new adodb.recordset
--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
###

Is this a bug in VBA for Access? Is there a workaround when the user does
not have to see the Msgbox message?

Hi John,

Try this
RS.MoveLast
lnRecordCount = RS.RecordCount

On Tue, 21 Jun 2005 19:15:03 -0700, John Chee
<(E-Mail Removed)> wrote:

>1) I'm using MS Office XP, VBA code in Access form.
>2) I have 2 tables: A and B.
>
>3) I run a [do while] loop to insert (insert into table....) 2000 records
>to table A. Then I close my recordset (rs.close).
>
>4) Then I create a 'transform' SQL like this:
>lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
>group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
>[InvDate]"
>-- set RS=new adodb.recordset
>--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
>
>Let's say the correct number of records is 60 records after running the
>[transform] statement.
>
>But when I place a Breakpoint at the line [lnRecordCount=RS.RecordCount],
>the count is always less than 1, i.e. 60 -1 = 59 records.
>
>I've done alot of testing on summary commands like transform, distinct,
>group by. And I noticed that they (most of the time) return I record less.
>
>However if I place a Msgbox between the two SQL statements like this, it
>works:
>
>###
>a)
>do while not rs.eof()
> insert into tableA .....
> (loop 2000 records)
>loop
>rs.close
>
>'I need to add this message box.
>'Only then I get the correct record count of 60.
>
>Msgbox "Press any key to continue..."
>
>lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
>group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
>[InvDate]"
>-- set RS=new adodb.recordset
>--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
>###
>
>Is this a bug in VBA for Access? Is there a workaround when the user does
>not have to see the Msgbox message?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

> RS.MoveLast
> lnRecordCount = RS.RecordCount
This is just what I did. I placed a breakpoint at line [lnRecordCount =
RS.RecordCount]. I did [? lnRecordCount]. It's always 1 record less.

However if I place a Msgbox just before the SQL [lcSQL="transform
sum(NetSales)...], the lnRecordCount give the correct number of records.

Just to make sure I am right, I even create new tables and [insert... 2000
records] and run [select distinct .....]. The recordcount is always 1 less.
I discovered the problem will go away when I put a Msgbox!

I also upgrade my .mdb Access database to Windows 2003 version. The problem
(of having 1 record less when I run transform, select distinct) is still
there.


"John Nurick" wrote:

> Hi John,
>
> Try this
> RS.MoveLast
> lnRecordCount = RS.RecordCount
>
> On Tue, 21 Jun 2005 19:15:03 -0700, John Chee
> <(E-Mail Removed)> wrote:
>
> >1) I'm using MS Office XP, VBA code in Access form.
> >2) I have 2 tables: A and B.
> >
> >3) I run a [do while] loop to insert (insert into table....) 2000 records
> >to table A. Then I close my recordset (rs.close).
> >
> >4) Then I create a 'transform' SQL like this:
> >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
> >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
> >[InvDate]"
> >-- set RS=new adodb.recordset
> >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
> >
> >Let's say the correct number of records is 60 records after running the
> >[transform] statement.
> >
> >But when I place a Breakpoint at the line [lnRecordCount=RS.RecordCount],
> >the count is always less than 1, i.e. 60 -1 = 59 records.
> >
> >I've done alot of testing on summary commands like transform, distinct,
> >group by. And I noticed that they (most of the time) return I record less.
> >
> >However if I place a Msgbox between the two SQL statements like this, it
> >works:
> >
> >###
> >a)
> >do while not rs.eof()
> > insert into tableA .....
> > (loop 2000 records)
> >loop
> >rs.close
> >
> >'I need to add this message box.
> >'Only then I get the correct record count of 60.
> >
> >Msgbox "Press any key to continue..."
> >
> >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
> >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
> >[InvDate]"
> >-- set RS=new adodb.recordset
> >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
> >###
> >
> >Is this a bug in VBA for Access? Is there a workaround when the user does
> >not have to see the Msgbox message?
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

"...upgrade my .mdb Access database to Windows 2003 version..."
I mean upgrade my .mdb Access database to Office Access 2003 version.

"John Chee" wrote:

> > RS.MoveLast
> > lnRecordCount = RS.RecordCount
> This is just what I did. I placed a breakpoint at line [lnRecordCount =
> RS.RecordCount]. I did [? lnRecordCount]. It's always 1 record less.
>
> However if I place a Msgbox just before the SQL [lcSQL="transform
> sum(NetSales)...], the lnRecordCount give the correct number of records.
>
> Just to make sure I am right, I even create new tables and [insert... 2000
> records] and run [select distinct .....]. The recordcount is always 1 less.
> I discovered the problem will go away when I put a Msgbox!
>
> I also upgrade my .mdb Access database to Windows 2003 version. The problem
> (of having 1 record less when I run transform, select distinct) is still
> there.
>
>
> "John Nurick" wrote:
>
> > Hi John,
> >
> > Try this
> > RS.MoveLast
> > lnRecordCount = RS.RecordCount
> >
> > On Tue, 21 Jun 2005 19:15:03 -0700, John Chee
> > <(E-Mail Removed)> wrote:
> >
> > >1) I'm using MS Office XP, VBA code in Access form.
> > >2) I have 2 tables: A and B.
> > >
> > >3) I run a [do while] loop to insert (insert into table....) 2000 records
> > >to table A. Then I close my recordset (rs.close).
> > >
> > >4) Then I create a 'transform' SQL like this:
> > >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
> > >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
> > >[InvDate]"
> > >-- set RS=new adodb.recordset
> > >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
> > >
> > >Let's say the correct number of records is 60 records after running the
> > >[transform] statement.
> > >
> > >But when I place a Breakpoint at the line [lnRecordCount=RS.RecordCount],
> > >the count is always less than 1, i.e. 60 -1 = 59 records.
> > >
> > >I've done alot of testing on summary commands like transform, distinct,
> > >group by. And I noticed that they (most of the time) return I record less.
> > >
> > >However if I place a Msgbox between the two SQL statements like this, it
> > >works:
> > >
> > >###
> > >a)
> > >do while not rs.eof()
> > > insert into tableA .....
> > > (loop 2000 records)
> > >loop
> > >rs.close
> > >
> > >'I need to add this message box.
> > >'Only then I get the correct record count of 60.
> > >
> > >Msgbox "Press any key to continue..."
> > >
> > >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
> > >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
> > >[InvDate]"
> > >-- set RS=new adodb.recordset
> > >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
> > >###
> > >
> > >Is this a bug in VBA for Access? Is there a workaround when the user does
> > >not have to see the Msgbox message?
> >
> > --
> > John Nurick [Microsoft Access MVP]
> >
> > Please respond in the newgroup and not by email.
> >

This is the latest 2 test2 I've done.

==> Test 1
a) I create a new Test.mdb Access database.
b) I create one form and one button.
c) There are 4 tables in this Test.mdb database.
d) Then I copy the vba codes (only sufficient codes) from my original .mdb
to Test.mdb. I'm doing this to make sure that my database is not very large.
My original .mdb is about 350 Mb. The vba codes are the same listed below.

e) I notice that if place a Breakpoint (without adding the MsgBox message
and then press F5 to continue) just before the line [lcSQL="transform
sum(NetSales)...from CustTxnLines], the lnRecordCount give the correct number
of records.

==> Test 2
If I omitted step (e) and I did not insert the Msgbox message, I noticed the
lnRecordCount was always one record less.

I thought I should summarise my source codes.

a) Query 1 - Run a Group SQL statement on 2 tables. RaCustTxnLines table
contains about 7000 records.

b) Insert all records into tbNS306A by running a do while..loop. When all
records are inserted, I do a [rs.close].

c) Then I run 'transform' SQL statement on tbNS306A table.

d) I run 'insert' SQL statement to insert all records into tbNS306B.

Here, I'm sending extracts of my vba codes. The rest of the codes are of
not much importance. If it is required later, I don't mind sending the
complete codes.

Thanks, anyway.

@@@@@@@@@@@
a)
###
lcSQL = "SELECT Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3) AS
Dept, "
lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2) AS
Br, "
lcSQL = lcSQL & " Sum(RaCustTxnLines.revenue_amount) AS NetSales,
tbItemMs.Pcls, "
lcSQL = lcSQL & " left([LAST_UPDATE_DATE],6) AS InvDate, tbItemMs.DeptID"
lcSQL = lcSQL & " FROM RaCustTxnLines LEFT JOIN tbItemMs ON "
lcSQL = lcSQL & " (RaCustTxnLines.INTERFACE_LINE_ATTRIBUTE10 =
tbItemMs.DeptID) "
lcSQL = lcSQL & " AND (RaCustTxnLines.INVENTORY_ITEM_ID = tbItemMs.ItemID)"
lcSQL = lcSQL & " WHERE (((RaCustTxnLines.INTERFACE_LINE_CONTEXT) = '" &
lcORDERENTRY & "'))"
lcSQL = lcSQL & " GROUP BY
Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3),"
lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2),"
lcSQL = lcSQL & " tbItemMs.Pcls, left([LAST_UPDATE_DATE],6), tbItemMs.DeptID"
###
b)
lcSQL = "insert into " & lctbNS306A & " (Dept,Br, NetSales, Pcls,
InvDate, DeptID) values ('"
c)
###
lcSQL = "TRANSFORM Sum(NetSales) AS SumOfNetSales"
lcSQL = lcSQL & " SELECT DeptID, Dept, Pcls, Br"
lcSQL = lcSQL & " FROM " & lctbNS306A
lcSQL = lcSQL & " GROUP BY DeptID, Dept, Pcls, Br"
lcSQL = lcSQL & " ORDER BY Right([InvDate],2)"
lcSQL = lcSQL & " PIVOT Right([InvDate],2)"
###
d)
###
lcSQL = "insert into " & lctbNS306B & " (Dept, DeptID, Pcls, Br,"
lcSQL = lcSQL &
"MS01,MS02,MS03,MS04,MS05,MS06,MS07,MS08,MS09,MS10,MS11,MS12) VALUES ('"
lcSQL = lcSQL & lc2Dept & "','" & lc2DeptID & "','"
lcSQL = lcSQL & lc2Pcls & "','" & lc2Br & "',"

###
@@@@@@@@@@@@

"John Chee" wrote:

> "...upgrade my .mdb Access database to Windows 2003 version..."
> I mean upgrade my .mdb Access database to Office Access 2003 version.
>
> "John Chee" wrote:
>
> > > RS.MoveLast
> > > lnRecordCount = RS.RecordCount
> > This is just what I did. I placed a breakpoint at line [lnRecordCount =
> > RS.RecordCount]. I did [? lnRecordCount]. It's always 1 record less.
> >
> > However if I place a Msgbox just before the SQL [lcSQL="transform
> > sum(NetSales)...], the lnRecordCount give the correct number of records.
> >
> > Just to make sure I am right, I even create new tables and [insert... 2000
> > records] and run [select distinct .....]. The recordcount is always 1 less.
> > I discovered the problem will go away when I put a Msgbox!
> >
> > I also upgrade my .mdb Access database to Windows 2003 version. The problem
> > (of having 1 record less when I run transform, select distinct) is still
> > there.
> >
> >
> > "John Nurick" wrote:
> >
> > > Hi John,
> > >
> > > Try this
> > > RS.MoveLast
> > > lnRecordCount = RS.RecordCount
> > >
> > > On Tue, 21 Jun 2005 19:15:03 -0700, John Chee
> > > <(E-Mail Removed)> wrote:
> > >
> > > >1) I'm using MS Office XP, VBA code in Access form.
> > > >2) I have 2 tables: A and B.
> > > >
> > > >3) I run a [do while] loop to insert (insert into table....) 2000 records
> > > >to table A. Then I close my recordset (rs.close).
> > > >
> > > >4) Then I create a 'transform' SQL like this:
> > > >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
> > > >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
> > > >[InvDate]"
> > > >-- set RS=new adodb.recordset
> > > >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
> > > >
> > > >Let's say the correct number of records is 60 records after running the
> > > >[transform] statement.
> > > >
> > > >But when I place a Breakpoint at the line [lnRecordCount=RS.RecordCount],
> > > >the count is always less than 1, i.e. 60 -1 = 59 records.
> > > >
> > > >I've done alot of testing on summary commands like transform, distinct,
> > > >group by. And I noticed that they (most of the time) return I record less.
> > > >
> > > >However if I place a Msgbox between the two SQL statements like this, it
> > > >works:
> > > >
> > > >###
> > > >a)
> > > >do while not rs.eof()
> > > > insert into tableA .....
> > > > (loop 2000 records)
> > > >loop
> > > >rs.close
> > > >
> > > >'I need to add this message box.
> > > >'Only then I get the correct record count of 60.
> > > >
> > > >Msgbox "Press any key to continue..."
> > > >
> > > >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
> > > >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
> > > >[InvDate]"
> > > >-- set RS=new adodb.recordset
> > > >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
> > > >###
> > > >
> > > >Is this a bug in VBA for Access? Is there a workaround when the user does
> > > >not have to see the Msgbox message?
> > >
> > > --
> > > John Nurick [Microsoft Access MVP]
> > >
> > > Please respond in the newgroup and not by email.
> > >

John,
The code you posted in your last message doesn't include any reference
to RS, so it's impossible to understand the situation. In your previous
messages you don't actually say that you did what I suggested and put
RS.MoveLast
before your existing
lnRecordCount = RS.RecordCount

If you haven't done that, try it now. (If necessary, add RS.MoveFirst
after RS.MoveLast to make the first record current again.)

If calling MoveLast doesn't give you a correct record count, try
inserting the line
DoEvents
in the same place as the breakpoint that you mention.

On Wed, 22 Jun 2005 01:47:09 -0700, John Chee
<(E-Mail Removed)> wrote:

>This is the latest 2 test2 I've done.
>
>==> Test 1
>a) I create a new Test.mdb Access database.
>b) I create one form and one button.
>c) There are 4 tables in this Test.mdb database.
>d) Then I copy the vba codes (only sufficient codes) from my original .mdb
>to Test.mdb. I'm doing this to make sure that my database is not very large.
> My original .mdb is about 350 Mb. The vba codes are the same listed below.
>
>e) I notice that if place a Breakpoint (without adding the MsgBox message
>and then press F5 to continue) just before the line [lcSQL="transform
>sum(NetSales)...from CustTxnLines], the lnRecordCount give the correct number
>of records.
>
>==> Test 2
>If I omitted step (e) and I did not insert the Msgbox message, I noticed the
>lnRecordCount was always one record less.
>
>I thought I should summarise my source codes.
>
>a) Query 1 - Run a Group SQL statement on 2 tables. RaCustTxnLines table
>contains about 7000 records.
>
>b) Insert all records into tbNS306A by running a do while..loop. When all
>records are inserted, I do a [rs.close].
>
>c) Then I run 'transform' SQL statement on tbNS306A table.
>
>d) I run 'insert' SQL statement to insert all records into tbNS306B.
>
>Here, I'm sending extracts of my vba codes. The rest of the codes are of
>not much importance. If it is required later, I don't mind sending the
>complete codes.
>
>Thanks, anyway.
>
>@@@@@@@@@@@
>a)
>###
>lcSQL = "SELECT Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3) AS
>Dept, "
>lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2) AS
>Br, "
>lcSQL = lcSQL & " Sum(RaCustTxnLines.revenue_amount) AS NetSales,
>tbItemMs.Pcls, "
>lcSQL = lcSQL & " left([LAST_UPDATE_DATE],6) AS InvDate, tbItemMs.DeptID"
>lcSQL = lcSQL & " FROM RaCustTxnLines LEFT JOIN tbItemMs ON "
>lcSQL = lcSQL & " (RaCustTxnLines.INTERFACE_LINE_ATTRIBUTE10 =
>tbItemMs.DeptID) "
>lcSQL = lcSQL & " AND (RaCustTxnLines.INVENTORY_ITEM_ID = tbItemMs.ItemID)"
>lcSQL = lcSQL & " WHERE (((RaCustTxnLines.INTERFACE_LINE_CONTEXT) = '" &
>lcORDERENTRY & "'))"
>lcSQL = lcSQL & " GROUP BY
>Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3),"
>lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2),"
>lcSQL = lcSQL & " tbItemMs.Pcls, left([LAST_UPDATE_DATE],6), tbItemMs.DeptID"
>###
>b)
> lcSQL = "insert into " & lctbNS306A & " (Dept,Br, NetSales, Pcls,
>InvDate, DeptID) values ('"
>c)
>###
>lcSQL = "TRANSFORM Sum(NetSales) AS SumOfNetSales"
>lcSQL = lcSQL & " SELECT DeptID, Dept, Pcls, Br"
>lcSQL = lcSQL & " FROM " & lctbNS306A
>lcSQL = lcSQL & " GROUP BY DeptID, Dept, Pcls, Br"
>lcSQL = lcSQL & " ORDER BY Right([InvDate],2)"
>lcSQL = lcSQL & " PIVOT Right([InvDate],2)"
>###
>d)
>###
> lcSQL = "insert into " & lctbNS306B & " (Dept, DeptID, Pcls, Br,"
> lcSQL = lcSQL &
>"MS01,MS02,MS03,MS04,MS05,MS06,MS07,MS08,MS09,MS10,MS11,MS12) VALUES ('"
> lcSQL = lcSQL & lc2Dept & "','" & lc2DeptID & "','"
> lcSQL = lcSQL & lc2Pcls & "','" & lc2Br & "',"
>
>###
>@@@@@@@@@@@@
>
>"John Chee" wrote:
>
>> "...upgrade my .mdb Access database to Windows 2003 version..."
>> I mean upgrade my .mdb Access database to Office Access 2003 version.
>>
>> "John Chee" wrote:
>>
>> > > RS.MoveLast
>> > > lnRecordCount = RS.RecordCount
>> > This is just what I did. I placed a breakpoint at line [lnRecordCount =
>> > RS.RecordCount]. I did [? lnRecordCount]. It's always 1 record less.
>> >
>> > However if I place a Msgbox just before the SQL [lcSQL="transform
>> > sum(NetSales)...], the lnRecordCount give the correct number of records.
>> >
>> > Just to make sure I am right, I even create new tables and [insert... 2000
>> > records] and run [select distinct .....]. The recordcount is always 1 less.
>> > I discovered the problem will go away when I put a Msgbox!
>> >
>> > I also upgrade my .mdb Access database to Windows 2003 version. The problem
>> > (of having 1 record less when I run transform, select distinct) is still
>> > there.
>> >
>> >
>> > "John Nurick" wrote:
>> >
>> > > Hi John,
>> > >
>> > > Try this
>> > > RS.MoveLast
>> > > lnRecordCount = RS.RecordCount
>> > >
>> > > On Tue, 21 Jun 2005 19:15:03 -0700, John Chee
>> > > <(E-Mail Removed)> wrote:
>> > >
>> > > >1) I'm using MS Office XP, VBA code in Access form.
>> > > >2) I have 2 tables: A and B.
>> > > >
>> > > >3) I run a [do while] loop to insert (insert into table....) 2000 records
>> > > >to table A. Then I close my recordset (rs.close).
>> > > >
>> > > >4) Then I create a 'transform' SQL like this:
>> > > >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
>> > > >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
>> > > >[InvDate]"
>> > > >-- set RS=new adodb.recordset
>> > > >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
>> > > >
>> > > >Let's say the correct number of records is 60 records after running the
>> > > >[transform] statement.
>> > > >
>> > > >But when I place a Breakpoint at the line [lnRecordCount=RS.RecordCount],
>> > > >the count is always less than 1, i.e. 60 -1 = 59 records.
>> > > >
>> > > >I've done alot of testing on summary commands like transform, distinct,
>> > > >group by. And I noticed that they (most of the time) return I record less.
>> > > >
>> > > >However if I place a Msgbox between the two SQL statements like this, it
>> > > >works:
>> > > >
>> > > >###
>> > > >a)
>> > > >do while not rs.eof()
>> > > > insert into tableA .....
>> > > > (loop 2000 records)
>> > > >loop
>> > > >rs.close
>> > > >
>> > > >'I need to add this message box.
>> > > >'Only then I get the correct record count of 60.
>> > > >
>> > > >Msgbox "Press any key to continue..."
>> > > >
>> > > >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
>> > > >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
>> > > >[InvDate]"
>> > > >-- set RS=new adodb.recordset
>> > > >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
>> > > >###
>> > > >
>> > > >Is this a bug in VBA for Access? Is there a workaround when the user does
>> > > >not have to see the Msgbox message?
>> > >
>> > > --
>> > > John Nurick [Microsoft Access MVP]
>> > >
>> > > Please respond in the newgroup and not by email.
>> > >

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Thanks for the reply.

You are right. I removed the (test lines) lines [rs.movelast, lnRecordCount
= RS.RecordCount, rs.movefirst] when I extracted the vba source codes and
placed them in the post.

This time I'm sending the complete vba source. Pls see below.

I've done 2 tests:
'==> TEST 1
'If I place a breakpoint at the line [lcSQL = "TRANSFORM Sum(NetSales) AS
SumOfNetSales"] and then press F5 to continue,
'the lnRecordCount = 96 (correct recordcount) (at line [lnRecordCount =
RS.RecordCount].

'==> TEST 2
'If I DO NOT place a breakpoint at the line [lcSQL = "TRANSFORM
Sum(NetSales) AS SumOfNetSales"],
'the lnRecordCount = 95 (correct recordcount LESS 1 record) (at line
[lnRecordCount = RS.RecordCount].


@@@@@@@@@@@@
Private Sub ProcessOENetSales()
Dim lcSQL$
Dim lcORDERENTRY$
Dim lctbNS306A$, lctbNS306B$
Dim lcDept$, lcBr$, lcNetSales$, lcPcls$, lcInvDate$, lcDeptID$
Dim lnFldCount As Long, lcFldName$
Dim lc201$, lc202$, lc203$, lc204$, lc205$, lc206$, lc207$, lc208$, lc209$,
lc210$, lc211$, lc212$
Dim lc2Dept$, lc2DeptID$, lc2Pcls$, lc2Br$
'=======================
'This section is for testing purpose only. To be deleted later.
Dim lnRecordCount As Long
'=======================
gcMsgHdr = "Procedure: ProcessOENetSales"
lcORDERENTRY = "ORDER ENTRY"
lctbNS306A = "tbNS306A"
lctbNS306B = "tbNS306B"

'=================================================
'JCQNS10 query.
lcSQL = "SELECT Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3) AS
Dept, "
lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2) AS
Br, "
lcSQL = lcSQL & " Sum(RaCustTxnLines.revenue_amount) AS NetSales,
tbItemMs.Pcls, "
lcSQL = lcSQL & " left([LAST_UPDATE_DATE],6) AS InvDate, tbItemMs.DeptID"
lcSQL = lcSQL & " FROM RaCustTxnLines LEFT JOIN tbItemMs ON "
lcSQL = lcSQL & " (RaCustTxnLines.INTERFACE_LINE_ATTRIBUTE10 =
tbItemMs.DeptID) "
lcSQL = lcSQL & " AND (RaCustTxnLines.INVENTORY_ITEM_ID = tbItemMs.ItemID)"
lcSQL = lcSQL & " WHERE (((RaCustTxnLines.INTERFACE_LINE_CONTEXT) = '" &
lcORDERENTRY & "'))"
lcSQL = lcSQL & " GROUP BY
Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3),"
lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2),"
lcSQL = lcSQL & " tbItemMs.Pcls, left([LAST_UPDATE_DATE],6), tbItemMs.DeptID"
'---------------------
Set RS = New ADODB.Recordset

RS.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly

If RS.BOF Or RS.EOF Then
RS.Close
Beep
MsgBox "No record!"
Exit Sub
End If

Cn2.Execute "Delete * from " & lctbNS306A

RS.MoveFirst

Do While Not RS.EOF()
lcDept = ReplSglDblQte("" & RS("Dept"))
lcBr = ReplSglDblQte("" & RS("Br"))

If IsNumeric(RS("NetSales")) Then
lcNetSales = RS("NetSales")
Else
lcNetSales = "0"
End If

lcPcls = ReplSglDblQte("" & RS("Pcls"))

If Len(Trim(RS("InvDate"))) = 0 Then
lcInvDate = ""
Else
lcInvDate = RS("InvDate")
End If

lcDeptID = ReplSglDblQte("" & RS("DeptID"))

lcSQL = "insert into " & lctbNS306A & " (Dept,Br, NetSales, Pcls,
InvDate, DeptID) values ('"
lcSQL = lcSQL & lcDept & "','" & lcBr & "',"
lcSQL = lcSQL & lcNetSales & ",'" & lcPcls & "','"
lcSQL = lcSQL & lcInvDate & "','" & lcDeptID & "')"

Cn2.Execute lcSQL

i = i + 1
lblStatus.Caption = "No. of records appended (" & lctbNS306A & ") : " &
Str(j)
DoEvents

RS.MoveNext
Loop

RS.Close

'Beep
'For testing purpose, I'm commenting the Msgbox line below.
'MsgBox "Cross-tab tbNS308 table in progress. Press any key."
'==========================
'This section is for testing purpose only. To be deleted later.
'I have commented out the MsgBox line.
'For Test 1, I have added 2 breakpoints at these 2 lines:
'(a) lcSQL = "TRANSFORM Sum(NetSales) AS SumOfNetSales"
'(b) lnRecordCount = RS.RecordCount
'
'==> TEST 1
'If I place a breakpoint at the line [lcSQL = "TRANSFORM Sum(NetSales) AS
SumOfNetSales"] and then press F5 to continue,
'the lnRecordCount = 96 (correct recordcount) (at line [lnRecordCount =
RS.RecordCount].
'==> TEST 2
'If I DO NOT place a breakpoint at the line [lcSQL = "TRANSFORM
Sum(NetSales) AS SumOfNetSales"],
'the lnRecordCount = 95 (correct recordcount LESS 1 record) (at line
[lnRecordCount = RS.RecordCount].
'
'Looks like vba.ado prefers a little rest before continuing with its journey!
'-----------------

lcSQL = "TRANSFORM Sum(NetSales) AS SumOfNetSales"
lcSQL = lcSQL & " SELECT DeptID, Dept, Pcls, Br"
lcSQL = lcSQL & " FROM " & lctbNS306A
lcSQL = lcSQL & " GROUP BY DeptID, Dept, Pcls, Br"
lcSQL = lcSQL & " ORDER BY Right([InvDate],2)"
lcSQL = lcSQL & " PIVOT Right([InvDate],2)"
'------------------
Set RS = New ADODB.Recordset

RS.Open lcSQL, Conn2, adOpenKeyset, adLockReadOnly
'RS.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly
If RS.BOF Or RS.EOF Then
RS.Close
Beep
MsgBox "No record!"
Exit Sub
End If

'=============================
'This section is for testing purpose only. To be deleted later.
'I'm placing a Breakpoint at line [lnRecordCount = RS.RecordCount].
RS.MoveLast
lnRecordCount = RS.RecordCount

RS.MoveFirst
'=============================

Cn2.Execute "Delete * from " & lctbNS306B

lnFldCount = RS.Fields.Count
j = 0

RS.MoveFirst
Do While Not RS.EOF()
lc201 = "0"
lc202 = "0"
lc203 = "0"
lc204 = "0"
lc205 = "0"
lc206 = "0"
lc207 = "0"
lc208 = "0"
lc209 = "0"
lc210 = "0"
lc211 = "0"
lc212 = "0"

For i = 0 To lnFldCount - 1
lcFldName = RS.Fields(i).Name

Select Case lcFldName
Case "Dept"
lc2Dept = "" & RS.Fields(i).Value
Case "DeptID"
lc2DeptID = "" & RS.Fields(i).Value
Case "Pcls"
lc2Pcls = "" & RS.Fields(i).Value
Case "Br"
lc2Br = "" & RS.Fields(i).Value
Case "01"
If IsNull(RS.Fields(i).Value) Then
lc201 = "0"
Else
lc201 = RS.Fields(i).Value
End If
Case "02"
If IsNull(RS.Fields(i).Value) Then
lc202 = "0"
Else
lc202 = RS.Fields(i).Value
End If

Case "03"
If IsNull(RS.Fields(i).Value) Then
lc203 = "0"
Else
lc203 = RS.Fields(i).Value
End If

Case "04"
If IsNull(RS.Fields(i).Value) Then
lc204 = "0"
Else
lc204 = RS.Fields(i).Value
End If

Case "05"
If IsNull(RS.Fields(i).Value) Then
lc205 = "0"
Else
lc205 = RS.Fields(i).Value
End If

Case "06"
If IsNull(RS.Fields(i).Value) Then
lc206 = "0"
Else
lc206 = RS.Fields(i).Value
End If

Case "07"
If IsNull(RS.Fields(i).Value) Then
lc207 = "0"
Else
lc207 = RS.Fields(i).Value
End If

Case "08"
If IsNull(RS.Fields(i).Value) Then
lc208 = "0"
Else
lc208 = RS.Fields(i).Value
End If

Case "09"
If IsNull(RS.Fields(i).Value) Then
lc209 = "0"
Else
lc209 = RS.Fields(i).Value
End If

Case "10"
If IsNull(RS.Fields(i).Value) Then
lc210 = "0"
Else
lc210 = RS.Fields(i).Value
End If

Case "11"
If IsNull(RS.Fields(i).Value) Then
lc211 = "0"
Else
lc211 = RS.Fields(i).Value
End If

Case "12"
If IsNull(RS.Fields(i).Value) Then
lc212 = "0"
Else
lc212 = RS.Fields(i).Value
End If

End Select
Next

lcSQL = "insert into " & lctbNS306B & " (Dept, DeptID, Pcls, Br,"
lcSQL = lcSQL &
"MS01,MS02,MS03,MS04,MS05,MS06,MS07,MS08,MS09,MS10,MS11,MS12) VALUES ('"
lcSQL = lcSQL & lc2Dept & "','" & lc2DeptID & "','"
lcSQL = lcSQL & lc2Pcls & "','" & lc2Br & "',"
lcSQL = lcSQL & lc201 & "," & lc202 & "," & lc203 & ","
lcSQL = lcSQL & lc204 & "," & lc205 & "," & lc206 & ","
lcSQL = lcSQL & lc207 & "," & lc208 & "," & lc209 & ","
lcSQL = lcSQL & lc210 & "," & lc211 & "," & lc212 & ")"

Cn2.Execute lcSQL

j = j + 1
lblStatus.Caption = "No. of records appended (" & lctbNS306B & ") : " &
Str(j)
DoEvents

RS.MoveNext
Loop

RS.Close


End Sub
@@@@@@@@@@@@

"John Nurick" wrote:

> John,
> The code you posted in your last message doesn't include any reference
> to RS, so it's impossible to understand the situation. In your previous
> messages you don't actually say that you did what I suggested and put
> RS.MoveLast
> before your existing
> lnRecordCount = RS.RecordCount
>
> If you haven't done that, try it now. (If necessary, add RS.MoveFirst
> after RS.MoveLast to make the first record current again.)
>
> If calling MoveLast doesn't give you a correct record count, try
> inserting the line
> DoEvents
> in the same place as the breakpoint that you mention.
>
> On Wed, 22 Jun 2005 01:47:09 -0700, John Chee
> <(E-Mail Removed)> wrote:
>
> >This is the latest 2 test2 I've done.
> >
> >==> Test 1
> >a) I create a new Test.mdb Access database.
> >b) I create one form and one button.
> >c) There are 4 tables in this Test.mdb database.
> >d) Then I copy the vba codes (only sufficient codes) from my original .mdb
> >to Test.mdb. I'm doing this to make sure that my database is not very large.
> > My original .mdb is about 350 Mb. The vba codes are the same listed below.
> >
> >e) I notice that if place a Breakpoint (without adding the MsgBox message
> >and then press F5 to continue) just before the line [lcSQL="transform
> >sum(NetSales)...from CustTxnLines], the lnRecordCount give the correct number
> >of records.
> >
> >==> Test 2
> >If I omitted step (e) and I did not insert the Msgbox message, I noticed the
> >lnRecordCount was always one record less.
> >
> >I thought I should summarise my source codes.
> >
> >a) Query 1 - Run a Group SQL statement on 2 tables. RaCustTxnLines table
> >contains about 7000 records.
> >
> >b) Insert all records into tbNS306A by running a do while..loop. When all
> >records are inserted, I do a [rs.close].
> >
> >c) Then I run 'transform' SQL statement on tbNS306A table.
> >
> >d) I run 'insert' SQL statement to insert all records into tbNS306B.
> >
> >Here, I'm sending extracts of my vba codes. The rest of the codes are of
> >not much importance. If it is required later, I don't mind sending the
> >complete codes.
> >
> >Thanks, anyway.
> >
> >@@@@@@@@@@@
> >a)
> >###
> >lcSQL = "SELECT Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3) AS
> >Dept, "
> >lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2) AS
> >Br, "
> >lcSQL = lcSQL & " Sum(RaCustTxnLines.revenue_amount) AS NetSales,
> >tbItemMs.Pcls, "
> >lcSQL = lcSQL & " left([LAST_UPDATE_DATE],6) AS InvDate, tbItemMs.DeptID"
> >lcSQL = lcSQL & " FROM RaCustTxnLines LEFT JOIN tbItemMs ON "
> >lcSQL = lcSQL & " (RaCustTxnLines.INTERFACE_LINE_ATTRIBUTE10 =
> >tbItemMs.DeptID) "
> >lcSQL = lcSQL & " AND (RaCustTxnLines.INVENTORY_ITEM_ID = tbItemMs.ItemID)"
> >lcSQL = lcSQL & " WHERE (((RaCustTxnLines.INTERFACE_LINE_CONTEXT) = '" &
> >lcORDERENTRY & "'))"
> >lcSQL = lcSQL & " GROUP BY
> >Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3),"
> >lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2),"
> >lcSQL = lcSQL & " tbItemMs.Pcls, left([LAST_UPDATE_DATE],6), tbItemMs.DeptID"
> >###
> >b)
> > lcSQL = "insert into " & lctbNS306A & " (Dept,Br, NetSales, Pcls,
> >InvDate, DeptID) values ('"
> >c)
> >###
> >lcSQL = "TRANSFORM Sum(NetSales) AS SumOfNetSales"
> >lcSQL = lcSQL & " SELECT DeptID, Dept, Pcls, Br"
> >lcSQL = lcSQL & " FROM " & lctbNS306A
> >lcSQL = lcSQL & " GROUP BY DeptID, Dept, Pcls, Br"
> >lcSQL = lcSQL & " ORDER BY Right([InvDate],2)"
> >lcSQL = lcSQL & " PIVOT Right([InvDate],2)"
> >###
> >d)
> >###
> > lcSQL = "insert into " & lctbNS306B & " (Dept, DeptID, Pcls, Br,"
> > lcSQL = lcSQL &
> >"MS01,MS02,MS03,MS04,MS05,MS06,MS07,MS08,MS09,MS10,MS11,MS12) VALUES ('"
> > lcSQL = lcSQL & lc2Dept & "','" & lc2DeptID & "','"
> > lcSQL = lcSQL & lc2Pcls & "','" & lc2Br & "',"
> >
> >###
> >@@@@@@@@@@@@
> >
> >"John Chee" wrote:
> >
> >> "...upgrade my .mdb Access database to Windows 2003 version..."
> >> I mean upgrade my .mdb Access database to Office Access 2003 version.
> >>
> >> "John Chee" wrote:
> >>
> >> > > RS.MoveLast
> >> > > lnRecordCount = RS.RecordCount
> >> > This is just what I did. I placed a breakpoint at line [lnRecordCount =
> >> > RS.RecordCount]. I did [? lnRecordCount]. It's always 1 record less.
> >> >
> >> > However if I place a Msgbox just before the SQL [lcSQL="transform
> >> > sum(NetSales)...], the lnRecordCount give the correct number of records.
> >> >
> >> > Just to make sure I am right, I even create new tables and [insert... 2000
> >> > records] and run [select distinct .....]. The recordcount is always 1 less.
> >> > I discovered the problem will go away when I put a Msgbox!
> >> >
> >> > I also upgrade my .mdb Access database to Windows 2003 version. The problem
> >> > (of having 1 record less when I run transform, select distinct) is still
> >> > there.
> >> >
> >> >
> >> > "John Nurick" wrote:
> >> >
> >> > > Hi John,
> >> > >
> >> > > Try this
> >> > > RS.MoveLast
> >> > > lnRecordCount = RS.RecordCount
> >> > >
> >> > > On Tue, 21 Jun 2005 19:15:03 -0700, John Chee
> >> > > <(E-Mail Removed)> wrote:
> >> > >
> >> > > >1) I'm using MS Office XP, VBA code in Access form.
> >> > > >2) I have 2 tables: A and B.
> >> > > >
> >> > > >3) I run a [do while] loop to insert (insert into table....) 2000 records
> >> > > >to table A. Then I close my recordset (rs.close).
> >> > > >
> >> > > >4) Then I create a 'transform' SQL like this:
> >> > > >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
> >> > > >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
> >> > > >[InvDate]"
> >> > > >-- set RS=new adodb.recordset
> >> > > >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
> >> > > >
> >> > > >Let's say the correct number of records is 60 records after running the
> >> > > >[transform] statement.
> >> > > >
> >> > > >But when I place a Breakpoint at the line [lnRecordCount=RS.RecordCount],
> >> > > >the count is always less than 1, i.e. 60 -1 = 59 records.
> >> > > >
> >> > > >I've done alot of testing on summary commands like transform, distinct,
> >> > > >group by. And I noticed that they (most of the time) return I record less.
> >> > > >
> >> > > >However if I place a Msgbox between the two SQL statements like this, it
> >> > > >works:
> >> > > >
> >> > > >###
> >> > > >a)
> >> > > >do while not rs.eof()
> >> > > > insert into tableA .....
> >> > > > (loop 2000 records)
> >> > > >loop
> >> > > >rs.close
> >> > > >
> >> > > >'I need to add this message box.
> >> > > >'Only then I get the correct record count of 60.
> >> > > >
> >> > > >Msgbox "Press any key to continue..."
> >> > > >
> >> > > >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
> >> > > >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
> >> > > >[InvDate]"
> >> > > >-- set RS=new adodb.recordset
> >> > > >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
> >> > > >###
> >> > > >
> >> > > >Is this a bug in VBA for Access? Is there a workaround when the user does
> >> > > >not have to see the Msgbox message?
> >> > >
> >> > > --
> >> > > John Nurick [Microsoft Access MVP]
> >> > >
> >> > > Please respond in the newgroup and not by email.
> >> > >
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

I've also tested by:
a) adding [doevents] just before the line [lcSQL = "TRANSFORM Sum(NetSales)
AS SumOfNetSales"] .
b) There is no breakpoint at line [lcSQL = "TRANSFORM Sum(NetSales) AS
SumOfNetSales"] .

The recordcount is still 95 (i.e. one less than the correct recordcount of
96.).


"John Chee" wrote:

> Thanks for the reply.
>
> You are right. I removed the (test lines) lines [rs.movelast, lnRecordCount
> = RS.RecordCount, rs.movefirst] when I extracted the vba source codes and
> placed them in the post.
>
> This time I'm sending the complete vba source. Pls see below.
>
> I've done 2 tests:
> '==> TEST 1
> 'If I place a breakpoint at the line [lcSQL = "TRANSFORM Sum(NetSales) AS
> SumOfNetSales"] and then press F5 to continue,
> 'the lnRecordCount = 96 (correct recordcount) (at line [lnRecordCount =
> RS.RecordCount].
>
> '==> TEST 2
> 'If I DO NOT place a breakpoint at the line [lcSQL = "TRANSFORM
> Sum(NetSales) AS SumOfNetSales"],
> 'the lnRecordCount = 95 (correct recordcount LESS 1 record) (at line
> [lnRecordCount = RS.RecordCount].
>
>
> @@@@@@@@@@@@
> Private Sub ProcessOENetSales()
> Dim lcSQL$
> Dim lcORDERENTRY$
> Dim lctbNS306A$, lctbNS306B$
> Dim lcDept$, lcBr$, lcNetSales$, lcPcls$, lcInvDate$, lcDeptID$
> Dim lnFldCount As Long, lcFldName$
> Dim lc201$, lc202$, lc203$, lc204$, lc205$, lc206$, lc207$, lc208$, lc209$,
> lc210$, lc211$, lc212$
> Dim lc2Dept$, lc2DeptID$, lc2Pcls$, lc2Br$
> '=======================
> 'This section is for testing purpose only. To be deleted later.
> Dim lnRecordCount As Long
> '=======================
> gcMsgHdr = "Procedure: ProcessOENetSales"
> lcORDERENTRY = "ORDER ENTRY"
> lctbNS306A = "tbNS306A"
> lctbNS306B = "tbNS306B"
>
> '=================================================
> 'JCQNS10 query.
> lcSQL = "SELECT Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3) AS
> Dept, "
> lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2) AS
> Br, "
> lcSQL = lcSQL & " Sum(RaCustTxnLines.revenue_amount) AS NetSales,
> tbItemMs.Pcls, "
> lcSQL = lcSQL & " left([LAST_UPDATE_DATE],6) AS InvDate, tbItemMs.DeptID"
> lcSQL = lcSQL & " FROM RaCustTxnLines LEFT JOIN tbItemMs ON "
> lcSQL = lcSQL & " (RaCustTxnLines.INTERFACE_LINE_ATTRIBUTE10 =
> tbItemMs.DeptID) "
> lcSQL = lcSQL & " AND (RaCustTxnLines.INVENTORY_ITEM_ID = tbItemMs.ItemID)"
> lcSQL = lcSQL & " WHERE (((RaCustTxnLines.INTERFACE_LINE_CONTEXT) = '" &
> lcORDERENTRY & "'))"
> lcSQL = lcSQL & " GROUP BY
> Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3),"
> lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2),"
> lcSQL = lcSQL & " tbItemMs.Pcls, left([LAST_UPDATE_DATE],6), tbItemMs.DeptID"
> '---------------------
> Set RS = New ADODB.Recordset
>
> RS.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly
>
> If RS.BOF Or RS.EOF Then
> RS.Close
> Beep
> MsgBox "No record!"
> Exit Sub
> End If
>
> Cn2.Execute "Delete * from " & lctbNS306A
>
> RS.MoveFirst
>
> Do While Not RS.EOF()
> lcDept = ReplSglDblQte("" & RS("Dept"))
> lcBr = ReplSglDblQte("" & RS("Br"))
>
> If IsNumeric(RS("NetSales")) Then
> lcNetSales = RS("NetSales")
> Else
> lcNetSales = "0"
> End If
>
> lcPcls = ReplSglDblQte("" & RS("Pcls"))
>
> If Len(Trim(RS("InvDate"))) = 0 Then
> lcInvDate = ""
> Else
> lcInvDate = RS("InvDate")
> End If
>
> lcDeptID = ReplSglDblQte("" & RS("DeptID"))
>
> lcSQL = "insert into " & lctbNS306A & " (Dept,Br, NetSales, Pcls,
> InvDate, DeptID) values ('"
> lcSQL = lcSQL & lcDept & "','" & lcBr & "',"
> lcSQL = lcSQL & lcNetSales & ",'" & lcPcls & "','"
> lcSQL = lcSQL & lcInvDate & "','" & lcDeptID & "')"
>
> Cn2.Execute lcSQL
>
> i = i + 1
> lblStatus.Caption = "No. of records appended (" & lctbNS306A & ") : " &
> Str(j)
> DoEvents
>
> RS.MoveNext
> Loop
>
> RS.Close
>
> 'Beep
> 'For testing purpose, I'm commenting the Msgbox line below.
> 'MsgBox "Cross-tab tbNS308 table in progress. Press any key."
> '==========================
> 'This section is for testing purpose only. To be deleted later.
> 'I have commented out the MsgBox line.
> 'For Test 1, I have added 2 breakpoints at these 2 lines:
> '(a) lcSQL = "TRANSFORM Sum(NetSales) AS SumOfNetSales"
> '(b) lnRecordCount = RS.RecordCount
> '
> '==> TEST 1
> 'If I place a breakpoint at the line [lcSQL = "TRANSFORM Sum(NetSales) AS
> SumOfNetSales"] and then press F5 to continue,
> 'the lnRecordCount = 96 (correct recordcount) (at line [lnRecordCount =
> RS.RecordCount].
> '==> TEST 2
> 'If I DO NOT place a breakpoint at the line [lcSQL = "TRANSFORM
> Sum(NetSales) AS SumOfNetSales"],
> 'the lnRecordCount = 95 (correct recordcount LESS 1 record) (at line
> [lnRecordCount = RS.RecordCount].
> '
> 'Looks like vba.ado prefers a little rest before continuing with its journey!
> '-----------------
>
> lcSQL = "TRANSFORM Sum(NetSales) AS SumOfNetSales"
> lcSQL = lcSQL & " SELECT DeptID, Dept, Pcls, Br"
> lcSQL = lcSQL & " FROM " & lctbNS306A
> lcSQL = lcSQL & " GROUP BY DeptID, Dept, Pcls, Br"
> lcSQL = lcSQL & " ORDER BY Right([InvDate],2)"
> lcSQL = lcSQL & " PIVOT Right([InvDate],2)"
> '------------------
> Set RS = New ADODB.Recordset
>
> RS.Open lcSQL, Conn2, adOpenKeyset, adLockReadOnly
> 'RS.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly
> If RS.BOF Or RS.EOF Then
> RS.Close
> Beep
> MsgBox "No record!"
> Exit Sub
> End If
>
> '=============================
> 'This section is for testing purpose only. To be deleted later.
> 'I'm placing a Breakpoint at line [lnRecordCount = RS.RecordCount].
> RS.MoveLast
> lnRecordCount = RS.RecordCount
>
> RS.MoveFirst
> '=============================
>
> Cn2.Execute "Delete * from " & lctbNS306B
>
> lnFldCount = RS.Fields.Count
> j = 0
>
> RS.MoveFirst
> Do While Not RS.EOF()
> lc201 = "0"
> lc202 = "0"
> lc203 = "0"
> lc204 = "0"
> lc205 = "0"
> lc206 = "0"
> lc207 = "0"
> lc208 = "0"
> lc209 = "0"
> lc210 = "0"
> lc211 = "0"
> lc212 = "0"
>
> For i = 0 To lnFldCount - 1
> lcFldName = RS.Fields(i).Name
>
> Select Case lcFldName
> Case "Dept"
> lc2Dept = "" & RS.Fields(i).Value
> Case "DeptID"
> lc2DeptID = "" & RS.Fields(i).Value
> Case "Pcls"
> lc2Pcls = "" & RS.Fields(i).Value
> Case "Br"
> lc2Br = "" & RS.Fields(i).Value
> Case "01"
> If IsNull(RS.Fields(i).Value) Then
> lc201 = "0"
> Else
> lc201 = RS.Fields(i).Value
> End If
> Case "02"
> If IsNull(RS.Fields(i).Value) Then
> lc202 = "0"
> Else
> lc202 = RS.Fields(i).Value
> End If
>
> Case "03"
> If IsNull(RS.Fields(i).Value) Then
> lc203 = "0"
> Else
> lc203 = RS.Fields(i).Value
> End If
>
> Case "04"
> If IsNull(RS.Fields(i).Value) Then
> lc204 = "0"
> Else
> lc204 = RS.Fields(i).Value
> End If
>
> Case "05"
> If IsNull(RS.Fields(i).Value) Then
> lc205 = "0"
> Else
> lc205 = RS.Fields(i).Value
> End If
>
> Case "06"
> If IsNull(RS.Fields(i).Value) Then
> lc206 = "0"
> Else
> lc206 = RS.Fields(i).Value
> End If
>
> Case "07"
> If IsNull(RS.Fields(i).Value) Then
> lc207 = "0"
> Else
> lc207 = RS.Fields(i).Value
> End If
>
> Case "08"
> If IsNull(RS.Fields(i).Value) Then
> lc208 = "0"
> Else
> lc208 = RS.Fields(i).Value
> End If
>
> Case "09"
> If IsNull(RS.Fields(i).Value) Then
> lc209 = "0"
> Else
> lc209 = RS.Fields(i).Value
> End If
>
> Case "10"
> If IsNull(RS.Fields(i).Value) Then
> lc210 = "0"
> Else
> lc210 = RS.Fields(i).Value
> End If
>
> Case "11"
> If IsNull(RS.Fields(i).Value) Then
> lc211 = "0"
> Else
> lc211 = RS.Fields(i).Value
> End If
>
> Case "12"
> If IsNull(RS.Fields(i).Value) Then
> lc212 = "0"
> Else
> lc212 = RS.Fields(i).Value
> End If
>
> End Select
> Next
>
> lcSQL = "insert into " & lctbNS306B & " (Dept, DeptID, Pcls, Br,"
> lcSQL = lcSQL &
> "MS01,MS02,MS03,MS04,MS05,MS06,MS07,MS08,MS09,MS10,MS11,MS12) VALUES ('"
> lcSQL = lcSQL & lc2Dept & "','" & lc2DeptID & "','"
> lcSQL = lcSQL & lc2Pcls & "','" & lc2Br & "',"
> lcSQL = lcSQL & lc201 & "," & lc202 & "," & lc203 & ","
> lcSQL = lcSQL & lc204 & "," & lc205 & "," & lc206 & ","
> lcSQL = lcSQL & lc207 & "," & lc208 & "," & lc209 & ","
> lcSQL = lcSQL & lc210 & "," & lc211 & "," & lc212 & ")"
>
> Cn2.Execute lcSQL
>
> j = j + 1
> lblStatus.Caption = "No. of records appended (" & lctbNS306B & ") : " &
> Str(j)
> DoEvents
>

I don't know what's happening here but am asking some real experts to
take a look. Meanwhile, one thing I'd try is to declare a second
recordset variable

Dim rsTransform As New ADODB.Recordset

and use rsTransform in the problematic area of code instead of re-using
RS.

On Wed, 22 Jun 2005 18:14:02 -0700, John Chee
<(E-Mail Removed)> wrote:

>Thanks for the reply.
>
>You are right. I removed the (test lines) lines [rs.movelast, lnRecordCount
>= RS.RecordCount, rs.movefirst] when I extracted the vba source codes and
>placed them in the post.
>
>This time I'm sending the complete vba source. Pls see below.
>
>I've done 2 tests:
>'==> TEST 1
>'If I place a breakpoint at the line [lcSQL = "TRANSFORM Sum(NetSales) AS
>SumOfNetSales"] and then press F5 to continue,
>'the lnRecordCount = 96 (correct recordcount) (at line [lnRecordCount =
>RS.RecordCount].
>
>'==> TEST 2
>'If I DO NOT place a breakpoint at the line [lcSQL = "TRANSFORM
>Sum(NetSales) AS SumOfNetSales"],
>'the lnRecordCount = 95 (correct recordcount LESS 1 record) (at line
>[lnRecordCount = RS.RecordCount].
>
>
>@@@@@@@@@@@@
>Private Sub ProcessOENetSales()
>Dim lcSQL$
>Dim lcORDERENTRY$
>Dim lctbNS306A$, lctbNS306B$
>Dim lcDept$, lcBr$, lcNetSales$, lcPcls$, lcInvDate$, lcDeptID$
>Dim lnFldCount As Long, lcFldName$
>Dim lc201$, lc202$, lc203$, lc204$, lc205$, lc206$, lc207$, lc208$, lc209$,
>lc210$, lc211$, lc212$
>Dim lc2Dept$, lc2DeptID$, lc2Pcls$, lc2Br$
>'=======================
>'This section is for testing purpose only. To be deleted later.
>Dim lnRecordCount As Long
>'=======================
>gcMsgHdr = "Procedure: ProcessOENetSales"
>lcORDERENTRY = "ORDER ENTRY"
>lctbNS306A = "tbNS306A"
>lctbNS306B = "tbNS306B"
>
>'=================================================
>'JCQNS10 query.
>lcSQL = "SELECT Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3) AS
>Dept, "
>lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2) AS
>Br, "
>lcSQL = lcSQL & " Sum(RaCustTxnLines.revenue_amount) AS NetSales,
>tbItemMs.Pcls, "
>lcSQL = lcSQL & " left([LAST_UPDATE_DATE],6) AS InvDate, tbItemMs.DeptID"
>lcSQL = lcSQL & " FROM RaCustTxnLines LEFT JOIN tbItemMs ON "
>lcSQL = lcSQL & " (RaCustTxnLines.INTERFACE_LINE_ATTRIBUTE10 =
>tbItemMs.DeptID) "
>lcSQL = lcSQL & " AND (RaCustTxnLines.INVENTORY_ITEM_ID = tbItemMs.ItemID)"
>lcSQL = lcSQL & " WHERE (((RaCustTxnLines.INTERFACE_LINE_CONTEXT) = '" &
>lcORDERENTRY & "'))"
>lcSQL = lcSQL & " GROUP BY
>Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3),"
>lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2),"
>lcSQL = lcSQL & " tbItemMs.Pcls, left([LAST_UPDATE_DATE],6), tbItemMs.DeptID"
>'---------------------
>Set RS = New ADODB.Recordset
>
>RS.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly
>
>If RS.BOF Or RS.EOF Then
> RS.Close
> Beep
> MsgBox "No record!"
> Exit Sub
>End If
>
>Cn2.Execute "Delete * from " & lctbNS306A
>
>RS.MoveFirst
>
>Do While Not RS.EOF()
> lcDept = ReplSglDblQte("" & RS("Dept"))
> lcBr = ReplSglDblQte("" & RS("Br"))
>
> If IsNumeric(RS("NetSales")) Then
> lcNetSales = RS("NetSales")
> Else
> lcNetSales = "0"
> End If
>
> lcPcls = ReplSglDblQte("" & RS("Pcls"))
>
> If Len(Trim(RS("InvDate"))) = 0 Then
> lcInvDate = ""
> Else
> lcInvDate = RS("InvDate")
> End If
>
> lcDeptID = ReplSglDblQte("" & RS("DeptID"))
>
> lcSQL = "insert into " & lctbNS306A & " (Dept,Br, NetSales, Pcls,
>InvDate, DeptID) values ('"
> lcSQL = lcSQL & lcDept & "','" & lcBr & "',"
> lcSQL = lcSQL & lcNetSales & ",'" & lcPcls & "','"
> lcSQL = lcSQL & lcInvDate & "','" & lcDeptID & "')"
>
> Cn2.Execute lcSQL
>
> i = i + 1
> lblStatus.Caption = "No. of records appended (" & lctbNS306A & ") : " &
>Str(j)
> DoEvents
>
> RS.MoveNext
>Loop
>
>RS.Close
>
>'Beep
>'For testing purpose, I'm commenting the Msgbox line below.
>'MsgBox "Cross-tab tbNS308 table in progress. Press any key."
>'==========================
>'This section is for testing purpose only. To be deleted later.
>'I have commented out the MsgBox line.
>'For Test 1, I have added 2 breakpoints at these 2 lines:
>'(a) lcSQL = "TRANSFORM Sum(NetSales) AS SumOfNetSales"
>'(b) lnRecordCount = RS.RecordCount
>'
>'==> TEST 1
>'If I place a breakpoint at the line [lcSQL = "TRANSFORM Sum(NetSales) AS
>SumOfNetSales"] and then press F5 to continue,
>'the lnRecordCount = 96 (correct recordcount) (at line [lnRecordCount =
>RS.RecordCount].
>'==> TEST 2
>'If I DO NOT place a breakpoint at the line [lcSQL = "TRANSFORM
>Sum(NetSales) AS SumOfNetSales"],
>'the lnRecordCount = 95 (correct recordcount LESS 1 record) (at line
>[lnRecordCount = RS.RecordCount].
>'
>'Looks like vba.ado prefers a little rest before continuing with its journey!
>'-----------------
>
>lcSQL = "TRANSFORM Sum(NetSales) AS SumOfNetSales"
>lcSQL = lcSQL & " SELECT DeptID, Dept, Pcls, Br"
>lcSQL = lcSQL & " FROM " & lctbNS306A
>lcSQL = lcSQL & " GROUP BY DeptID, Dept, Pcls, Br"
>lcSQL = lcSQL & " ORDER BY Right([InvDate],2)"
>lcSQL = lcSQL & " PIVOT Right([InvDate],2)"
>'------------------
>Set RS = New ADODB.Recordset
>
>RS.Open lcSQL, Conn2, adOpenKeyset, adLockReadOnly
>'RS.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly
>If RS.BOF Or RS.EOF Then
> RS.Close
> Beep
> MsgBox "No record!"
> Exit Sub
>End If
>
>'=============================
>'This section is for testing purpose only. To be deleted later.
>'I'm placing a Breakpoint at line [lnRecordCount = RS.RecordCount].
>RS.MoveLast
>lnRecordCount = RS.RecordCount
>
>RS.MoveFirst
>'=============================
>
>Cn2.Execute "Delete * from " & lctbNS306B
>
>lnFldCount = RS.Fields.Count
>j = 0
>
>RS.MoveFirst
>Do While Not RS.EOF()
> lc201 = "0"
> lc202 = "0"
> lc203 = "0"
> lc204 = "0"
> lc205 = "0"
> lc206 = "0"
> lc207 = "0"
> lc208 = "0"
> lc209 = "0"
> lc210 = "0"
> lc211 = "0"
> lc212 = "0"
>
> For i = 0 To lnFldCount - 1
> lcFldName = RS.Fields(i).Name
>
> Select Case lcFldName
> Case "Dept"
> lc2Dept = "" & RS.Fields(i).Value
> Case "DeptID"
> lc2DeptID = "" & RS.Fields(i).Value
> Case "Pcls"
> lc2Pcls = "" & RS.Fields(i).Value
> Case "Br"
> lc2Br = "" & RS.Fields(i).Value
> Case "01"
> If IsNull(RS.Fields(i).Value) Then
> lc201 = "0"
> Else
> lc201 = RS.Fields(i).Value
> End If
> Case "02"
> If IsNull(RS.Fields(i).Value) Then
> lc202 = "0"
> Else
> lc202 = RS.Fields(i).Value
> End If
>
> Case "03"
> If IsNull(RS.Fields(i).Value) Then
> lc203 = "0"
> Else
> lc203 = RS.Fields(i).Value
> End If
>
> Case "04"
> If IsNull(RS.Fields(i).Value) Then
> lc204 = "0"
> Else
> lc204 = RS.Fields(i).Value
> End If
>
> Case "05"
> If IsNull(RS.Fields(i).Value) Then
> lc205 = "0"
> Else
> lc205 = RS.Fields(i).Value
> End If
>
> Case "06"
> If IsNull(RS.Fields(i).Value) Then
> lc206 = "0"
> Else
> lc206 = RS.Fields(i).Value
> End If
>
> Case "07"
> If IsNull(RS.Fields(i).Value) Then
> lc207 = "0"
> Else
> lc207 = RS.Fields(i).Value
> End If
>
> Case "08"
> If IsNull(RS.Fields(i).Value) Then
> lc208 = "0"
> Else
> lc208 = RS.Fields(i).Value
> End If
>
> Case "09"
> If IsNull(RS.Fields(i).Value) Then
> lc209 = "0"
> Else
> lc209 = RS.Fields(i).Value
> End If
>
> Case "10"
> If IsNull(RS.Fields(i).Value) Then
> lc210 = "0"
> Else
> lc210 = RS.Fields(i).Value
> End If
>
> Case "11"
> If IsNull(RS.Fields(i).Value) Then
> lc211 = "0"
> Else
> lc211 = RS.Fields(i).Value
> End If
>
> Case "12"
> If IsNull(RS.Fields(i).Value) Then
> lc212 = "0"
> Else
> lc212 = RS.Fields(i).Value
> End If
>
> End Select
> Next
>
> lcSQL = "insert into " & lctbNS306B & " (Dept, DeptID, Pcls, Br,"
> lcSQL = lcSQL &
>"MS01,MS02,MS03,MS04,MS05,MS06,MS07,MS08,MS09,MS10,MS11,MS12) VALUES ('"
> lcSQL = lcSQL & lc2Dept & "','" & lc2DeptID & "','"
> lcSQL = lcSQL & lc2Pcls & "','" & lc2Br & "',"
> lcSQL = lcSQL & lc201 & "," & lc202 & "," & lc203 & ","
> lcSQL = lcSQL & lc204 & "," & lc205 & "," & lc206 & ","
> lcSQL = lcSQL & lc207 & "," & lc208 & "," & lc209 & ","
> lcSQL = lcSQL & lc210 & "," & lc211 & "," & lc212 & ")"
>
> Cn2.Execute lcSQL
>
> j = j + 1
> lblStatus.Caption = "No. of records appended (" & lctbNS306B & ") : " &
>Str(j)
> DoEvents
>
> RS.MoveNext
>Loop
>
>RS.Close
>
>
>End Sub
>@@@@@@@@@@@@
>
>"John Nurick" wrote:
>
>> John,
>> The code you posted in your last message doesn't include any reference
>> to RS, so it's impossible to understand the situation. In your previous
>> messages you don't actually say that you did what I suggested and put
>> RS.MoveLast
>> before your existing
>> lnRecordCount = RS.RecordCount
>>
>> If you haven't done that, try it now. (If necessary, add RS.MoveFirst
>> after RS.MoveLast to make the first record current again.)
>>
>> If calling MoveLast doesn't give you a correct record count, try
>> inserting the line
>> DoEvents
>> in the same place as the breakpoint that you mention.
>>
>> On Wed, 22 Jun 2005 01:47:09 -0700, John Chee
>> <(E-Mail Removed)> wrote:
>>
>> >This is the latest 2 test2 I've done.
>> >
>> >==> Test 1
>> >a) I create a new Test.mdb Access database.
>> >b) I create one form and one button.
>> >c) There are 4 tables in this Test.mdb database.
>> >d) Then I copy the vba codes (only sufficient codes) from my original .mdb
>> >to Test.mdb. I'm doing this to make sure that my database is not very large.
>> > My original .mdb is about 350 Mb. The vba codes are the same listed below.
>> >
>> >e) I notice that if place a Breakpoint (without adding the MsgBox message
>> >and then press F5 to continue) just before the line [lcSQL="transform
>> >sum(NetSales)...from CustTxnLines], the lnRecordCount give the correct number
>> >of records.
>> >
>> >==> Test 2
>> >If I omitted step (e) and I did not insert the Msgbox message, I noticed the
>> >lnRecordCount was always one record less.
>> >
>> >I thought I should summarise my source codes.
>> >
>> >a) Query 1 - Run a Group SQL statement on 2 tables. RaCustTxnLines table
>> >contains about 7000 records.
>> >
>> >b) Insert all records into tbNS306A by running a do while..loop. When all
>> >records are inserted, I do a [rs.close].
>> >
>> >c) Then I run 'transform' SQL statement on tbNS306A table.
>> >
>> >d) I run 'insert' SQL statement to insert all records into tbNS306B.
>> >
>> >Here, I'm sending extracts of my vba codes. The rest of the codes are of
>> >not much importance. If it is required later, I don't mind sending the
>> >complete codes.
>> >
>> >Thanks, anyway.
>> >
>> >@@@@@@@@@@@
>> >a)
>> >###
>> >lcSQL = "SELECT Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3) AS
>> >Dept, "
>> >lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2) AS
>> >Br, "
>> >lcSQL = lcSQL & " Sum(RaCustTxnLines.revenue_amount) AS NetSales,
>> >tbItemMs.Pcls, "
>> >lcSQL = lcSQL & " left([LAST_UPDATE_DATE],6) AS InvDate, tbItemMs.DeptID"
>> >lcSQL = lcSQL & " FROM RaCustTxnLines LEFT JOIN tbItemMs ON "
>> >lcSQL = lcSQL & " (RaCustTxnLines.INTERFACE_LINE_ATTRIBUTE10 =
>> >tbItemMs.DeptID) "
>> >lcSQL = lcSQL & " AND (RaCustTxnLines.INVENTORY_ITEM_ID = tbItemMs.ItemID)"
>> >lcSQL = lcSQL & " WHERE (((RaCustTxnLines.INTERFACE_LINE_CONTEXT) = '" &
>> >lcORDERENTRY & "'))"
>> >lcSQL = lcSQL & " GROUP BY
>> >Left([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],3),"
>> >lcSQL = lcSQL & " Mid([RaCustTxnLines].[INTERFACE_LINE_ATTRIBUTE2],5,2),"
>> >lcSQL = lcSQL & " tbItemMs.Pcls, left([LAST_UPDATE_DATE],6), tbItemMs.DeptID"
>> >###
>> >b)
>> > lcSQL = "insert into " & lctbNS306A & " (Dept,Br, NetSales, Pcls,
>> >InvDate, DeptID) values ('"
>> >c)
>> >###
>> >lcSQL = "TRANSFORM Sum(NetSales) AS SumOfNetSales"
>> >lcSQL = lcSQL & " SELECT DeptID, Dept, Pcls, Br"
>> >lcSQL = lcSQL & " FROM " & lctbNS306A
>> >lcSQL = lcSQL & " GROUP BY DeptID, Dept, Pcls, Br"
>> >lcSQL = lcSQL & " ORDER BY Right([InvDate],2)"
>> >lcSQL = lcSQL & " PIVOT Right([InvDate],2)"
>> >###
>> >d)
>> >###
>> > lcSQL = "insert into " & lctbNS306B & " (Dept, DeptID, Pcls, Br,"
>> > lcSQL = lcSQL &
>> >"MS01,MS02,MS03,MS04,MS05,MS06,MS07,MS08,MS09,MS10,MS11,MS12) VALUES ('"
>> > lcSQL = lcSQL & lc2Dept & "','" & lc2DeptID & "','"
>> > lcSQL = lcSQL & lc2Pcls & "','" & lc2Br & "',"
>> >
>> >###
>> >@@@@@@@@@@@@
>> >
>> >"John Chee" wrote:
>> >
>> >> "...upgrade my .mdb Access database to Windows 2003 version..."
>> >> I mean upgrade my .mdb Access database to Office Access 2003 version.
>> >>
>> >> "John Chee" wrote:
>> >>
>> >> > > RS.MoveLast
>> >> > > lnRecordCount = RS.RecordCount
>> >> > This is just what I did. I placed a breakpoint at line [lnRecordCount =
>> >> > RS.RecordCount]. I did [? lnRecordCount]. It's always 1 record less.
>> >> >
>> >> > However if I place a Msgbox just before the SQL [lcSQL="transform
>> >> > sum(NetSales)...], the lnRecordCount give the correct number of records.
>> >> >
>> >> > Just to make sure I am right, I even create new tables and [insert... 2000
>> >> > records] and run [select distinct .....]. The recordcount is always 1 less.
>> >> > I discovered the problem will go away when I put a Msgbox!
>> >> >
>> >> > I also upgrade my .mdb Access database to Windows 2003 version. The problem
>> >> > (of having 1 record less when I run transform, select distinct) is still
>> >> > there.
>> >> >
>> >> >
>> >> > "John Nurick" wrote:
>> >> >
>> >> > > Hi John,
>> >> > >
>> >> > > Try this
>> >> > > RS.MoveLast
>> >> > > lnRecordCount = RS.RecordCount
>> >> > >
>> >> > > On Tue, 21 Jun 2005 19:15:03 -0700, John Chee
>> >> > > <(E-Mail Removed)> wrote:
>> >> > >
>> >> > > >1) I'm using MS Office XP, VBA code in Access form.
>> >> > > >2) I have 2 tables: A and B.
>> >> > > >
>> >> > > >3) I run a [do while] loop to insert (insert into table....) 2000 records
>> >> > > >to table A. Then I close my recordset (rs.close).
>> >> > > >
>> >> > > >4) Then I create a 'transform' SQL like this:
>> >> > > >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
>> >> > > >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
>> >> > > >[InvDate]"
>> >> > > >-- set RS=new adodb.recordset
>> >> > > >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
>> >> > > >
>> >> > > >Let's say the correct number of records is 60 records after running the
>> >> > > >[transform] statement.
>> >> > > >
>> >> > > >But when I place a Breakpoint at the line [lnRecordCount=RS.RecordCount],
>> >> > > >the count is always less than 1, i.e. 60 -1 = 59 records.
>> >> > > >
>> >> > > >I've done alot of testing on summary commands like transform, distinct,
>> >> > > >group by. And I noticed that they (most of the time) return I record less.
>> >> > > >
>> >> > > >However if I place a Msgbox between the two SQL statements like this, it
>> >> > > >works:
>> >> > > >
>> >> > > >###
>> >> > > >a)
>> >> > > >do while not rs.eof()
>> >> > > > insert into tableA .....
>> >> > > > (loop 2000 records)
>> >> > > >loop
>> >> > > >rs.close
>> >> > > >
>> >> > > >'I need to add this message box.
>> >> > > >'Only then I get the correct record count of 60.
>> >> > > >
>> >> > > >Msgbox "Press any key to continue..."
>> >> > > >
>> >> > > >lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
>> >> > > >group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
>> >> > > >[InvDate]"
>> >> > > >-- set RS=new adodb.recordset
>> >> > > >--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
>> >> > > >###
>> >> > > >
>> >> > > >Is this a bug in VBA for Access? Is there a workaround when the user does
>> >> > > >not have to see the Msgbox message?
>> >> > >
>> >> > > --
>> >> > > John Nurick [Microsoft Access MVP]
>> >> > >
>> >> > > Please respond in the newgroup and not by email.
>> >> > >
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

In my ProcessOENetSales() procedure, I replace all the RS with rsTransform.

It just works like magic. Now it's giving the correct recordcount of 96
without me putting the Msgbox command.

It looks like access.vba does not like RS; it prefers a longer name like
rsTransform.

Thanks a lot, John. You solve my problem.


"John Nurick" wrote:

> I don't know what's happening here but am asking some real experts to
> take a look. Meanwhile, one thing I'd try is to declare a second
> recordset variable
>
> Dim rsTransform As New ADODB.Recordset
>
> and use rsTransform in the problematic area of code instead of re-using
> RS.
>