Access transform x-tab SQL statement

J

John Chee

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?
 
J

John Nurick

Hi John,

Try this
RS.MoveLast
lnRecordCount = RS.RecordCount

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?
Click to expand...
 
J

John Chee

RS.MoveLast
lnRecordCount = RS.RecordCount
Click to expand...
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.


[QUOTE="John Nurick"]
Hi John,

Try this
RS.MoveLast
lnRecordCount = RS.RecordCount

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?
Click to expand...
[/QUOTE]
 
J

John Chee

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

John Chee said:
RS.MoveLast
lnRecordCount = RS.RecordCount
Click to expand...
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.


[QUOTE="John Nurick"]
Hi John,

Try this
RS.MoveLast
lnRecordCount = RS.RecordCount

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?
Click to expand...
Click to expand...
[/QUOTE]
 
J

John Chee

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 & "',"

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

[QUOTE="John Chee"]
"...upgrade my .mdb Access database to Windows 2003 version..."
I mean upgrade my .mdb Access database to Office Access 2003 version.

John Chee said:
RS.MoveLast
lnRecordCount = RS.RecordCount
Click to expand...
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.


[QUOTE="John Nurick"]
Hi John,

Try this
RS.MoveLast
lnRecordCount = RS.RecordCount

On Tue, 21 Jun 2005 19:15:03 -0700, John Chee

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?
Click to expand...
[/QUOTE][/QUOTE]
 
J

John Nurick

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.

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 & "',"

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

[QUOTE="John Chee"]
"...upgrade my .mdb Access database to Windows 2003 version..."
I mean upgrade my .mdb Access database to Office Access 2003 version.

John Chee said:
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.


:

Hi John,

Try this
RS.MoveLast
lnRecordCount = RS.RecordCount

On Tue, 21 Jun 2005 19:15:03 -0700, John Chee

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?
Click to expand...
Click to expand...
[/QUOTE]
 
J

John Chee

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 said:
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.

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 & "',"

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

[QUOTE="John Chee"]
"...upgrade my .mdb Access database to Windows 2003 version..."
I mean upgrade my .mdb Access database to Office Access 2003 version.

:

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.


:

Hi John,

Try this
RS.MoveLast
lnRecordCount = RS.RecordCount

On Tue, 21 Jun 2005 19:15:03 -0700, John Chee

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?
Click to expand...
Click to expand...
[/QUOTE]
 
J

John Chee

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 said:
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
Click to expand...
 
J

John Nurick

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.

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 said:
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.

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 & "',"

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

:

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

:

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.


:

Hi John,

Try this
RS.MoveLast
lnRecordCount = RS.RecordCount

On Tue, 21 Jun 2005 19:15:03 -0700, John Chee

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?
Click to expand...
Click to expand...
Click to expand...
 
J

John Chee

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.
 
J

John Chee

No. The problem is still there.

When I re-checked my codes, I mistakenly placed a Breakpoint BEFORE the line
[lcSQL = "TRANSFORM Sum(NetSales) AS >SumOfNetSales"]. That's why I was able
to get the correct recordcount. Probably I was too excited then.

Replacing RS with rsTransform does not work [Dim rsTransform As New
ADODB.Recordset].

The reason why I took a long time to update this post was because initially,
when I thought replacing 'rsTransform' with 'RS' would work after testing the
vba code in my Test.mdb database, I went to my production .mdb database and
replace all the 'RS' with 'rsTransform'. There are several hundred lines of
codes.

When I checked my report, the control figures did not tally. So I back
tracked to look at the vba codes in Test.mdb. The recordcount is still ONE
less.

I'm posting my latest codes here. Pls ignore the name 'rsTransform2'.
Because 'rsTransform' did not work, I was testing my luck on 'rsTransform2'.

@@@@@@@@@@@
1) General Declarations section:
Option Compare Database
Option Explicit
Dim Cn As ADODB.Connection
Dim Cn2 As ADODB.Connection
'Dim RS As New ADODB.Recordset
'==================
'To be deleted later.
'Dim rsTransform As New ADODB.Recordset
Dim rsTransform2 As New ADODB.Recordset
'==================

Dim Conn As String, Conn2 As String
Dim CPw1 As ADODB.Command
Dim QSQL As String
Dim gcMsgHdr$
Dim gcMsg$
Dim i As Long, j As Long
Dim fnYN As Long

2) ProcessOENetSale procedure():
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 rsTransform2 = New ADODB.Recordset

rsTransform2.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly

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

Cn2.Execute "Delete * from " & lctbNS306A

rsTransform2.MoveFirst

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

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

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

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

