By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,882 Members | 2,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,882 IT Pros & Developers. It's quick & easy.

Access 2003 + Type mismatch issue

P: 1
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub BatchNum_AfterUpdate()
  2. On Error GoTo Err_BatchNum_AfterUpdate
  4.     Dim strBatchNum As String
  5.     Dim strFilter As String
  6.     Dim sPath As String
  7.     Dim lngFlags As Long
  8.     Dim varFileName As Variant
  9.     Dim objXLApp As Excel.Application
  10.     Dim objXLBook As Excel.Workbook
  11.     Dim objDataSheet As Excel.Worksheet
  12.     Dim objCell As Excel.Range
  14.         strBatchNum = Right(Me.BatchNum, 4)
  15.         strFilter = "Excel Files (*.xls)" & vbNullChar & "*" & strBatchNum & "*.xls"
  19.         lngFlags = tscFNPathMustExist Or tscFNFileMustExist Or tscFNHideReadOnly
  21.     varFileName = tsGetFileFromUser( _
  22.     fOpenFile:=True, _
  23.     strFilter:=strFilter, _
  24.     rlngflags:=lngFlags, _
  25.     strDialogTitle:="Find File (Select The File And Click The Open Button)")
  27.     If IsNull(varFileName) Or varFileName = "" Then
  29.         MsgBox "File selection was canceled.", vbInformation
  31.         Exit Sub
  32.     Else
  34.         Me.tbfile = varFileName
  35.         Set objXLBook = GetObject(varFileName)  --------Generates error "Type Mismatch"        
  37. Set objXLApp = objXLBook.Parent
  39.         Set objDataSheet = objXLBook.ActiveSheet
  40.         Set objCell = objDataSheet.Range("A8")
  41.         objCell.FormulaR1C1 = "=AVERAGE(R[-6]C:R[-1]C)"
  42.         Me.Viscosity = objCell.Value
  43.         Set objCell = objDataSheet.Range("B8")
  44.         objCell.FormulaR1C1 = "=AVERAGE(R[-6]C:R[-1]C)"
  45.         Me.Speed = objCell.Value
  46.         Set objCell = objDataSheet.Range("F8")
  47.         objCell.FormulaR1C1 = "=AVERAGE(R[-6]C:R[-1]C)"
  48.         Me.Temp = objCell.Value
  49.         Set objCell = objDataSheet.Range("H7")
  51.             Select Case objCell.Value
  52.                 Case "T-D"
  53.                     Me.Spindle = "94"
  54.                 Case "T-A"
  55.                     Me.Spindle = "91"
  56.             End Select
  57.     End If
  58.     Me.Analyst.SetFocus
  60. Exit_BatchNum_AfterUpdate:
  61.     Exit Sub
  62. Err_BatchNum_AfterUpdate:
  63.     MsgBox Err.Description
  64.     Resume Exit_BatchNum_AfterUpdate
  66. End Sub
thanks for your help, hope this is enough info.
Oct 17 '08 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 489
It looks like you have at least one outside function there "tsGetFileFromUser" 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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 18 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.