Weird VBA behavior (1 Viewer)

ASherbuck · Dec 25, 2011

I have a class, Customer

When I call the following function I have strange behavior:

Code:

Public Function isCustomer(custID As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlstr As String

isCustomer = False

sqlstr = "SELECT * from tbltmpCustomer WHERE iAccountID = " & custID & ";"
Debug.Print sqlstr
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstr)
With rs
    If .EOF = False Then
        MsgBox "Customer exists"
        isCustomer = True
        Else
        MsgBox "Customer does not exist"
        End If
    End With
rs.Close
db.Close
End Function

If a record is found in the database it works fine, if no record is found it's like the function doesn't even run.

For testing I tossed a msgbox in after I declare my variables, if I give an accountID I know doesn't exist it won't even pop up the message box. If I give a valid accountID I get my test message box and my customer exists message box - all works fine.

jdraw · Dec 25, 2011

I copied your function and adjusted to use one of my test tables. Worked fine as shown below.

What is the value of the CustId you used?

Public Function isCustomer(custID As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlstr As String

isCustomer = False

sqlstr = "SELECT * from CustList WHERE Custno = " & custID
'iAccountID = " & custID & ";"
Debug.Print sqlstr
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstr)
With rs
If .EOF = False Then
MsgBox "Customer exists"
isCustomer = True
Else
MsgBox "Customer does not exist"
End If
End With
rs.Close
db.Close
End Function

Sub testcustno()
Debug.Print isCustomer(100)
End Sub

Immediate window:

SELECT * from CustList WHERE Custno = 100
True

ASherbuck · Dec 25, 2011

I was using isCustomer(1) and isCustomer(100)

There is no account #100 in my DB and that's where I was getting this odd behavior.

Oh wow, right in the middle of typing this I decided to Debug -> Compile and it went through fine but now I'm getting the customer not found message. I swore I'd tried that, too. Such odd behavior

gemma-the-husky · Dec 26, 2011

maybe the function is not running at all.

how do you call iscustomer?

Weird VBA behavior (1 Viewer)

ASherbuck

Registered User.

jdraw

Super Moderator

ASherbuck

Registered User.

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread