Populate Form through a series of Combo Box selections

[Robertjb1]

First Post - New member - I've searched the discussions available to no avail. I have, however, learned a few things in doing so. (Great Site -- Thanks)
My issue is as follows:

Access 2003/WinXP

I have created a task tracking database. On the main form, I have placed two combo boxes. The first (cboContact) lists the user that the task is assigned to.

record source =

SELECT Contacts.ID, Contacts.[First Name] FROM Contacts ORDER BY Contacts.[First Name]; 
 

AfterUpdate Event =

Private Sub cboContact_AfterUpdate()
cboTask.Requery
End Sub 
 

The second (cboTask) lists only the tasks that the user in cboContact is responisble for.

record source =

SELECT Issues.ID, Issues.Title, Issues.[Assigned To] FROM Issues WHERE (((Issues.[Assigned To])=FORMS!Issues!cboContact)) ORDER BY Issues.Title; 
 

the AfterUpdate event == I'm having some battles with this one, as you'll see after the on GetFocus code

onGetFocus event =

Private Sub cboTask_GotFocus()
If Len(Trim(Nz(cboContact, "") & "")) = 0 Then
MsgBox "Please Specify Person Task Assigned To First"
cboContact.SetFocus
Else
cboTask.Requery
End If
End Sub
 

The form has multiple fields that I want to populate based on cboTasks choice. My combo boxes work. The form, however does nothing once the second box is populated. I've tried [Forms]![Issues]![cboTask] in the criteria of the query that is the forms record source. That doesn't work. I've tried the following code on the AfterUpdate event on cboTask and it doesn't work.

Private Sub cboTask_AfterUpdate()
 
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Title] = " & Me![cboTask]
If Not rs.EOF Then Me.Bookmark = 
rs.Bookmark
 
End Sub 
 

My tables are set up as follows:
tblContacts
ID - Autonumber
First Name - Txt ( I have the full name in this field )

tblIssues
ID-Autonumber -PK
Title - Txt
Assigned To - Nbr (This has a one to many relationship from Contacts (ID))
Opened By - Nbr
Opened Date - Date/Time
Status - Txt
Category - Txt
etc, etc ... about 10 other fields

I'm going in circles trying to figure this one out. I may be right on top of it. Most likely not.

Thank you in advance for your reply.

[MMcCarthy]

Try this ...

Private Sub cboTask_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Title] = '" & Me![cboTask] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 
End Sub
 

[Robertjb1]

Thanks for your reply. I'll try that solution.

I did get it working using this one.

Private Sub cboTask_AfterUpdate()
On Error Resume Next
 
    cboTask.SetFocus
    If cboTask.Value > 0 Then
        Me.Filter = "[ID]=" & cboTask.Value
        Me.FilterOn = True
    End If
 
End Sub

Private Sub Form_Load()
    Me.Filter = "[ID]=0"
End Sub

Private Sub cboTask_GotFocus()
If Len(Trim(Nz(cboContact, "") & "")) = 0 Then
MsgBox "Please Specify Person Task Assigned To First"
cboContact.SetFocus
Else
cboTask.Requery
End If
End Sub