asked on

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

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
Root-Cause-Analysis-for-Tunnel-Construct
Root-Cause-Analysis-for-Tunnel-Construct

Helen Feddema

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

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

cssc1

ASKER

Helen,
I did all that I know about the code you gave me. See the attached.
Root-Cause-Analysis-for-Tunnel-Construct
Root-Cause-Analysis-for-Tunnel-Construct
AfterUpdate.png
Code-.jpg

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Helen Feddema

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:
http://www.helenfeddema.com/Files/code10.zip
http://en.wikipedia.org/wiki/Leszynski_naming_convention

Access 2007 - 2013:
http://www.helenfeddema.com/Files/code63.zip

cssc1

ASKER

Thanks Helen.

I appreciate your help and advice.

cssc1

ASKER

Thanks Helen.

I appreciate your help and advice.