Go To Record

[eacollie]

I'm stuck and would appreciate any help!

I have a main form based on a table of events. On this main form, I have a subform displaying the contact information for each event. The form and subform are linked by [contact] in the event table (long) and [ID] in the contacts table (also long).

The contact information is contained in two separate databases and I'm pulling the information from each database to populate the subform (and a combo box "cboContact") in the on_current event of the main form as follows:

Select Case Me.Campus
Case "Main Campus"
    Me.subfrmContacts.Form.RecordSource = "SELECT [CNOS] AS ID, " & _
            "[ContactLastName] AS LastName, [ContactFirstName] AS FirstName, " & _
            "[ContactTitle] AS Title, ([ContactLastName] " & _
            " & "", "" & [ContactTitle] & "" "" & [ContactFirstName]) AS CName, " & _
            "IIf (IsNull([ContactAddress]), "", "", [ContactAddress] & "", "" & [ContactCity] & "", "" & [ContactState]) AS Address1, " & _
            "[ContactCity] AS City, [ContactState] AS State, " & _
            "[ContactZip] AS Zip, " & _
            "[ContactHomePhone] AS PhoneNumber, [ContactFax] AS FaxNumber, " & _
            "[ContactWorkPhone] AS WorkPhone, [ContactMobil] AS CellPhone, " & _
            "[ContactEmail] AS EMail, [SpecialNotes] AS Notes, " & _
            "[UpdateParish] AS [Group] FROM tblMailingList;"
 
Me!subfrmContacts.Form!cboContact.RowSource = "SELECT [CNOS] As ID, ([ContactLastName] " & _
            " & "", "" & [ContactTitle] & "" "" & [ContactFirstName]) AS CName FROM tblMailingList " & _
            "ORDER BY tblMailingList.ContactLastName, tblMailingList.ContactFirstName; "
 
Case "SecondCampus"
 
Me.subfrmContacts.Form.RecordSource = "SELECT [ID] , [LastName]  " & _
            " & "", "" & [Title] & "" "" & [FirstName] AS CName, " & _
            "IIf (IsNull([Address]), "" "",[Address] & "", "" & [City] & "" "" & [State]) As Address1, " & _
            " [PhoneNumber], [FaxNumber], [WorkPhone], [CellPhone], [EMail], [Notes], [Group] from Contacts;"
 
Me!subfrmContacts.Form!cboContact.RowSource = "SELECT [ID], [LastName]  " & _
            " & "", "" & [Title] & "" "" & [FirstName] AS CName FROM Contacts " & _
            "ORDER BY Contacts.LastName, Contacts.FirstName; "
    End Select

I believe this is all working fine, as the correct contact information is displayed in the subform when I navigate through the events and the correct information is displayed in the combobox when selected.

What isn't working is the after_update event of the combobox on the subform. I'm using the following:

    Dim RS As Object
 
    Set RS = Me.Recordset.Clone
    RS.FindFirst "[ID] = " & Str(Nz(Screen.ActiveControl, 0))
 
    Me.Bookmark = RS.Bookmark

It is not finding the record.