Runtime error Query Function (1 Viewer)

[Bechert]

Hello group,
I have a user experiencing a runtime error when the Print button is clicked on one form within the application (Access 2007). There is no error number assocatiated with the runtime error message. After clicking OK on the message Access shuts down. All of the VBA modules have error trapping.

The new code involves adding a function that is called from a query. The query is the source for a report that is generated when the Print button is clicked. The query is to complex to include all the necesary tables for the report. The code is attached.

All other features in the new FE are working properly. The user is running Windows 7. I have tried recreating the error on PCs with Vista, 7 and 8 with no luck.

The code is part of a new FE. They downloaded the file using an email link to LogmeIn. The user does a copy/paste to replace the old FE.

Thanks for your help.
Bill

Public Function PhysicianNameRetrieval(TblDocID As Variant)
On Error GoTo Err_PhysicianNameRetrieval
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim PhysicianWork As String

    PublicVariable9 = TblDocID
    Set db = CurrentDb()
        
    Set rst = db.OpenRecordset("QryPhysicianInfoForCMS1500Final", dbOpenDynaset)
    
    If rst.BOF And rst.EOF Then
        PhysicianWork = " "
        GoTo CloseRecordSet
    End If
    
    rst.MoveFirst
    
    PhysicianWork = rst.Fields("[PhysicianNameFull]").Value
                      

    PhysicianNameRetrieval = UCase(PhysicianWork)
                      
CloseRecordSet:
    rst.Close
    Set rst = Nothing
                              
Exit_PhysicianNameRetrieval:
    Exit Function
        
Err_PhysicianNameRetrieval:

    Box "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume Exit_PhysicianNameRetrieval
                   
End Function

 

[bob fitz]

What is the error message

 

[vbaInet]

There's one thing you're doing in your code which isn't advisable but we'll come that later.

There is no error number assocatiated with the runtime error message.[/CODE]

Comment out the following line and re-run the code to get the error message:

[COLOR="Red"]' [/COLOR]On Error GoTo Err_PhysicianNameRetrieval

 

[Bechert]

bob, the message says, "Execution of this application has stopped due to a run-time error. The application can't continue and will be shutdown."

Bill

 

[JHB]

Which code line cause the problem, (do as vbaInet suggest, comment out the line).
What is PublicVariable9 declared as, (we can't see it in the code)?
What type of value are you transferring into TblDocID, because you've declared as variant, so it can be everything from a boolean to an object?
The below line is not the normal way of receiving a value from field in a recordset!

   PhysicianWork = rst.Fields("[PhysicianNameFull]").Value

Normal it is done so:

   PhysicianWork = rst![PhysicianNameFull]

 

[Bechert]

JHB,

PublicVaraible9 is declared as a Variant. TblDocID is the key field for the record that contains the physician key, which gets me to the physician's name.

The user is running the runtime version of Access 2007. I don't have access to their computer.

The error does not occur in my development environment or when I use the production runtime version on different operating systems.

The code above is the only code change to the FE the user received. So I was expecting there to be something obvious in the code that would cause the problem.

Here is the call in the query to this routine:
PhysicianName: PhysicianNameRetrieval([TbldocMain].[TblDocID])

Is there anything at the user's end that might cause this?

Thanks for your help.
Bill

 

[spikepl]

If there is no error message then your code fails elsewhere...

The new code involves adding a function that is called from a query.

so that means your code goes belly-up when calling the function in the query, presumably.

A runtime DB stops on ANY unhandled error. This means you have to put error handlers all around the suspect area. Use MZ-tools, it makes that task easy.

 

[Bechert]

The following message does appear, I just don't know where it comes from.

"Execution of this application has stopped due to a run-time error. The application can't continue and will be shutdown."

I have looked for missing error trapping but haven't found any yet.
Bill

 

[spikepl]

QED!

The thing is kindly telling you "Go forth and multiply"

So you still need to follow my suggestion to move on.

 

[spikepl]

Alternatively tell us a story illustrated by code /screenshots where and how your function gets called

 

[vbaInet]

Ask your user the exact steps s/he took prior to the error and replicate the same on your development environment.

And yes like spikepl mentioned, you need to apply error handling on every single function/sub that could cause errors. If you're unsure, put it in every single function/sub.