how to Find a record on my form based on the value I selected in my combo box

Posted on 2014-08-09
Last Modified: 2014-08-09
I placed a combo box ( Combo31 ) on form Causal Factors (Causal_Factors)  and I am trying to have the combo box “Find a record on my form based on the value I selected in my combo box”. The values are the field “INCIDENT TITLE:”  in the “1-Incident-tbl” table.
When I go to Add Existing Field the “INCIDENT TITLE:”  field is not listed.

I have uploaded the DB twice. Once with no ZIP and one that is zzipped
Question by:cssc1
    LVL 31

    Expert Comment

    The combo box should be unbound, and have a row source that is the same as the form, or a subset of it.  Usually the first column is an ID field (set its width to zero if you don't want to see it in the drop-down list), and the 2nd column shows something meaningful for selection.  Here is some standard code:
    Private Sub cboSelect_AfterUpdate()
    'Created by Helen Feddema 29-Apr-2011
    'Last modified by Helen Feddema 29-Apr-2011
    On Error GoTo ErrorHandler
       Dim strSearch As String
       'For text IDs
       strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
          & Chr$(39)
       'For numeric IDs
       strSearch = "[______ID] = " & Me.ActiveControl.Value
       'Find the record that matches the control
       Me.Recordset.FindFirst strSearch
       Exit Sub
       MsgBox "Error No: " & Err.Number _
          & " in " & Me.ActiveControl.Name & " procedure; " _
          & "Description: " & Err.Description
       Resume ErrorHandlerExit
    End Sub

    Open in new window


    Author Comment

       I did all that I know about the code you gave me. See the attached.
    LVL 31

    Accepted Solution

    I modified the combo box AfterUpdate event as needed, but it won't work with Incident_ID, because that is not the key field for the table.  You need to use the key field, which for this table is Event_ID, so I used that instead.  I also put this field on the form, since you have an Event_Description field filled with data that is misleading (it says "this is event no. 3" for Event_ID 1, and so forth, which is confusing).  The modified database is attached.
    LVL 31

    Expert Comment

    Also, I suggest giving all controls meaningful prefixes.  My freeware LNC Rename add-in does this semi-automatically; you can download the version for your Office version from one of these links:

    Access 2000 - 2003:

    Access 2007 - 2013:

    Author Comment

    Thanks Helen.

    I appreciate your help and advice.

    Author Closing Comment

    Thanks Helen.

    I appreciate your help and advice.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now