I have 2 combo boxes in a form- One for the client and the other for the project.
When the form loads, the client combo box gets populated with all the clients.
When a particular client is clicked, the project combo box gets populated with all the projects corresponding to that client.
So far, so good. Now when I click a project in the project combo box, I get this runtime error.
Been stuck here for 2 days without a solution. Please help.
This is the code I have
Expand|Select|Wrap|Line Numbers
- Private Sub Form_Load()
- Call SetupADO
- Call SetupClients
- End sub
- Private Sub SetupADO()
- Dim adoParm As ADODB.Parameter
- 'Create connection object and connect to database
- Set acnChecklist = New ADODB.Connection
- With acnChecklist
- .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\My Documents\Specifications\Checklist1.mdb;Persist Security Info=False"
- .Open
- .CursorLocation = adUseClient
- End With
- ' Setup the clients command and record set
- Set acmClients = New ADODB.Command
- With acmClients
- .ActiveConnection = acnChecklist
- .CommandType = adCmdText
- .CommandText = "SELECT ClientID, ClientName from tblClients"
- End With
- Set arsClients = Nothing
- ' Setup the projects command and record set
- Set acmProjects = New ADODB.Command
- With acmProjects
- .ActiveConnection = acnChecklist
- .CommandType = adCmdText
- .CommandText = "SELECT ProjectID, ProjectName FROM tblProject WHERE ClientID= ?"
- Set adoParm = .CreateParameter("ClientID", adNumeric, adParamInput, , 1)
- .Parameters.Append adoParm
- End With
- End sub
- Populate the Clients Combo box
- Private Sub SetupClients()
- 'Execute Query
- Set arsClients = New ADODB.Recordset
- arsClients.Open acmClients, , adOpenStatic, adLockOptimistic
- ' Populate combo-box with query results
- cboClients.Clear
- While Not arsClients.EOF
- cboClients.AddItem arsClients!ClientName
- arsClients.MoveNext
- Wend
- cboClients.Text = ""
- cboProjects.Text = ""
- End sub
- ' Handle change in client selection
- Private Sub cboClients_Click()
- Dim strClientName As String
- Dim iClientID As Integer
- ' If nothing selected, then do nothing
- If cboClients.Text = "" Then
- Exit Sub
- End If
- strClientName = cboClients.Text
- arsClients.Find "ClientName= '" & strClientName & "'", , adSearchForward, adBookmarkFirst
- iClientID = arsClients("ClientID")
- Call SetupProjects(iClientID)
- End Sub
- 'Populate the projects combo box based on the selected client
- Private Sub SetupProjects(ByRef iClientID As Integer)
- Call acmProjects.Parameters.Delete("ClientID")
- Dim adoParm As ADODB.Parameter
- Set adoParm = acmProjects.CreateParameter("ClientID", adNumeric, adParamInput, , iClientID)
- Call acmProjects.Parameters.Append(adoParm)
- If arsProjects Is Nothing Then
- Set arsProjects = New ADODB.Recordset
- arsProjects.Open acmProjects, , adOpenStatic, adLockOptimistic
- Else
- arsProjects.Requery
- End If
- cboProjects.Clear
- While Not arsProjects.EOF
- cboProjects.AddItem arsProjects!ProjectName
- arsProjects.MoveNext
- Wend
- End Sub
- Private Sub cboProjects_Click()
- Dim strProjectName As String
- Dim iProjectID As Long
- If cboProjects.Text = "" Then
- Exit Sub
- End If
- strProjectName = cboProjects.Text
- If arsProjects Is Nothing Then
- Set arsProjects = New ADODB.Recordset
- arsProjects.Open acmProjects, , adOpenStatic, adLockOptimistic
- Else
- arsProjects.Requery
- End If
- With arsProjects
- If .BOF And .EOF Then
- .Requery
- .MoveFirst
- End If
- End With
- arsProjects.Find "ProjectName='" & strProjectName & "'", adSearchForward, adBookmarkFirst
- iProjectID = arsProjects("ProjectID") ------> Error in this line
End Sub
Hope u guys can bail me out.