CODEWITHSUNDEEP
vbams-accesscombobox
Amari Richardson
Amari Richardson

Reputation: 1

Go to Contact Form based on Name in ComboBox

I have a Contacts Table and a Contacts Extended query (screenshot attached). The ComboBox ([SearchContacts]) is unbound and is on my Contacts Details form. When I type in the name the ComboBox does not go to the contact form associated with the name.

The Contacts Table has columns: ID (Autonumber), First Name, Last Name, Department, Business Phone, Physical Address, City, State.

Contacts Extended Query

SELECT  IIf(IsNull([Last Name]),
            IIf(IsNull([First Name]),[Group Code],[First Name]),
            IIf(IsNull([First Name]),[Last Name],[Last Name] & "," & [First Name])) AS [File As],
        IIf(IsNull([Last Name]),
            IIf(IsNull([First Name]),[Group Code],[First Name]),
            IIf(IsNull([First Name]),[Last Name],[Last Name] & "," & [First Name])) AS [Contact Name], 
        Contacts.* 
FROM    Contacts 
ORDER BY    IIf(IsNull([Last Name]),
                IIf(IsNull([First Name]),[Group Code],[First Name]),
                IIf(IsNull([First Name]),[Last Name],[Last Name] & "," & [First Name])), 
            IIf(IsNull([Last Name]),
                IIf(IsNull([First Name]),[Group Code],[First Name]),
                IIf(IsNull([First Name]),[Last Name],[Last Name] & "," & [First Name]));

I am a novice. The closest code I found is below, but the form would not appear when the name was selected:

Private Sub Search_Exp_AfterUpdate()
    If search_SearchContact <> "" Then
    query = "SELECT * FROM Contacts WHERE (([Contacts].[Last Name])=[Forms].[Contacts Details].[SearchContacts])” 
    Me.RecordSource = query
    Me.Refresh
Else
    Me.RecordSource = "SELECT * FROM Contacts"
    Me.Refresh
End If

I can't figure out how to make the form appear and then requery so then I can type in another contact's name.

I tried VBA code, as the macro was insufficient. Also, when I used the Controls Wizard, the option "find a record on my form based on the value I selected in my combo box" does not appear.

Upvotes: 0

Views: 68

Answers (0)

Related Questions