lcDeptID = ReplSglDblQte("" & rsTransform2("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

rsTransform2.MoveNext
Loop

rsTransform2.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.
'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 below [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 below [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!
'-----------------
'DoEvents

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
Set rsTransform2 = New ADODB.Recordset
'Dim rsTransform2 As New ADODB.Recordset

'RS.Open lcSQL, Conn2, adOpenKeyset, adLockReadOnly
rsTransform2.Open lcSQL, Conn2, adOpenKeyset, adLockReadOnly

'RS.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly
If rsTransform2.BOF Or rsTransform2.EOF Then
rsTransform2.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].
rsTransform2.MoveLast
lnRecordCount = rsTransform2.RecordCount

rsTransform2.MoveFirst
'=============================

Cn2.Execute "Delete * from " & lctbNS306B

lnFldCount = rsTransform2.Fields.Count
j = 0

rsTransform2.MoveFirst
Do While Not rsTransform2.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 = rsTransform2.Fields(i).Name

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

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

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

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

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

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

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

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

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

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

Case "12"
If IsNull(rsTransform2.Fields(i).Value) Then
lc212 = "0"
Else
lc212 = rsTransform2.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

rsTransform2.MoveNext
Loop

rsTransform2.Close


End Sub

@@@@@@@@@@@
 
J

John Nurick

I'm afraid I didn't make myself sufficiently clear in my previous
message. When I said
use rsTransform in the problematic area of code instead of
re-using RS
Click to expand...

I did not mean that you should replace RS with rsTransform throughout
(the length of the variable name is not a problem), but that you should
have two recordset variables and use one (RS) in the first part of the
procedure (the part you've headed "JCQNS10 query"), and the second
(rsTransform) in the second part of the procedure, where you handle the
TRANSFORM query.

In other words you would replace RS with rsTransform from this point on:

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 <---------replace RS with rsTransform
from here onwards

The reason I suggested this was to exclude the possibility of a timing
problem in between
RS.Close
and
Set RS = New ADODB.Recordset

One thing I can't quite work out from your previous posts is whether you
have ever done what I suggested and inserted
DoEvents
in the same place as you have successfully used a breakpoint, namely
immediately before
lcSQL = "TRANSFORM Sum(NetSales) AS SumOfNetSales"
In your code below, it's there but has been commented out.

In my last message I said I was asking some experts if they'd
encountered this problem. None has, so far: doing
RS.MoveLast
lnRecordCount = RS.RecordCount
normally produces an accurate record count. Probably there's something
else in your code that is causing the problem.

The fact that when you use a message box or a breakpoint you get the
correct record count makes me suspect it's a timing issue of some kind -
and normally these can be fixed by calling DoEvents at relevant places.




No. The problem is still there.

When I re-checked my codes, I mistakenly placed a Breakpoint BEFORE the line
[lcSQL = "TRANSFORM Sum(NetSales) AS >SumOfNetSales"]. That's why I was able
to get the correct recordcount. Probably I was too excited then.

Replacing RS with rsTransform does not work [Dim rsTransform As New
ADODB.Recordset].

The reason why I took a long time to update this post was because initially,
when I thought replacing 'rsTransform' with 'RS' would work after testing the
vba code in my Test.mdb database, I went to my production .mdb database and
replace all the 'RS' with 'rsTransform'. There are several hundred lines of
codes.

When I checked my report, the control figures did not tally. So I back
tracked to look at the vba codes in Test.mdb. The recordcount is still ONE
less.

I'm posting my latest codes here. Pls ignore the name 'rsTransform2'.
Because 'rsTransform' did not work, I was testing my luck on 'rsTransform2'.

@@@@@@@@@@@
1) General Declarations section:
Option Compare Database
Option Explicit
Dim Cn As ADODB.Connection
Dim Cn2 As ADODB.Connection
'Dim RS As New ADODB.Recordset
'==================
'To be deleted later.
'Dim rsTransform As New ADODB.Recordset
Dim rsTransform2 As New ADODB.Recordset
'==================

Dim Conn As String, Conn2 As String
Dim CPw1 As ADODB.Command
Dim QSQL As String
Dim gcMsgHdr$
Dim gcMsg$
Dim i As Long, j As Long
Dim fnYN As Long

2) ProcessOENetSale procedure():
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 rsTransform2 = New ADODB.Recordset

rsTransform2.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly

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

Cn2.Execute "Delete * from " & lctbNS306A

rsTransform2.MoveFirst

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

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

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

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

