Combo box to search on TWO fields on a form (Access 2K)

[computerider]

I have a form is based has a table with a dual primary key comprised of a Project ID# and a Department name.

I created a combo box to search the form for a specific record - so I might be looking for record “3-Sales” but if there is a “3-Audit” it will stop at “3-Audit” because of this code:

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++
Private Sub cboLookUp_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[AAID] = " & Str(Me![cboLookUp])
Me.Bookmark = rs.Bookmark
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++

so how do I rewrite have it search for AAID and DepartmentName?

[ADezii]

I have a form is based has a table with a dual primary key comprised of a Project ID# and a Department name.

I created a combo box to search the form for a specific record - so I might be looking for record “3-Sales” but if there is a “3-Audit” it will stop at “3-Audit” because of this code:

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++
Private Sub cboLookUp_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[AAID] = " & Str(Me![cboLookUp])
Me.Bookmark = rs.Bookmark
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++

so how do I rewrite have it search for AAID and DepartmentName?

Assumptions:

1. [AAID] is a Numeric Field [LONG]
2. [DepartmentName] is a String Field
3. Combo Box Name is cboLookUp
4. cboLookup contains 2 Columns (AAID, and DepartmentName)
5. [AAID] and [DepartmentName] make up the Composite, Primary Key
6. Code is executed in the AfterUpdate() Event of cboLookup

Private Sub cboLookUp_AfterUpdate()
Dim intAAID As Integer, strDeptName As String
 
intAAID = Me![cboLookUp].Column(0)
strDeptName = Me![cboLookUp].Column(1)
 
Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
 
rs.FindFirst "[AAID]=" & Me![cboLookUp].Column(0) & " AND [DepartmentName]='" & _
                         Me![cboLookUp].Column(1) & "'"
 
If rs.NoMatch Then
  Exit Sub
Else    'got us a match
  Me.Bookmark = rs.Bookmark
End If
 
rs.Close
 End Sub

[computerider]

Bullseye - works perfect - thank you for your effort!

[ADezii]

Bullseye - works perfect - thank you for your effort!

You're welcome.