Hi all,
I'm really running into problems with this error popping up every time I try running a query from VBA/Excel to Access, on this particular command. I've gone through my code multiple times, played around with multiple SQL statements, and made sure that my paths were correct. I keep getting Error 3265: Item Cannot Be Found In The Collection Corresponding to the Requested Name or Ordinal.
Here's my code:
I'm really running into problems with this error popping up every time I try running a query from VBA/Excel to Access, on this particular command. I've gone through my code multiple times, played around with multiple SQL statements, and made sure that my paths were correct. I keep getting Error 3265: Item Cannot Be Found In The Collection Corresponding to the Requested Name or Ordinal.
Here's my code:
Code:
Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_Click_Err
Dim SIP As New ADODB.Connection
Dim ManagerSearch As New ADODB.Recordset
Dim managerFID As String
Dim managerSQL As String
SIP.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=S:\Fin and Corporate\Skills Inventory Project\SDLC_PMLC\Database\SIPDB.mdb"
If cboSearchCriteria.Value = "Resource Manager" Then
managerFID = Me.cboCriteriaChoices.Value
managerSQL = "SELECT ManagerResults.Id_Emp, ManagerResults.LastName_Emp, ManagerResults.FirstName_Emp, ManagerResults.Name_Skills, " & _
"ManagerResults.Desc_Ctgy, ManagerResults.Level_Expertise, ManagerResults.Comments_Emp_Skill, ManagerResults.Manager_Emp_Resource FROM ManagerResults WHERE ManagerResults.Manager_Emp_Resource='" & managerFID & "';"
ManagerSearch.Open managerSQL, SIP, adOpenStatic
lboxResults.Clear
lboxResults.AddItem "FID"
lboxResults.Column(1, Me.lboxResults.ListCount - 1) = "Last Name"
lboxResults.Column(2, Me.lboxResults.ListCount - 1) = "First Name"
lboxResults.Column(3, Me.lboxResults.ListCount - 1) = "Skill Category"
lboxResults.Column(4, Me.lboxResults.ListCount - 1) = "Skill"
lboxResults.Column(5, Me.lboxResults.ListCount - 1) = "Level"
lboxResults.Column(6, Me.lboxResults.ListCount - 1) = "Comments"
ManagerSearch.MoveFirst
With Me.lboxResults
Do
.AddItem ManagerSearch![Employees.Id_Emp]
.Column(1, lboxResults.ListCount - 1) = ManagerSearch![ManagerResults.LastName_Emp]
.Column(2, lboxResults.ListCount - 1) = ManagerSearch![ManagerResults.FirstName_Emp]
.Column(3, lboxResults.ListCount - 1) = ManagerSearch![ManagerResults.Desc_Ctgy]
.Column(4, lboxResults.ListCount - 1) = ManagerSearch![ManagerResults.Name_Skills]
.Column(5, lboxResults.ListCount - 1) = ManagerSearch![ManagerResults.Level_Expertise]
.Column(6, lboxResults.ListCount - 1) = ManagerSearch![ManagerResults.Comments_Emp_Skill]
ManagerSearch.MoveNext
Loop Until ManagerSearch.EOF
End With
End If
cmdSubmit_Click_Exit:
On Error Resume Next
ManagerSearch.Close
SIP.Close
Set ManagerSearch = Nothing
Set SIP = Nothing
Exit Sub
cmdSubmit_Click_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume cmdSubmit_Click_Exit