lcDeptID = ReplSglDblQte("" & rsTransform2("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

rsTransform2.MoveNext
Loop

rsTransform2.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.
'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 below [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 below [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!
'-----------------
'DoEvents

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
Set rsTransform2 = New ADODB.Recordset
'Dim rsTransform2 As New ADODB.Recordset

'RS.Open lcSQL, Conn2, adOpenKeyset, adLockReadOnly
rsTransform2.Open lcSQL, Conn2, adOpenKeyset, adLockReadOnly

'RS.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly
If rsTransform2.BOF Or rsTransform2.EOF Then
rsTransform2.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].
rsTransform2.MoveLast
lnRecordCount = rsTransform2.RecordCount

rsTransform2.MoveFirst
'=============================

Cn2.Execute "Delete * from " & lctbNS306B

lnFldCount = rsTransform2.Fields.Count
j = 0

rsTransform2.MoveFirst
Do While Not rsTransform2.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 = rsTransform2.Fields(i).Name

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

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

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

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

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

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

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

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

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

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

Case "12"
If IsNull(rsTransform2.Fields(i).Value) Then
lc212 = "0"
Else
lc212 = rsTransform2.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

rsTransform2.MoveNext
Loop

rsTransform2.Close


End Sub

@@@@@@@@@@@


John Chee said:
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.
Click to expand...
Click to expand...
 
J

John Chee

1) "..> I did not mean that you should replace RS with rsTransform throughout
(the length of the variable name is not a problem), but that you should
have two recordset variables and use one (RS)..."
Click to expand...
I did many tests on the vba codes. Yes. I did try what you suggested
regarding using two recorddsets (in one of my tests). However it didn't work.

2) "....what I suggested and inserted 'DoEvents' > in the same place .."
Yes. In one of my tests, I did place a 'DoEvents' just before line
[TRANSFORM Sum(NetSales) AS SumOfNetSales"] although I commented it out when
I extracted the vba code and post it here. It didn't work. However, I've
inserted the 'doevents' in my latest vba codes I posted here.

Suspecting there is a timing issue, I also tried something like this
###
dim lnTimes as long
lnTimes = 1

do while lnTimes <= 5000
lnTimes = lnTimes + 1
loop

It didn't work.
###

3) "...None has, so far: doing > RS.MoveLast; > lnRecordCount =
RS.RecordCount > normally produces an accurate record count..."
You are right. Normally, in my program, I do not need the line
[RS.MoveLast] to get the RecordCount.

4) "...Probably there's something > else in your code that is causing the
problem...."
For testing purpose, I've only extracted one procedure (ProcessOENetSales)
into the file called Test_050622.mdb. In this database file, there are (a) 4
tables, (b) one form with only one button on it and (c) there is only one
procedure (ProcessOENetSales).

In this Test_050622.mdb, I've also [Compact and Repair Database].

5) Pardon me if I am asking too much. I've temporarily placed the file
Test_050622.mdb at [ftp:\\219.94.76.28]. Login User ID: myguest; Password:
guestmy.

Thank you.

This is the latest vba codes I've tested.
@@@@@
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 rsTransform2 = New ADODB.Recordset

rsTransform2.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly

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

Cn2.Execute "Delete * from " & lctbNS306A

rsTransform2.MoveFirst

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

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

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

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

lcDeptID = ReplSglDblQte("" & rsTransform2("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

rsTransform2.MoveNext
Loop

rsTransform2.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.
'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 below [lcSQL = "TRANSFORM Sum(NetSales)
AS SumOfNetSales"] and then press F5 to continue,
'the lnRecordCount = 98 (correct recordcount) (at line [lnRecordCount =
RS.RecordCount].
'==> TEST 2
'If I DO NOT place a breakpoint at the line below [lcSQL = "TRANSFORM
Sum(NetSales) AS SumOfNetSales"],
'the lnRecordCount = 97 (correct recordcount LESS 1 record) (at line
[lnRecordCount = RS.RecordCount].
'
'Looks like vba.ado prefers a little rest before continuing with its journey!
'-----------------
'Placing a DoEvents does not work.
DoEvents
'-----------------

'-------------------------------------------------
'If you place a Breakpoint on this line [lcSQL = "TRANSFORM Sum(NetSales) AS
SumOfNetSales"], and then
'press F5 to continue, the lnRecordCount will give the correct number.

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
Set rsTransform = New ADODB.Recordset

'RS.Open lcSQL, Conn2, adOpenKeyset, adLockReadOnly
rsTransform.Open lcSQL, Conn2, adOpenKeyset, adLockReadOnly

'RS.Open lcSQL, Conn2, adOpenForwardOnly, adLockReadOnly
If rsTransform.BOF Or rsTransform.EOF Then
rsTransform.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].
rsTransform.MoveLast
lnRecordCount = rsTransform.RecordCount

rsTransform.MoveFirst
'=============================

Cn2.Execute "Delete * from " & lctbNS306B

lnFldCount = rsTransform.Fields.Count
j = 0

rsTransform.MoveFirst
Do While Not rsTransform.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 = rsTransform.Fields(i).Name

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

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

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

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

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

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

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

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

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

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

Case "12"
If IsNull(rsTransform.Fields(i).Value) Then
lc212 = "0"
Else
lc212 = rsTransform.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

rsTransform.MoveNext
Loop

rsTransform.Close


End Sub

@@@@@
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access transform x-tab SQL statement 1
Access transform x-tab SQL statement 1
Help writing SQL in VBA 3
record count of table while looping through all tables 2
help 5
count of records in a table in external database 2
SQL automatically sorting 6
Automation error when trying to open an ADO recordset 3

Top