Page 1 of 4 123 ... LastLast
Results 1 to 15 of 49
  1. #1
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87

    Unanswered: Difficult to articulate.

    First and foremost, thanks to all who have helped me on this forum. The question I have today is pretty hard to articulate, so I have attached a .zip file with my database and HDC22.xls. Neither is completely programmed, however this is what I have so far. I will be referencing certain objects in my db as I try to articulate my problem, so I hope I haven't made too much of a mess in my db. The database was written by me, but not the Excel document. The Excel document is derived from an Excel document ERO.xls that my coworker is in the midst of programming. When my db exports information to his Excel document, it automatically saves as a new document. I'm attaching one of those new documents, with data already exported, so that you can see what it is that I'm attempting to do.

    If you look at HDC22, you may be able to put a little of the puzzle together. The information in the Import worksheet is the information exported from my db, and the ERO worksheet is all programmed by my coworker. There may be questions about that later, but he's trying to program it without using any macros/vba.

    If you look at my db, the exporting of all of that information is handled by function ExportToExcel, which is called from the OpenEROButton control on the Induct Gear form.

    Problem
    I would like to do something similar to this, only I'd like to export the information (after filling it out of course) from the Annotate form. The problem with that is that the Annotate form is unbound, because it's all unique information, so I didn't make a table for it. The ExportToExcel function makes a recordset based on DataObject. DataObject cannot be controls on a form.

    Eventually, what I'm trying to do is get the controls on the form Annotate to export to row 4, starting with cell A4, below the already exported "Induct Gear" information. After I get that information exported to row 4, my coworker can work his magic that he's been doing and pull the information over into the ERO.

    Is there a way for me to make a recordset of the data that the user inputs into those controls? and if so, can someone show me how (if possible) to modify ExportToExcel to use that as a recordset, rather than DataObject?

    Any help is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is one solution:

    1. Create a query (Qry_Export_Annotate) with the following code:
    Code:
    SELECT [Forms]![Annotate]![Annotate_ERONumber] AS ERONumber, 
    Forms!Annotate!Annotate_JulianDate AS JulianDate, 
    Forms!Annotate!Annotate_DescriptionOfWork AS DescriptionOfWork, 
    Forms!Annotate!Annotate_NewDefect AS NewDefect, 
    Forms!Annotate!Annotate_HoursBox AS HoursBox, 
    Forms!Annotate!Annotate_FirstInitial AS FirstInitial, 
    Forms!Annotate!Annotate_MiddleInital AS MiddleInital, 
    Forms!Annotate!Annotate_LastName AS LastName
    FROM MSysObjects
    WHERE (((MSysObjects.Id)=3));
    2. Call the ExportToExcel function with "Qry_Export_To_Excel" as argument.

    For this to work the form Annotate must be open (not in design view) AND THERE MUST BE NO ERROR IN ITS CLASS MODULE!

    I insist because in the database you posted, it was impossible to compile the code, due to several errors in the Annotate class module:
    Code:
    Private Sub Annotate_AnnotateButton_Click()
    On Error GoTo Err_Annotate_AnnotateButton_Click ' --> Label does not exist.
    ExportToExcel "SELECT " ' --> Argument not optional
    ...
    End Sub
    The WHERE clause is just there to limit the number of returned rows to 1.
    Have a nice day!

  3. #3
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    haha yes, those errors are there because I was tinkering with it, and reading up on SQL SELECT FROM statements, seeing if there was a way I could use the form as a recordset. Those aren't normally there. Thanks for reminding me though . I'll give it a shot.

  4. #4
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    I got an error that says:
    Code:
    Property let procedure not defined and property get procedure did not return an object.
    here's the new DB.
    Attached Files Attached Files

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Gizza clue - what do you click to get that?

  6. #6
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    should this:
    Code:
    "S:\1 MLG\CLR-17\Comm Co\Maint Plt\Electronic Forms\" & ERONumber
    be this:
    Code:
    "'S:\1 MLG\CLR-17\Comm Co\Maint Plt\Electronic Forms\' & ERONumber"
    ?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Several problems in your database. In short:

    1. For some reason I still have to understand, you cannot open the query Query_Export_Annotate in the code, though it work perfectly while opening it via the database window.

    2. The error you receive is because you improperly reference a control in the form Annotate. This:
    Code:
    ERONumber = Forms![Annotate]![Annotate_ERONumber]!Value
    should be:
    Code:
    ERONumber = Forms![Annotate]![Annotate_ERONumber].Value
    or simply:
    Code:
    ERONumber = Me.Annotate_ERONumber.Value
    "." and "!" are not equivalent.

    As the function ExportToExcel cannot open the query, let's replace this query by a table where you can write the values of the form controls before calling the function, this time passing the name of the table instead of the name of the query. Here is the modified code:
    Code:
    Private Sub Annotate_AnnotateButton_Click()
    On Error GoTo Err_Annotate_AnnotateButton_Click
        
        Dim ERONumber As String
        Dim strSQL As String
        
        strSQL = "DELETE FROM Tbl_Export_Annotate;"
        CurrentDb.Execute strSQL
        strSQL = "INSERT INTO Tbl_Export_Annotate ( ERONumber, JulianDate, DescriptionOfWork, NewDefect, HoursBox, FirstInitial, MiddleInital, LastName ) " & _
                 "VALUES ( '" & Me.Annotate_ERONumber & "', '" & _
                                Me.Annotate_JulianDate & "', '" & _
                                Me.Annotate_DescriptionOfWork & "', '" & _
                                Me.Annotate_NewDefect & "', '" & _
                                Me.Annotate_HoursBox & "', '" & _
                                Me.Annotate_FirstInitial & "', '" & _
                                Me.Annotate_MiddleInital & "', '" & _
                                Me.Annotate_LastName & "' );"
        CurrentDb.Execute strSQL
                 
    ' --> "." and "!" are not equivalent.
    '
    '    ERONumber = Forms![Annotate]![Annotate_ERONumber]!Value
        ERONumber = Me.Annotate_ERONumber.Value
    
    '    ExportToExcel "Query_Export_Annotate", "S:\1 MLG\CLR-17\Comm Co\Maint Plt\Electronic Forms\" & ERONumber, False, "", "ERO", "Annotate", "Import", True
    ' -->Replace with:
    '    ExportToExcel "Tbl_Export_Annotate", "S:\1 MLG\CLR-17\Comm Co\Maint Plt\Electronic Forms\" & ERONumber, False, "", "ERO", "Annotate", "Import", True
    ' --> Here for testing:
        ExportToExcel "Tbl_Export_Annotate", ERONumber, False, "", "ERO", "Annotate", "Import", True
        
    Exit_Annotate_AnnotateButton_Click:
        Exit Sub
    
    Err_Annotate_AnnotateButton_Click:
        MsgBox Err.Description
        Resume Exit_Annotate_AnnotateButton_Click
        
    End Sub
    I've tested it with a local path (did not checked with yours) and it works.

    See the corrected database (unsigned!) in the attached file for the structure of the table Tbl_Export_Annotate.
    Attached Files Attached Files
    Have a nice day!

  8. #8
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    Still no luck. Closer, but I'm getting this error now:
    Code:
    Application-defined or object-defined error
    Also, when stepping through the code, I notice that when it runs the ExportToExcel function, it gets to here:
    Code:
    If IncludeHeader = True Then
                For Each fld In rst.Fields
                    .Range(ExcelCodes(fld.OrdinalPosition + 1) & CStr(intRow)).FormulaR1C1 = fld.Name
                Next
                intRow = intRow + 1
            End If
    it doesn't add any of the field names for the header, like it does for the Induct actiontype, even though there's no if statement saying otherwise.

    It also skips over those last two lines:
    Code:
                Next
                intRow = intRow + 1
    So I'm led to believe that even if it DID add a header, it would only be one field name.

    It also does not export the data.

    Again, here's the updated database. I really only changed a couple of things. The file who's name corresponds with "Annotate_ERONumber" was not being opened, so I edited this:
    Code:
        ExportToExcel "Tbl_Export_Annotate", ERONumber, False, "", "ERO", "Annotate", "Import", True
    to say this:
    [code]
    ExportToExcel "Tbl_Export_Annotate", "S:\1 MLG\CLR-17\Comm Co\Maint Plt\Electronic Forms\" & ERONumber, False, "", "ERO", "Annotate", "Import", True
    [/code

    which I still think is wrong, but it opens the right file, so I guess it works.

    and I edited this from ExportToExcel:
    Code:
    If Not rst.EOF Then rst.MoveLast: rst.MoveFirst
                .ActiveWorkbook.SaveAs Replace(FileName, FileType & ".xls", FileNameSuffix & ".xls")
                    
                intRow = 1
    End If
    to say this:
    Code:
            If ActionType = "Induct" Then 'I added this, because it was trying to save over the file, but I only want it to SaveAs when inducting. Otherwise, just save.
                If Not rst.EOF Then rst.MoveLast: rst.MoveFirst
                .ActiveWorkbook.SaveAs Replace(FileName, FileType & ".xls", FileNameSuffix & ".xls")
                    
                intRow = 1
            Else
                .ActiveWorkbook.Save
            End If
    Those are the only two things I changed. So I don't know what's wrong.
    Attached Files Attached Files
    Last edited by th3spankst3r; 04-02-10 at 21:40.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As I wrote beforen there are several problems in your database.

    The error:
    Application-defined or object-defined error
    is because you handle the variable intRow in a manner that's inconsistent. intRow holds the current line number in the Excel sheet. If you do not initialise it, intRow = 0 and you then try to write something in line zero (in an Excel sheet line numbering begins at one). In my original code, intRow was unconditionally initialised BEFORE performing the test for IncludeHeader:
    Code:
            intRow = 1
            If IncludeHeader = True Then
                For Each fld In rst.Fields
                    Select Case fld.OrdinalPosition
    In your code, intRow is initialised ONLY IF ActionType = "Induct":
    Code:
            If ActionType = "Induct" Then 'I added this, because it was trying to save over the file, but I only want it to save as when inducting. Otherwise, just save.
                If Not rst.EOF Then rst.MoveLast: rst.MoveFirst
                .ActiveWorkbook.SaveAs Replace(FileName, FileType & ".xls", FileNameSuffix & ".xls")
                    
                intRow = 1
            Else
                .ActiveWorkbook.Save
            End If
    In other cases (ActionType <> "Induct"), intRow keeps its default value (zero), which means that the function tries to write something in line zero (which does not exist) of an Excel sheet:
    Code:
     .Range(ExcelCodes(fld.OrdinalPosition + 1) & CStr(intRow)).FormulaR1C1 = fld.Name ' intRow = 0 --> .Range("A0").FormulaR1C1 = fld.Name
    When you change the logic flow of a function you must be sure that you understand the possible side-effects of those changes.
    Have a nice day!

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Sinndho View Post
    in an Excel sheet line numbering begins at one
    This is correct, and applies to all collections in Excel. In Access (and most other applications and languages) the first item in a collection is at position 0. You need to keep this in mind during all your Excel programming.

  11. #11
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    oh. thank you! I wasn't too sure what introw was used for. Sorry i'm so new to this. I fixed it to declare what introw is before the if statement, and now I'm getting a logical error rather than a runtime error.

  12. #12
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    I stepped through the code and it just stops at this line:
    Code:
            .Range(ExportTo).CopyFromRecordset rst
    I'm using ExportTo, rather than a Cell name, so that I can determine the difference between Inducting gear and Editing EROs, and eventually Closing EROs. I want all of these to export information, but I want it to export it into different rows. I dim'd ExportTo as a string and defined it as A4 if ActionType is Annotate, as opposed to the ExportTo of the Induct ActionType that is A2. This should put the newly exported information 2 lines below the old exported information. I'm probably missing quotes, but since it's a variable, rather than an actual string, I took the quotes out.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    When it fails, open the immediate window and type:
    Code:
    ?ExportTo
    and hit enter. Copy and paste the exact result here.
    Also, what is the error?

  14. #14
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    It's a logical error, so it doesn't tell me anything. It appears to run through the command, but doesn't do what I want it to. No error message or anything. When I use the immediate window like you told me, to find the value of ExportTo, when the function barely starts, the value is 0, but after that, it says nothing.
    Code:
    ?ExportTo
    That would explain why it is not exporting what I want, but doesn't make sense, because I have ExportTo defined with a Select Case... End select statement.

  15. #15
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    Not only that, but when I mouse over ExportTo after it has been defined, it correctly gives me the value as "A4". Maybe because it's dim'd as a string. The range function that uses it may not be able to accept a string.

Posting Permissions

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