By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

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

P: 1
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.
Expand|Select|Wrap|Line Numbers
  1. ublic Sub ConnectDB(Optional strConnString As String, Optional intConnectID As Integer) '(Optional DBPath As String, Optional ConnString As String)
  2.     Dim strConn, DBPath, ConnString As String
  3.  
  4.     'Define Connection
  5.     Select Case shtSettings.Range("setDBType").Value
  6.         Case "Access"
  7.             If InStr(1, shtSettings.Range("setSQLServer").Value, ":") <> 0 Then
  8.             'If FileOrDirExists(GetRootFolder() & "\system32\sqlncli.dll") = False Then
  9.                 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & shtSettings.Range("setSQLServer").Rows(1) & DBPath & ";Persist Security Info=False"
  10.             Else
  11.                 If CStr(shtSettings.Range("setSQLUsername").Value) = "" And CStr(shtSettings.Range("setSQLPassword").Value) = "" Then
  12.                     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ("C:\Documents and Settings\User\Desktop\Eitans Stuff\Docs\OJE.mdb")
  13.                 Else
  14.                     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & shtSettings.Range("setSQLServer").Rows(1) & ";Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";Persist Security Info=False"
  15.                 End If
  16.             End If
  17.         Case "MSSQL"
  18.             If FileOrDirExists(GetRootFolder() & "\system32\sqlncli.dll") = False Then
  19.                 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) & ";"
  20.             Else
  21.                 If CStr(shtSettings.Range("setSQLUsername").Value) = "" And CStr(shtSettings.Range("setSQLPassword").Value) = "" Then
  22.                     strConn = "Provider=SQLNCLI;Server=" & shtSettings.Range("setSQLServer").Rows(1) & ";Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";Trusted_Connection=yes;"
  23.                 Else
  24.                     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 & ";"
  25.                 End If
  26.             End If
  27.         Case "MSSQL2000"
  28.             If FileOrDirExists(GetRootFolder() & "\system32\sqlncli.dll") = False Then
  29.                 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) & ";"
  30.             Else
  31.                 If CStr(shtSettings.Range("setSQLUsername").Value) = "" And CStr(shtSettings.Range("setSQLPassword").Value) = "" Then
  32.                     strConn = "Provider=SQLNCLI;Server=" & shtSettings.Range("setSQLServer").Rows(1) & ";Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";Trusted_Connection=yes;"
  33.                 Else
  34.                     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 & ";"
  35.                 End If
  36.             End If
  37.         Case "MySQL"
  38.             If InStr(1, shtSettings.Range("setSQLServer").Value, ":") <> 0 Then
  39.                 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, ":")), "") & ";"
  40.             Else
  41.                 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 & ";"
  42.             End If
  43.     End Select
  44.     If GetSetting("ReimbursementTool", "Office", "OfficePC") = "1" Then
  45.         'strConn = "Driver=SQL Server;Server=SSISQL1\WEXCEL;user id=sa;password=hawkey;Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";"
  46.         'strConn = "Driver=SQL Server;Server=EITAN\SQLEXPRESS;user id=;password=;Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";"
  47.         'strConn = "Driver=SQL Server;Server=wxlsvr1;user id=sa;password=hawkey;Database=" & shtSettings.Range("setSQLDatabase").Rows(1) & ";"
  48.     End If
  49.     If strConnString <> "" Then strConn = strConnString
  50.  
  51.     'Instantiate Connection Object
  52.     Set cnnXLW = New ADODB.Connection
  53.  
  54.     'Specify read and write for the Access database
  55.     cnnXLW.Mode = adModeReadWrite
  56.  
  57.     'Open connection object
  58.     On Error GoTo DBError
  59.     cnnXLW.Open strConn
  60.     cnnXLW.CommandTimeout = 0
  61.  
  62.     Set rsXLW = New ADODB.Recordset
  63.     Set rsXLW_1 = New ADODB.Recordset
  64.     Set rsXLW_2 = New ADODB.Recordset
  65.     Set rsXLW_3 = New ADODB.Recordset
  66.     Set cmdXLW = New ADODB.Command
  67.     cmdXLW.ActiveConnection = cnnXLW
  68.     cmdXLW.CommandTimeout = 0
  69.  
  70.     Connected = True
  71.     'Application.Calculation = xlCalculationManual
  72.  
  73. ErrCont:
  74. Exit Sub
  75.  
  76. DBError:
  77. MsgBox "Database Connection Error: " & Err.Description
  78. Connected = False
  79. Resume ErrCont
  80.  
  81. End Sub
Expand|Select|Wrap|Line Numbers
  1. Public Sub OpenRec(SQL As String, Optional ByVal intCount As Integer)
  2.  
  3.     Select Case intCount
  4.         Case 0
  5.             If rsXLW.State = 1 Then CloseRec
  6.             If rsXLW.State = 0 Then
  7.              rsXLW.CursorLocation = adUseClient
  8.              rsXLW.Open SQL, cnnXLW, adOpenStatic, adLockOptimistic
  9.              RecOpen = True
  10.             End If
  11.  
  12.         Case 1
  13.             If rsXLW_1.State = 1 Then CloseRec (1)
  14.             If rsXLW_1.State = 0 Then
  15.              rsXLW_1.CursorLocation = adUseClient
  16.              rsXLW_1.Open SQL, cnnXLW, adOpenStatic, adLockOptimistic
  17.              RecOpen = True
  18.             End If
  19.  
  20.         Case 2
  21.             If rsXLW_2.State = 1 Then CloseRec (2)
  22.             If rsXLW_2.State = 0 Then
  23.              rsXLW_2.CursorLocation = adUseClient
  24.              rsXLW_2.Open SQL, cnnXLW, adOpenStatic, adLockOptimistic
  25.              RecOpen = True
  26.             End If
  27.  
  28.         Case 3
  29.             If rsXLW_3.State = 1 Then CloseRec (3)
  30.             If rsXLW_3.State = 0 Then
  31.              rsXLW_3.CursorLocation = adUseClient
  32.              rsXLW_3.Open SQL, cnnXLW, adOpenStatic, adLockOptimistic
  33.              RecOpen = True
  34.             End If
  35.  
  36.     End Select
  37.  
  38.     Exit Sub
  39.  
  40.  
  41.  
  42. End Sub 
Apr 21 '10 #1
Share this Question
Share on Google+
1 Reply


patjones
Expert 100+
P: 931
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
Apr 22 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.