Query or code problem ? (exporting recordset to excel)

Hi !

I have this code that exports a recordset to an excel workbook in an Access Database (Access 2002).
My procedure is exporting the whole thing and is dynamic (depending on fields selected and number of records).

The problem is that i use an inner join query and it only returns
me the first result, even though the same query in Access returns two results !!

I have the source here (it's a little easy code) :
http://pages.infinit.net/rouxjean/bd1.zip

Open the export form Export and click on the Excel button.
Compare the result of the export with the Query Requête 1.
I use the same SQL query for both...

Please help this is important !!!
rouxjeanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ampapaCommented:
You count was off on your for next routine:

' Exporter les données
rstSource.MoveFirst
For i = 0 To rstSource.RecordCount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst
0
appariCommented:
change

' Exporter les données
rstSource.MoveFirst
For i = 0 To rstSource.RecordCount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i

to

' Exporter les données
rstSource.MoveFirst
while not     rstSource.EOF
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
wend

0
rouxjeanAuthor Commented:
Thx for your replies.
Unfortunately, the solution you provide is not dynamic so i changed the 3 for rstSource.RecordCount.
When i used my sql string, it didn't work either, i had the same result as before.
Any idea ??
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ampapaCommented:
You count is dictated by the "i" variable. Replacing the "3" with rstsource.recordcount wil surely error.

What kind of error are you recieving?
0
ampapaCommented:
The output that you recieve when running the for next routine is exactly tied to the number of records in your table.

This is what I get when I run the export routine:
Nom      Prenom      Solde
Roux      Jean-Sébastien      $500.00
Milot      Valérie      $1,000.00
0
ampapaCommented:
Her is the full source:

Option Compare Database
Option Explicit

Private Sub Commande0_Click()
Dim rstSource As DAO.Recordset
Dim ExcelApp As Excel.Application
Dim ExcelWBook As Excel.Workbook
Dim ExcelWSheet As Excel.Worksheet
Dim i, j As Integer
Dim strSQL As String

strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.ID WHERE (([soldes]![IDClient]=[clients]![ID]));"

Set rstSource = CurrentDb.OpenRecordset(strSQL)

'Avoir ou créer l'objet Excel
On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
    Set ExcelApp = New Excel.Application
        Err.Clear
End If

' Créer le classeur Excel
Set ExcelWBook = ExcelApp.Workbooks.Add
Set ExcelWSheet = ExcelWBook.ActiveSheet

' Exporter le nom des champs
For j = 0 To rstSource.Fields.Count
    ExcelWSheet.Cells(2, j + 1) = rstSource.Fields(j).Name
Next j

' Exporter les données
rstSource.MoveFirst
For i = 0 To rstSource.RecordCount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value           <=================
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst

' Ajuster la largeur des colonnes automatiquement
For i = 1 To rstSource.Fields.Count
    ExcelWSheet.Columns(i).AutoFit
Next i

' Sélection de la première cellule pour déselectionner
ExcelWSheet.Cells(i + 1, j - 1).Select

'ActiveCell.FormulaR1C1 = "=SUM(B1:B2)"

' Afficher Excel pour modifications ultérieures
ExcelApp.Visible = True

'On flushe l'objet
Set ExcelApp = Nothing
Set ExcelWBook = Nothing
Set ExcelWSheet = Nothing
End Sub
0
rouxjeanAuthor Commented:
What i'm trying to say is what if there is a third record, the code won't work anymore !!! It will show only the first two !!!
0
rouxjeanAuthor Commented:
What is strange though is if my recordset is only the content of a table, it'll work...
0
ampapaCommented:
O.K here is the scoop. In the help file it states "The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only–type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object."

So because you were starting at the first record "rstSource.MoveFirst" your count is off. I just added a variable to get the count. Hope this helps.

ampapa,



Option Compare Database
Option Explicit

Private Sub Commande0_Click()
Dim rstSource As DAO.Recordset
Dim ExcelApp As Excel.Application
Dim ExcelWBook As Excel.Workbook
Dim ExcelWSheet As Excel.Worksheet
Dim i, j, rstcount As Integer                     <=====================
Dim strSQL As String

'strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.ID WHERE (([soldes]![IDClient]=[clients]![ID]));"
strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.ID WHERE ((([clients]![ID])=[soldes]![IDClient]));"
Set rstSource = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rstSource.MoveLast                                <=====================
rstcount = rstSource.RecordCount           <=====================

'Avoir ou créer l'objet Excel
On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
    Set ExcelApp = New Excel.Application
        Err.Clear
End If

' Créer le classeur Excel
Set ExcelWBook = ExcelApp.Workbooks.Add
Set ExcelWSheet = ExcelWBook.ActiveSheet

' Exporter le nom des champs
For j = 0 To rstSource.Fields.count
    ExcelWSheet.Cells(2, j + 1) = rstSource.Fields(j).Name
Next j

' Exporter les données
rstSource.MoveFirst
For i = 1 To rstcount 'rstSource.RecordCount               <=====================
    For j = 0 To rstSource.Fields.count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst

' Ajuster la largeur des colonnes automatiquement
For i = 1 To rstSource.Fields.count
    ExcelWSheet.Columns(i).AutoFit
Next i

' Sélection de la première cellule pour déselectionner
ExcelWSheet.Cells(i + 1, j - 1).Select

'ActiveCell.FormulaR1C1 = "=SUM(B1:B2)"

' Afficher Excel pour modifications ultérieures
ExcelApp.Visible = True

'On flushe l'objet
Set ExcelApp = Nothing
Set ExcelWBook = Nothing
Set ExcelWSheet = Nothing
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rouxjeanAuthor Commented:
Thanks :-)
I have this so far :
======================================================================
Option Compare Database
Option Explicit

