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

Runtime Error '13'

P: 4
I have an Access DB that was originally created in Access 2, then converted to Access '97, and a few years ago converted again, to Access '03.

During the convert to '03 it was determined that DAO reference was incorrect and needed to be updated to 3.6 which we did.

I have a macro that won't run in '03 but will run under '97

When running the macro, the Error generated is
"Run TIme Error '13' Type mismatch"

Expand|Select|Wrap|Line Numbers
  1. Function Have_Recs(Table_Name As String)
  2.  
  3. ' This function will determine if a table is present and if present
  4. ' does it contain any records
  5. ' Returns -1 if records are found
  6. ' Returns 0 if the table is not present or is present without records
  7.  
  8. Dim listset As Recordset
  9.  
  10. Open_Data_Database
  11. Set listset = db.TableDefs()[/b] (this line is highlited in VBA)
  12.  
  13. Have_Recs = 0
  14. Have_Table = 0
  15.  
  16. listset.MoveFirst
  17.  
  18. While (Not listset.EOF And Have_Table = 0)
  19.    If listset!Name = Table_Name Then
  20.       If (listset!RecordCount > 0) Then
  21.          Have_Recs = -1
  22.       End If
  23.       j = listset!RecordCount
  24.       Have_Table = -1
  25.    End If
  26.    listset.MoveNext
  27. Wend
  28.  
  29. End Function
Does anyone have a clue what I need to do with this?
Apr 29 '10 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,638
@Frabker3
It was easier just to rewrite the code to something simpler and more efficient. The following Function will return True only if a Table exists AND contains at least 1 Record in it:
Expand|Select|Wrap|Line Numbers
  1. Public Function Have_Recs(strTableName As String) As Boolean
  2. If strTableName = "" Then Exit Function
  3.  
  4. Dim tdf As TableDef
  5.  
  6. Have_Recs = False           'Initialize to False
  7.  
  8. For Each tdf In CurrentDb.TableDefs
  9.   If tdf.Name = strTableName Then           'Table exists
  10.     If DCount("*", strTableName) > 0 Then   'Exists and has Records, return True!
  11.       Have_Recs = True
  12.         Exit For
  13.     End If
  14.   End If
  15. Next
  16. End Function
Apr 29 '10 #2

NeoPa
Expert Mod 15k+
P: 31,494
Can you say which line of your code generated the error message?

Welcome to Bytes!
Apr 30 '10 #3

P: 4
Line 11 of the original post is the offending line. But like everything in programming could be elsewhere as well.
Apr 30 '10 #4

P: 4
@ADezii
Thank You, this works well. And does what the other was supposed to do.

One issue that came up after this fix was run is that when closing the DB Access stays open. Any ideas?
Apr 30 '10 #5

ADezii
Expert 5K+
P: 8,638
@Frabker3
Hard to say, but sounds like you have a
Expand|Select|Wrap|Line Numbers
  1.  DoCmd.Close 
instead of a
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Quit 
somewhere.
Apr 30 '10 #6

NeoPa
Expert Mod 15k+
P: 31,494
In your original line #11 you are trying to assign a collection of recordsets (No indexd provided) to a recordset object variable. This is not possible. If you were to add an index however, this would be fine. It can also be done within a looping structure as in :
Expand|Select|Wrap|Line Numbers
  1. For Each listset in DB.TableDefs
May 2 '10 #7

P: 4
Thank you ADezil and NeoPa for your input. I will give it a shot on Monday or Tuesday.

Frank
May 2 '10 #8

NeoPa
Expert Mod 15k+
P: 31,494
A pleasure :)

Let us know how you get on.
May 3 '10 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
I would just mention here that when using DAO in Access you have to explicitly declare the variables as DAO type or they will default to ADO.

So in this case your recordset declaration has to be:

Expand|Select|Wrap|Line Numbers
  1. Dim listset As DAO.Recordset
Jun 12 '10 #10

NeoPa
Expert Mod 15k+
P: 31,494
Good point Mary. I overlooked that one.
Jun 13 '10 #11

Post your reply

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