Using public function from private sub?

[Tekime]

Is it possible to use a public function from within a private form sub? I am trying to pass a value to the function, and assign the return value to a variable.

The function is defined as such in a module:


Public Function cbStatus(chargebackId As Integer) As Integer
' Determine the current status of the chargeback specified by chargebackId

Dim cnn As ADODB.Connection
Dim rstStatus As New ADODB.Recordset
Dim strSQLStatus As String

Set cnn = CurrentProject.Connection

strSQLStatus = "SELECT tbl_Chargebacks.chargebackId, tbl_Rebuttals.rbt_isComplete, tbl_Rebuttals.rbt_trackingNumber, tbl_Chargebacks.cb_resolutionId " & _
"FROM tbl_Chargebacks INNER JOIN tbl_Rebuttals ON tbl_Chargebacks.chargebackId = tbl_Rebuttals.rbt_chargebackId " & _
"WHERE tbl_Chargebacks.chargebackId = " & chargebackId

rstStatus.Open strSQLStatus, cnn, adOpenForwardOnly
rstStatus.MoveFirst

If (Not rstStatus.EOF) Then
' Make sure we have a recordset
If (rstStatus("rbt_isComplete") = True) Then
' See if a rebuttal has been completed

If (rstStatus("rbt_trackingNumber") > 0) Then
' Check for an outgoing tracking number

If (rstStatus("cb_resolutionId") > 0) Then
' See if case has been resolved
' Case closed
cbStatus = 4
Else
' Case pending resolution
cbStatus = 3
End If
Else
' Rebuttal complete, not mailed
cbStatus = 2
End If
Else
' Case entered, rebuttal incomplete
cbStatus = 1
End If

Else
cbStatus = 0
End If

End Function


When I try to call this function from a form module it fails with "Type Mismatch". I don't think any of my variable types are mismatched, but I can't figure out what is preventing me from getting this to work.

I'm calling it like:


Dim statusCode As Integer

statusCode = cbStatus(1)


Any help would be highly appreciated, thanks!

 

[dcx693]

What line of the function gives you the type mismatch error?

 

[Tekime]

This line in the form module:


statusCode = cbStatus(1)


Actually, I call this function with a reference to the value of a control on the form, but I have tested it with just a number and left it that way for clarity.

I can use the function in, say, the control source of a control on my form (i.e. "=cbStatus([chargebackId])"), but I cannot use it from the form's module...

 

[RichMorrison]

I would guess that you are falling through all the Ifs.

Assign a zero value to cbStatus right after the Dim statements.

Then try it again.

RichM

 

[AncientOne]

I would also do a debug.Print after the SQL statement to ensure that it is being correctly built

 

[Tekime]

Hey, thanks for the replies guys. I tried assigning zero to cbStatus where mentioned, and adding debug.print after the SQL statement assignment. Still the same thing though, type mismatch.

The strangest thing is that the function is working perfectly fine when I use it in the form (as a control source). For example, I'm passing the results of this function to another function in the control source of a status name field:


=cbStatusTitle(cbStatus([chargebackId]))


It just seems to cause problems when I use the function in the form module. I'm checking Me!chargebackId beforehand, and it holds a proper value.

I'll keep hackin away and update if I find anything worth mentioning. Thanks again for the input.

 

[AncientOne]

Whoa there! What did the Debug print statement return? please post it.

 

[Tekime]

Nothing.. it gives me run-time error 13: Type mismatch before it ever hits the Debug.Print statment Unless I'm using it incorrectly? I just placed Debug.Print after the SQL statement.

 

[AncientOne]

in that case one of your variables is mismatched. What is the datatype of the argument you send to your function? Should it be a Long and not an integer?

 

[Mile-O]

Try calling it thusly:

=cbStatusTitle(cbStatus(CInt([chargebackId])))

Furthermore if your value is over 37,000 something (don't know exact figure off head (37,568?) but you may need a Long instead.

 

[Tekime]

I'm sending an integer. Right now the value is < 100. The line I mentioned above that you referenced Mile:

=cbStatusTitle(cbStatus([chargebackId]))

Works perfectly. It's only when I try to call the function from form module that it gives an error. So I know the function works, I just can't use it from code.

I tried changing all references to Long and it still gives the same error

 

[AncientOne]

32768 (2 to the 16th)

 

[Tekime]

Hmm.. okay played around some more. Right now the code calling the function is in the OnLoad event of my form. I'll post the entire event sub (it's not too long):


Private Sub Form_Current()

Dim statusCode As Integer

' Breaks on this line
statusCode = cbStatus(Me!chargebackId)

If (statusCode = 1) Then
Me![statusCode].BackColor = 11039140
ElseIf (statusCode = 2) Then
Me![statusCode].BackColor = 11759718
ElseIf (statusCode = 3) Then
Me![statusCode].BackColor = 11514981
ElseIf (statusCode = 4) Then
Me![statusCode].BackColor = 4315347
End If


End Sub


If I try to run the form, get the mismatch error, click Debug to head to the VBA editor, and enter ?cbStatus(43) into the Immediate window it will also give me a Type Mismatch error. BUT, if I DON'T open the form, and go to the VBA editor from form design mode and type that into the Immediate window, it returns the correct value.

I don't know if this helps or just confuses things. I feel like I'm missing something drastically obvious...

edit: fixed something I had changed while messing around

 

[AncientOne]

I'd use a Select case statement on the form istead of all the IF statements, but I don't think that has any bearing on your problem.

As this code is on an automatic event at present, may I suggest you move it under the control of a command button just to test it out. Then you can load the form and see exactly what happens when the button is fired.There may be some subtle timing problem involved. Or perhaps we're all being a bit dense?

 

[Mile-O]

By any chance are you performing this on an autonumber that is a new record with no value yet?

 

[AncientOne]

Good thinking Mile!

 

[Mile-O]

Good thinking Mile!

This "heatwave" hasn't got to me yet...

 

[Tekime]

I'd use a Select case statement on the form istead of all the IF statements, but I don't think that has any bearing on your problem.

I would do, just haven't gotten to changing it. Actually, I'll probably replace the color codes with preset variables and change a few other things around, but I'm leaving it alone until I get this sorted out.

As this code is on an automatic event at present, may I suggest you move it under the control of a command button just to test it out. Then you can load the form and see exactly what happens when the button is fired.There may be some subtle timing problem involved. Or perhaps we're all being a bit dense?

Well I moved the exact code over to the onClick event of a button I added. Same error.

Mile I only wish it were that easy! This is not a data entry form, and I've checked the value beforehand. It'll even give this error if I specify a number instead of using Me!ChargebackId.

I also read that you can get a Type Mismatch error if you are using DAO and ADO and DAO is specified first in your references. So I changed the order of references, even the version of the ADO reference but it still gives the same error.

 

[Mile-O]

Does the message box run with this code?

Private Sub Form_Current() 

    'Dim statusCode As Integer 

    MsgBox Me!chargebackId
    'statusCode = cbStatus(Me!chargebackId) 

    Select Case statusCode
        Case Is = 1
            Me![statusCode].BackColor = 11039140
        Case Is = 2
            Me![statusCode].BackColor = 11759718
        Case Is = 3
            Me![statusCode].BackColor = 11514981 
        Case Is = 4
            Me![statusCode].BackColor = 4315347 
    End Select

End Sub

 

[Tekime]

Yes, it echoes the proper chargebackId (43 in this case).

PS thanks for making the case statements