Private Sub Commande0_Click()
Dim rstSource As DAO.Recordset
Dim ExcelApp As Excel.Application
Dim ExcelWBook As Excel.Workbook
Dim ExcelWSheet As Excel.Worksheet
Dim i, j, rstcount As Integer
Dim strSQL As String

strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.IDClient;"
Set rstSource = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rstSource.MoveLast
'Compter le nombre d'enregistrements dans le recordset
rstcount = rstSource.RecordCount

'Avoir ou créer l'objet Excel
On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
    Set ExcelApp = New Excel.Application
        Err.Clear
End If

' Créer le classeur Excel
Set ExcelWBook = ExcelApp.Workbooks.Add
Set ExcelWSheet = ExcelWBook.ActiveSheet

' Exporter le nom des champs
For j = 0 To rstSource.Fields.Count
    ExcelWSheet.Cells(2, j + 1) = rstSource.Fields(j).Name
Next j

' Exporter les données
rstSource.MoveFirst
For i = 1 To rstcount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst

' Ajuster la largeur des colonnes automatiquement
For i = 1 To rstSource.Fields.Count
    ExcelWSheet.Columns(i).AutoFit
Next i

' Sélection de la dernière cellule pour calculer
ExcelWSheet.Cells(rstcount + 4, rstSource.Fields.Count).Select

'Calculer le total
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"

' Afficher Excel pour modifications ultérieures
ExcelApp.Visible = True

'On flushe l'objet
Set ExcelApp = Nothing
Set ExcelWBook = Nothing
Set ExcelWSheet = Nothing
End Sub
======================================================================
The problem is when i click on the button to export once, close Excel without saving and click on the button again, Excel seems to freeze.

Any idea ?
0
ampapaCommented:
You should "quit" Excel before setting the object to nothing that may help.

ExcelApp.quit

You might try using  -  "CreateObject("Excel.Application")" instead of "GetObject(, "Excel.Application")"
0
rouxjeanAuthor Commented:
Using create object fixed the problem :-D
However, this line :
===========================================
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"
===========================================
executes only once even when i click on the button to export twice !!!
So i guess there is still problems with the object closing.
I have to close and re-load the database in order for it to work again.
I have no error at all, the focus is on the good cell but the sum is just ignored.

Any idea ?
0
ampapaCommented:
Replace:
'Calculer le total
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"

with:
ExcelWSheet.Cells(rstcount + 4, rstSource.Fields.Count).FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"
0
rouxjeanAuthor Commented:
Once again, that worked great :-)
Many thanks for your help !!!
0
ampapaCommented:
Your very welcome....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.