run-time error '-2147217904 (80040e10)': No value given for one or more parameters

[zedd]

Hi,

I orignaly posted this with the error above, however I am still trying to solve it on my own, and found that some of my syntax in the query was not correct and I corrected it. But i am still getting an error although a different one = run-time error '-2147217913 (80040e07)': data type mismatch in criteria expression.

I'm creating a user form in excel to pull through/update/insert info into my access db. I have created a user form to login. I had many issues in getting the login user form to show, but I finally managed. However, now when I type in the username and pw and click on the btnLogin, it gives me the error above, and when I click debug, it brings me into the OpenRec sub.

Here is the code I use to connect to the db, and the OpenRec sub I mentioned which it brings me to when I click debug. I've bolded, underlined and italiced where it breaks at.

ublic Sub ConnectDB(Optional strConnString As String, Optional intConnectID As Integer) '(Optional DBPath As String, Optional ConnString As String)
    Dim strConn, DBPath, ConnString As String
 
    'Define Connection
    Select Case shtSettings.Range("setDBType").Value
        Case "Access"
            If InStr(1, shtSettings.Range("setSQLServer").Value, ":") <> 0 Then
            'If FileOrDirExists(GetRootFolder() & "\system32\sqlncli.dll") = False Then
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & shtSettings.Range("setSQLServer").Rows(1) & DBPath & ";Persist Security Info=False"
            Else
                If CStr(shtSettings.Range("setSQLUsername").Value) = "" And CStr(shtSettings.Range("setSQLPassword").Value) = "" Then
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ("C:\Documents and Settings\User\Desktop\Eitans Stuff\Docs\OJE.mdb")
                Else
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & shtSettings.Range("setSQLServer").Rows(1) & ";Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";Persist Security Info=False"
                End If
            End If
        Case "MSSQL"
            If FileOrDirExists(GetRootFolder() & "\system32\sqlncli.dll") = False Then
                strConn = "Driver=SQL Server;Server=" & shtSettings.Range("setSQLServer").Rows(1) & ";user id=" & shtSettings.Range("setSQLUsername").Value & ";password=" & shtSettings.Range("setSQLPassword").Value & ";Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";"
            Else
                If CStr(shtSettings.Range("setSQLUsername").Value) = "" And CStr(shtSettings.Range("setSQLPassword").Value) = "" Then
                    strConn = "Provider=SQLNCLI;Server=" & shtSettings.Range("setSQLServer").Rows(1) & ";Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";Trusted_Connection=yes;"
                Else
                    strConn = "Provider=SQLNCLI;Server=" & shtSettings.Range("setSQLServer").Rows(1) & ";Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";Uid=" & shtSettings.Range("setSQLUsername").Value & "; Pwd=" & shtSettings.Range("setSQLPassword").Value & ";"
                End If
            End If
        Case "MSSQL2000"
            If FileOrDirExists(GetRootFolder() & "\system32\sqlncli.dll") = False Then
                strConn = "Driver=SQL Server;Server=" & shtSettings.Range("setSQLServer").Rows(1) & ";user id=" & shtSettings.Range("setSQLUsername").Value & ";password=" & shtSettings.Range("setSQLPassword").Value & ";Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";"
            Else
                If CStr(shtSettings.Range("setSQLUsername").Value) = "" And CStr(shtSettings.Range("setSQLPassword").Value) = "" Then
                    strConn = "Provider=SQLNCLI;Server=" & shtSettings.Range("setSQLServer").Rows(1) & ";Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";Trusted_Connection=yes;"
                Else
                    strConn = "Provider=SQLNCLI;Server=" & shtSettings.Range("setSQLServer").Rows(1) & ";Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";Uid=" & shtSettings.Range("setSQLUsername").Value & "; Pwd=" & shtSettings.Range("setSQLPassword").Value & ";"
                End If
            End If
        Case "MySQL"
            If InStr(1, shtSettings.Range("setSQLServer").Value, ":") <> 0 Then
                strConn = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & Left(shtSettings.Range("setSQLServer").Rows(1), InStr(1, shtSettings.Range("setSQLServer").Value, ":") - 1) & ";DATABASE=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";USER=" & shtSettings.Range("setSQLUsername").Value & ";PASSWORD=" & shtSettings.Range("setSQLPassword").Value & ";PORT=" & Replace(shtSettings.Range("setSQLServer").Rows(1), Left(shtSettings.Range("setSQLServer").Rows(1), InStr(1, shtSettings.Range("setSQLServer").Value, ":")), "") & ";"
            Else
                strConn = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & shtSettings.Range("setSQLServer").Rows(1) & ";DATABASE=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";USER=" & shtSettings.Range("setSQLUsername").Value & ";PASSWORD=" & shtSettings.Range("setSQLPassword").Value & ";"
            End If
    End Select
    If GetSetting("ReimbursementTool", "Office", "OfficePC") = "1" Then
        'strConn = "Driver=SQL Server;Server=SSISQL1\WEXCEL;user id=sa;password=hawkey;Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";"
        'strConn = "Driver=SQL Server;Server=EITAN\SQLEXPRESS;user id=;password=;Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";"
        'strConn = "Driver=SQL Server;Server=wxlsvr1;user id=sa;password=hawkey;Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";"
    End If
    If strConnString <> "" Then strConn = strConnString
 
    'Instantiate Connection Object
    Set cnnXLW = New ADODB.Connection
 
    'Specify read and write for the Access database
    cnnXLW.Mode = adModeReadWrite
 
    'Open connection object
    On Error GoTo DBError
    cnnXLW.Open strConn
    cnnXLW.CommandTimeout = 0
 
    Set rsXLW = New ADODB.Recordset
    Set rsXLW_1 = New ADODB.Recordset
    Set rsXLW_2 = New ADODB.Recordset
    Set rsXLW_3 = New ADODB.Recordset
    Set cmdXLW = New ADODB.Command
    cmdXLW.ActiveConnection = cnnXLW
    cmdXLW.CommandTimeout = 0
 
    Connected = True
    'Application.Calculation = xlCalculationManual
 
