I am receiving an unhandled vb6 overflow error on an ADO(2.5)
recordset.open statement connected to SQL Server 2000(SP2).
Some hopefully useful notes...
-this error only occurs in the compiled .exe on a different windows
2000 machine, not on the development windows 2000 machine (.exe or
ide).
-the error should be handled considering there is an on error goto at
the beginning of the procedure, however this statement is ignored and
the program exits ungracefully.
-Break on unhandled errors is selected in the general options tab.
-Same version MDAC both machines.

Any help would be greatly appreciated,
Justin

Reference Code: ------------------------------------------

Private Sub xcmdOk_Click()
On Error GoTo xcmdOk_Click_err

    If Len(xtxtUser.Text) = 0 Then
        xtxtUser.xErrorState = True
        Exit Sub
    End If

    Dim rstLogin As Recordset
    Set rstLogin = New Recordset

    rstLogin.Open "SELECT * FROM tblUser " & _
    "WHERE fldUserName = '" & xtxtUser.Text & "'", gcnnSQLDB

    If Not rstLogin.BOF And Not rstLogin.EOF Then
        If xtxtPass.Text = rstLogin!fldUserPassword Then
            SaveSetting "Leonardo", "Login", "Lastuser", xtxtUser.Text
                gintUserID = rstLogin!fldUserID
                rstLogin.Close
'###############CULPRIT OPEN STATEMENT ############################
                rstLogin.Open "SELECT fldPermissionMenu FROM
tblPermissions INNER JOIN " & _
"(tblUser INNER JOIN tblUserPermissions ON tblUser.fldUserID = " & _
                "tblUserPermissions.fldUserID) ON
tblPermissions.fldPermissionID = " & _
                "tblUserPermissions.fldPermissionID WHERE
tblUser.fldUserID = " & _
                gintUserID & "AND fldPermissionMenu Is Not Null"
'##################################################################
                Do While Not rstLogin.EOF

                    frmMain.Controls(rstLogin!fldPermissionMenu).Enabled
= True
                    rstLogin.MoveNext

                Loop

                'Loop through and set all disabled menus to
descriptive caption...
                Dim ctlSetEnabled As Control
                For Each ctlSetEnabled In frmMain.Controls
                    If TypeOf ctlSetEnabled Is Menu Then
                        If Not ctlSetEnabled.Enabled Then
                            ctlSetEnabled.Caption = "Not Available"
                        End If
                    End If
                Next ctlSetEnabled

            Unload Me
        Else
            MsgBox "The password is incorrect.", , "Password"
            xtxtPass.Text = vbNullString
            xtxtPass.SetFocus
        End If
    Else
        MsgBox "The user name is invalid.", , "User name"
        xtxtUser.SetFocus
    End If

    rstLogin.Close
    Set rstLogin = Nothing

Exit Sub
xcmdOk_Click_err:

    Call subErrHandler("frmLogin", "xcmdOk_Click", err.Number,
err.Description)
    Resume Next

End Sub
-----------------------------------------------

I have a couple of thoughts for you.

1. The problem is that you need to put a space character after the
gintUserID and the word "and". This should fix the problem.

2. Consider fully qualifying your types. Rather than

        Dim rstLogin as Recordset

you should do ...

        Dim rstLogin as ADODB.Recordset

This avoids so much confusion

3. When you run into a problem such as the one your have documented
here, put a breakpoint at the offending line, copy the code of the
select statement and paste it into the immediate window to evaluate the
string (preceeding the code with a '?'). This allows you to see exactly
what is being passed to the open method - makes debugging so much
easier.

--
Scott R. Quier
Don't cry because its over; smile because it happened.

I don't know how this could be the problem because for one, there is a
space between the two.  Secondly, this would cause a syntax error,
which I am not receiving on the development machine, nor as an error
message on the test machine (overflow error).

The variable gintUserID and the word AND are separated as such:
   gintUserID & "AND

As for the var declaration qualification, I have considered this, but
until we reference more than one data access library it is just easier
to accept the default.  As far as I know there are no problems
associated with referencing ADO as a default when there is no other DB
libraries referenced.

Any other ideas?

Thanks again,
Justin

He means...
    gintUserID & "AND
should become...
    gintUserID & " AND

Bill

: I don't know how this could be the problem because for one, there is a
: space between the two.  Secondly, this would cause a syntax error,
: which I am not receiving on the development machine, nor as an error
: message on the test machine (overflow error).
:
: The variable gintUserID and the word AND are separated as such:
:    gintUserID & "AND
:
: As for the var declaration qualification, I have considered this, but
: until we reference more than one data access library it is just easier
: to accept the default.  As far as I know there are no problems
: associated with referencing ADO as a default when there is no other DB
: libraries referenced.
:
: Any other ideas?
:
: Thanks again,
: Justin