VBA Function - Error

[Guest]

Hello-

I am somewhat of a nb so forgive my ignorance. I have developed some code
that utilizes a function. The code works (i.e. the query loads), but is
throwing an error. Here is the error message: "Error in procedure
cmdRunQuery_Click...Error 0:"

The code looks like this (this is only snippet of the code):

If TypeName = "pt_query" Then

results = PT(FilesName, 100)

End If

Here is the function:

Function PT(q As String, n As Integer):

On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("1-1_detailEstimate")
start_date = Format(Me.startDate, "yyyy-mm-dd")
end_date = Format(Me.endDate, "yyyy-mm-dd")
client_name = Me.ClientName

qdf.SQL = "SELECT adVariables.mediaPartner, adVariables.client,
adVariables.variable1, billableCampaignData.billableImpressions, billableCost
AS TotalCost, adVariables.cost, billableCampaignData.billableCost,
websiteMetrics.visits, websiteMetrics.visitors, websiteMetrics.pageViews,
websiteMetrics.visitDuration, websiteMetrics.timeStamp,
websiteMetrics.trackingId, websiteMetrics.endDate, websiteMetrics.startDate,
websiteMetrics.metric1, websiteMetrics.metric2, websiteMetrics.metric3,
websiteMetrics.metric4, websiteMetrics.metric5, websiteMetrics.id " & _
"FROM billableCampaignData RIGHT JOIN (websiteMetrics INNER JOIN adVariables
ON websiteMetrics.adVarId = adVariables.id) ON
billableCampaignData.destiniationUrl = adVariables.destinationUrl" & _
" WHERE websiteMetrics.startDate >= '" & start_date & "' AND
websiteMetrics.endDate <= '" & end_date & " '" & _
"And advariables.client = '" & client_name & "' " & _
"Limit " & n & ";"

DoCmd.OpenQuery q

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
'Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc

 

[Douglas J. Steele]

One problem is that you've commented out the line of code

Exit Sub

As a result, the code continues into the error handling bit of code, and
reports an Error 0 (which actually means No Error)

However, that being said, it looks as though you copied at least some of the
code from a sub somewhere else: since PT is a function, that line of code
needs to be Exit Function.

Even once you fix that, though, you're going to run into problems since your
function doesn't return any values. To have it return something, somewhere
in that function declaration, you'd need a line of code

PT = <value to return>

Worse, though, is that you set all of that SQL in your function, but you
don't use it! You've assigned the SQL to a query named 1-1_detailEstimate,
but you then run query q (which is whatever value was assigned to FilesName
in the routine that called the function)