Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting Access Records to Excel (2003)

    I've prepared a subroutine exportDataToExcel to export selected records from Access to Excel but if, on the startup form, I select 2005 in the combo box and click the Export button, the code stops at the line
    rsTmp.Open sSQL, cnn, adOpenStatic, adLockReadOnly
    with the message
    <font color=red>No value specified for some required parameters</font color=red>
    If I hover the mouse over the code line in question, I see all the parameters have values so what's wrong?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting Access Records to Excel (2003)

    ADO doesn't know anything about Access forms, so you can't use [Forms]![mseledata3]![Anno] in your SQL. Use this instead:
    ...
    " WHERE Year([PortDez23].[Giorno]) = " & Me.Anno & _
    ...

    You will then get a type mismatch when calling GetRowsOK. I haven't investigated why.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Access Records to Excel (2003)

    Thank you Hans,
    I have imported the basExportFunctions module without any modifications from the attached mdb and in there
    <font color=red> If GetRowsOK(rsTmp, conMaxRecords, _
    varRecords) Then</font color=red>
    works without flaws. I just can't figure out which one of the three arguments of GetRowsOK causes a type mismatch, can you please help? <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Exporting Access Records to Excel (2003)

    Unless I missed something in my quick skim through, I can't see where varRecords gets assigned to anything.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Access Records to Excel (2003)

    Hi Rory!
    You know what? I made exactly the same consideration about varRecords being empty when the GetRowsOK function is called yet varRecords is empty too when the GetRowsOK function is called in the original mdb(Copia di ExportToExcel) and still the export procedure goes to completion without a hitch. I can see though once varRecords is passed to the GetRowsOK function, the variable varData that receives it gets initialised to rsTemp.GetRows(iNumRows).
    What do you think?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting Access Records to Excel (2003)

    In the definition of GetRowsOK, you MUST specify that rsTemp is an ADO recordset, otherwise Access gets confused between ADO and DAO:

    Function GetRowsOK(rsTemp As ADODB.Recordset, iNumRows As Integer, _
    varData As Variant) As Boolean

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Access Records to Excel (2003)

    Thank you Hans,
    The strange thing is that if you put a breakpoint at the line
    <font color=red>iNumRows = UBound(varRecords, 2)</font color=red>
    in the exportDataToExcel Sub, and hover with the mouse over the line, Data Tip comes up with
    <font color=448800>UBound(varRecords, 2) = 19</font color=448800>
    Where has that 19 been set?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting Access Records to Excel (2003)

    The call to GetRowsOK In the instruction

    If GetRowsOK(rsTmp, conMaxRecords, _
    varRecords) Then

    fills the varRecords variable with an array.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Access Records to Excel (2003)

    Does it fill it through
    varData = rsTemp.GetRows(iNumRows)
    in the GetRowsOK function?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting Access Records to Excel (2003)

    Yes. The argument varData of the GetRowsOK function is (by default) passed By Reference. You pass the variable varRecords to the function as varData argument. The varData argument is filled in the line

    varData = rsTemp.GetRows(iNumRows)

    and in fact this fills the varRecords variable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •