Results 1 to 7 of 7
  1. #1
    bigfish311 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    4

    Object doesn't support this property method error

    Hello everyone, I have an old database in my group that has been handed down. It was originally created with 2003 and I'm getting an error "Object doesn't support this property method" when I select a button to search for a csv or xls file to import.

    Does anyone know the solution or have any suggestions? I'm okay for a business person in vba but definitely not at the super user level.

    If Me!XLSImport Then
    SFileName = OpenFile.("Find Cost Detail Excel Spreadsheet", "xls")
    Else
    ' MsgBox "Only Excel files are importing at this time."


    ' Exit Sub
    SFileName = BrowseFilename("Find Comma Seperated Value file", "csv")
    End If


    Thanks,

    Lou

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Which line of code does it fail on? And I think you will need to provide more code, and possibly including the BrowseFileName code.

  3. #3
    bigfish311 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    4
    It fails on the "BrowseFileName" line.




    Below is all the code.



    Private Sub ImportBtn_Click()
    Dim SFileName As String
    Dim iSheetType As Integer, i As Integer
    Dim Message, Title, ParentCC, s As String
    Dim rs1 As Recordset
    Dim db As Database
    Set db = CurrentDb()

    ' Changed "BrowseFilename" to "OpenFile"
    ' first have the user select a xls file
    '
    If Me!XLSImport Then
    SFileName = BrowseFilename("Find Cost Detail Excel Spreadsheet", "xls")
    Else
    ' MsgBox "Only Excel files are importing at this time."
    ' Exit Sub
    SFileName = BrowseFilename("Find Comma Seperated Value file", "csv")
    End If

    If SFileName <> "" Then
    ' delete the records out of the cost import table
    DoCmd.RunSQL "DELETE * FROM [CostImport];"
    ' drop the tempci table
    DoCmd.RunSQL "DELETE * FROM [tempci];"
    'DoCmd.DeleteObject acTable, "TempCI"
    ' import records into this table
    If Me!XLSImport Then
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TempCI", SFileName, True
    Else
    DoCmd.TransferText acImportDelim, "CSVSpec2", "TempCI", SFileName, True
    End If
    ' DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TempCI", SFileName, True
    ' move records into the cost import, this is due to field converstions that may be needed
    DoCmd.OpenQuery "qApptoCostImport"


    'Import CSA Labor and Expenses.
    '
    ' If Me!XLSImport Then
    ' DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "CostImport", SFileName, True
    'DoCmd.TransferSpreadsheet acImport, iSheetType, "CostImport", SFileName, True, "A1:AG10000"
    ' Else ' it is a csv
    ' DoCmd.TransferText acImportDelim, , "CostImport", SFileName, True
    ' End If

    ' DoCmd.TransferSpreadsheet acImport, iSheetType, "CostImport", SFileName, True, "A1:AG10000"

    ' ask for the company code, if not filled in
    Set rs1 = db.OpenRecordset("select * from CostImport where company is null")
    If Not rs1.EOF Then

    Message = "Enter a Company Code (up to 10 chars)" ' Set prompt.
    Title = " Company Code Input" ' Set title.

    Do While Trim([ParentCC] & " ") = ""
    ' Display dialog box
    ParentCC = InputBox(Message, Title)
    If ParentCC = "" Then ' cancel the import
    Exit Sub
    End If

    Loop

    ' update the CostImport file with the company code

    s = "UPDATE CostImport SET CostImport.company = '" & ParentCC & "' WHERE (((CostImport.COMPANY) Is Null));"
    If rs1.RecordCount > 0 Then
    DoCmd.RunSQL s
    End If
    rs1.close
    End If
    ' add any new companys to the rateovrd table
    DoCmd.OpenQuery "qAppendCompanyRateOvrd"
    ' get the list of odc and check it against the master list, add new ones
    DoCmd.OpenQuery "qAppendNewOdc2"
    ' get the list of employees and check it against the master list, add new ones
    DoCmd.OpenQuery "qAppendNewLabor2"
    ' get the list of projects and check it against the master list, add new ones
    DoCmd.OpenQuery "qAppendNewWBS3"
    ' get the list of companies and check against the master list (company table) and add new ones
    DoCmd.OpenQuery "qAppendNewCompany2"
    ' delete the records in the CostDetail table for this company or companies

    Set rs1 = db.OpenRecordset("SELECT DISTINCT COMPANY FROM CostImport;")
    rs1.MoveFirst
    Do While Not rs1.EOF
    ParentCC = rs1!Company
    s = "DELETE * from CostDetail "
    s = s + " WHERE (((CostDetail.DeleteThisRecord)<>0) AND ((CostDetail.COMPANY)='" & ParentCC & "'));"
    ' first delete all
    DoCmd.RunSQL s
    rs1.MoveNext
    Loop

    ' now append this data to the costdetail table !
    DoCmd.OpenQuery "qAppendImportCosts"


    MsgBox "Import from Excel completed.", vbInformation, "Import Completed!"
    Else
    MsgBox "No Excel file to import.", vbInformation, "Import Error!"
    End If
    End Sub

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    So, it likely has to do with the BrowseFileName function which is in your database somewhere. Can you post that code?

  5. #5
    bigfish311 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    4
    I think this is it. Not sure if it is buried somewhere else.

    Public Function BrowseFilename(DialogTitle As String, atype As String) As String
    Dim ctl As Control
    Dim s As String, sDirName As String, SFileName As String, ss As String, sVer As String
    Dim iSheetType As Integer, i As Integer
    Dim v As Variant
    On Error GoTo Err_cmdIMPORT
    ' xxx
    'MsgBox "in Utilities - Browsefilename first line"
    'Prepare the spreadsheet file filters
    If atype = "xls" Then
    s = "All Files (*.)|*.*|Excel 3.0 (*.xls)|*.xls|Excel 4.0 (*.xls)|*.xls|Excel 97 (*.xls;*.xlw)|*.xls;*.xlw"
    s = s & "|Excel 5.0 and 7.0 (*.xls;*.xlw)|*.xls;*.xlw|Lotus 1.0 (*.wk1)|*.wk1|Lotus 3.0 (*.wk3)|*.wk3|Lotus 4.0 (*.wk4)|*.wk4"
    Else
    s = "*.csv"
    End If

    'Initialize the Common Dialog control.
    ' xxx
    ' MsgBox "in utilities - Browsefilename before opening control"

    Set ctl = Forms![MAIN MENU]!ctlFileOpen

    ctl.DialogTitle = DialogTitle
    ctl.DefaultExt = "xls"
    ctl.Filter = s
    ctl.FilterIndex = 4
    ctl.Flags = cdlOFNAllowMultiselect Or cdlOFNExplorer
    ctl.CancelError = True
    ctl.ShowOpen

    s = ctl.filename
    sDirName = GetDirName(s)
    SFileName = GetNextFileName(s, True)

    SKIP:
    If SFileName <> "" Then
    BrowseFilename = sDirName & "\" & SFileName
    Else
    BrowseFilename = ""
    End If

    Fin_cmdIMPORT:
    Exit Function

    Err_cmdIMPORT:
    Select Case Err.Number
    Case ERR_SPREADSHEETTYPEXL
    ss = "Do you want the " & SFileName & " to be processed as Excel 5.0 or 7.0 file ?" & Chr(10) & Chr(13)
    ss = ss & "If you choose No the file will be skipped."
    i = MsgBox(ss, vbYesNo + vbInformation, "Import from Spreadsheet")
    If i = vbYes Then
    iSheetType = 5
    Resume Next
    Else
    Resume SKIP
    End If

    Case cdlCancel:
    Resume Fin_cmdIMPORT

    Case Else
    MsgBox Error$, vbInformation, "Import from Spreadsheet"
    ' ProgressReport ("- - - F A T A L E R R O R - - -" & CRLF & g_sError & CRLF)
    Resume Fin_cmdIMPORT

    End Select

    End Function

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Okay, I don't know if I have enough time today to go into how to rewrite that BrowseFileName code to simplify it and not use an ActiveX Control, which it appear is being used. But that's what you want. You need to delete the ActiveX Control (Common Dialog Control) from the form (and any form in the database) and then replace this code with some other which will just call the built in Office Dialogs. They didn't used to be available back in Access 2000 and possibly 2002, but they are now and it is so much simpler just to use them instead.

    If someone else wants to help you with that, I'm fine with that, but I will attempt to come up with something later, if possible, when I get home.

  7. #7
    bigfish311 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    4
    Will give it a try. Thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Object doesn't support property or method
    By Jamy in forum Programming
    Replies: 7
    Last Post: 07-15-2013, 01:42 AM
  2. Object does not support this method or property
    By Vronsky in forum Programming
    Replies: 5
    Last Post: 05-30-2011, 02:09 PM
  3. Replies: 7
    Last Post: 02-03-2011, 07:13 AM
  4. Replies: 5
    Last Post: 08-05-2009, 04:07 PM
  5. Replies: 2
    Last Post: 02-28-2009, 03:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums