Using a combo box to enter data

[integritycare]

Hello,
Here is an issue I need some help with please.
On a client form there is are 2 combo boxes. One will select a company and at the same time will select a department. There needs to be another text box with data ie; Attn to.

The form has been designed so that when the company data has changed, the department combo will open to select the department. The requirment is to be able to select the department person. The code for the Company combo box is;

Private Sub cboCompanyname_AfterUpdate()
Me.cboDepartment.RowSource = "SELECT Dept FROM" & " tblDepartment WHERE Company = " & Me.cboCompanyname & " ORDER BY Dept"

On Error Resume Next

Dim RS As ADODB.Recordset
Dim strSql As String

cboCompanyname.SetFocus
If cboCompanyname.Value > 0 Then
strSql = "SELECT * FROM tblBrokerStaff WHERE Brokerstaffid = " & cboCompanyname.Value

Set RS = CreateObject("ADODB.Recordset")
RS.CursorType = adOpenKeyset
RS.LockType = adLockOptimistic
RS.Open strSql, CurrentProject.Connection

If RS.State = 1 Then
If Not RS.BOF Then

Me.BrokerstaffID = RS("Brokerstaffid")
Me.Companyname = RS("companyname")
Me.BillingAddress = RS("BillingAddress")

End If
RS.Close
End If
Set RS = Nothing
End If
End Sub

The text fields used are [blue]Companyname, Dept, Attnto [/blue]

The tope of the code:
[blue]Me.cboDepartment.RowSource = "SELECT Dept FROM" & " tblDepartment WHERE Company = " & Me.cboCompanyname & " ORDER BY Dept" [/blue]

is used to select the Department. [blue]. The table (tblDepartment) has the fields, Dept and Attn To [/blue]

I would like the following process to happen;
1. Select the company (works ok)
2. Select the Department (works ok)
3. Select the AttnTo

This code has been written so that only the departments are selected are for that company. Now I needd to see the person for that department selected.

Hope this makes sense

Thank you

 

[kjv1611]

So, Attnto is another combo box, right?

If so, you just need to have the AfterUpdate code of Department change the record source for AttnTo, and then requery AttnTo just to be sure the change took.

At least, that's what it sounds like to me. It's basically the same thing you've got for Company to Department, but for Department to AttnTo.