Access 2003 + Type mismatch issue

[nckinfutz]

hello, I am having a problem with an access database. this is not my database and I did not create it, nor am I very good at access. however, I am a network engineer and that is why this problem has ended up in my lap i guess.

This is the issue: There is an access database that pulls data from a list of excel spread sheets. This access database and excel files are stored on a server. When the database requests info from the excel file and tries to import it from the spread sheet, there is a 'type mismatch' error.

Nowthen, if i log on to the database with admin rights, the request works, but if I log on as a user, It doesnt work and I get this mismatch error. However, I have checked all the permissions for the database and folders (etc.) and that all looks fine.

Below is the code with what the creator highlighted as where he thought was the problem existed

Private Sub BatchNum_AfterUpdate()
On Error GoTo Err_BatchNum_AfterUpdate
 
    Dim strBatchNum As String
    Dim strFilter As String
    Dim sPath As String
    Dim lngFlags As Long
    Dim varFileName As Variant
    Dim objXLApp As Excel.Application
    Dim objXLBook As Excel.Workbook
    Dim objDataSheet As Excel.Worksheet
    Dim objCell As Excel.Range
    
        strBatchNum = Right(Me.BatchNum, 4)
        strFilter = "Excel Files (*.xls)" & vbNullChar & "*" & strBatchNum & "*.xls"
        
        
        
        lngFlags = tscFNPathMustExist Or tscFNFileMustExist Or tscFNHideReadOnly
 
    varFileName = tsGetFileFromUser( _
    fOpenFile:=True, _
    strFilter:=strFilter, _
    rlngflags:=lngFlags, _
    strDialogTitle:="Find File (Select The File And Click The Open Button)")
 
    If IsNull(varFileName) Or varFileName = "" Then
        
        MsgBox "File selection was canceled.", vbInformation
        
        Exit Sub
    Else
        
        Me.tbfile = varFileName
        Set objXLBook = GetObject(varFileName)  [B]--------Generates error "Type Mismatch"[/B]        

Set objXLApp = objXLBook.Parent
        
        Set objDataSheet = objXLBook.ActiveSheet
        Set objCell = objDataSheet.Range("A8")
        objCell.FormulaR1C1 = "=AVERAGE(R[-6]C:R[-1]C)"
        Me.Viscosity = objCell.Value
        Set objCell = objDataSheet.Range("B8")
        objCell.FormulaR1C1 = "=AVERAGE(R[-6]C:R[-1]C)"
        Me.Speed = objCell.Value
        Set objCell = objDataSheet.Range("F8")
        objCell.FormulaR1C1 = "=AVERAGE(R[-6]C:R[-1]C)"
        Me.Temp = objCell.Value
        Set objCell = objDataSheet.Range("H7")
            
            Select Case objCell.Value
                Case "T-D"
                    Me.Spindle = "94"
                Case "T-A"
                    Me.Spindle = "91"
            End Select
    End If
    Me.Analyst.SetFocus
    
Exit_BatchNum_AfterUpdate:
    Exit Sub
Err_BatchNum_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_BatchNum_AfterUpdate
    
End Sub

thanks for your help, hope this is enough info.

[DonRayner]

It looks like you have at least one outside function there "tsGetFileFromU ser" This function is what is setting the variable that is used in the the line that you are getting your error on. The users might not have the correct permissions for whatever module the function is stored in.

Try putting a message box call just before that line to see what the variable is set to. If it's not returning what was selected then you probally have a problem with the function.

msgbox varFileName

As long as you are running a full version of Access you can find out for sure the line that the code is crashing on by commenting out the "on error" line in the code. When it's run without the "on error" it will crash to the debug screen and hi-lite the exact line causing the error.