ErrCont:
Exit Sub
 
DBError:
MsgBox "Database Connection Error: " & Err.Description
Connected = False
Resume ErrCont
 
End Sub

Public Sub OpenRec(SQL As String, Optional ByVal intCount As Integer)
 
    Select Case intCount
        Case 0
            If rsXLW.State = 1 Then CloseRec
            If rsXLW.State = 0 Then
             rsXLW.CursorLocation = adUseClient
             rsXLW.Open SQL, cnnXLW, adOpenStatic, adLockOptimistic
             RecOpen = True
            End If
 
        Case 1
            If rsXLW_1.State = 1 Then CloseRec (1)
            If rsXLW_1.State = 0 Then
             rsXLW_1.CursorLocation = adUseClient
             rsXLW_1.Open SQL, cnnXLW, adOpenStatic, adLockOptimistic
             RecOpen = True
            End If
 
        Case 2
            If rsXLW_2.State = 1 Then CloseRec (2)
            If rsXLW_2.State = 0 Then
             rsXLW_2.CursorLocation = adUseClient
             rsXLW_2.Open SQL, cnnXLW, adOpenStatic, adLockOptimistic
             RecOpen = True
            End If
 
        Case 3
            If rsXLW_3.State = 1 Then CloseRec (3)
            If rsXLW_3.State = 0 Then
             rsXLW_3.CursorLocation = adUseClient
             rsXLW_3.Open SQL, cnnXLW, adOpenStatic, adLockOptimistic
             RecOpen = True
            End If
 
    End Select
 
    Exit Sub
 
 
 
End Sub 

[patjones]

Where is the call to OpenRec? I'd like to see explicitly what is being passed in to it. And if you could post the code for the btnLogin On Click event that would be helpful.

Pat