Error 2950 only happens when I try to run an mde file

[kjpreston]

Hi,
I am at a loss. I have an Access 2003 database with a macro that calls a public function during the autoexec start up. This has been working fine in several databases that are .mde files. I have one that is not cooperating. I have added a couple of forms 2 tables, some queries and a report to this database, but nothing connect to the macro. When I try to open the .mde I get the message "the expression you entered has a function name that microsoft access can't find" and a button to click Ok. You must hit OK twice then you get the Action Failed dialog box that says this is error number 2950. I have check to make sure it is in a trusted location, the references are all there and even imported everything to a new database and still I get message. I am at a lose to solve this.
The macro and function were set up by someone else and I have inherited this database from them. If this is not the correct forum, let me know and I will re-post.
The action failed dialog box has:
Macro Name:
NASAsearch2
Condition:
True
ActionName:
RunCode
Argumetns:
ClrTable("NASAsearch")
Error Number:
2950


The code it blows up on is:

Public Function ClrTable(TName As String) As String
Dim db As DAO.Database
Dim wrs1 As DAO.Recordset
Set db = CurrentDb
Set wrs1 = db.OpenRecordset(TName, dbOpenDynaset)
Do Until wrs1.EOF
wrs1.Delete
wrs1.MoveNext
Loop
wrs1.close
Set wrs1 = Nothing
Set db = Nothing
ClrTable = "Table " & TName & " cleared! " & Time()
End Function

The rest of the macro repopulates the table after testing to see what type of user is signing in so it can open the correct form.

Thanks for your help

 

[vbajock]

You need some better error trapping, sometime code that looks correct bugs out because of some MS bug and you just have to code a work around. See my notes:


Public Function ClrTable(TName As String) As String
Dim db As DAO.Database
Dim wrs1 As DAO.Recordset


ON ERROR goto err_h <- I would add an error handler to see if I can trap an error in the function itself instead of letting the macro do it


STOP <- I would put a stop statement here and see if the macro is actually entering the function, t


Set db = CurrentDb
Set wrs1 = db.OpenRecordset(TName, dbOpenDynaset)
Do Until wrs1.EOF
wrs1.Delete
wrs1.MoveNext
Loop
wrs1.close
Set wrs1 = Nothing
Set db = Nothing
ClrTable = "Table " & TName & " cleared! " & Time()


'ADD THIS CODE TO TRAP ERROR
Exit Function
'*******
err_h:
'******
Msgbox Error$
Stop <- when it stops here, hit F8 to step thru the code back to the line giving the error
Resume




End Function

 

[dhookom]

I'm not sure why you are creating a recordset to delete records. SQL is much more efficient:

Public Function ClrTable(TName As String) As String
  Dim db As DAO.Database
  Set db = CurrentDb
  db.Execute "DELETE * FROM [" & TName & "]", dbFailOnError
  Set db = Nothing
  ClrTable = "Table " & TName & " cleared! " & Time()
End Function

Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

^ that indeed would be an excellent work around!

 

[kjpreston]

I don't know why the programmer did what he did either, but it works, so I leave it alone. About my initial problem. Apparently the Nz() function does not like to work in an Access .mde file. once I removed that (replaced it with an IIf statement) and complied the database (something else I forgot to do) everything worked fine. Thanks for your help I really appreciate it. I'm going to try the Public Function and see what happens. Thanks again