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

cssc1
cssc1 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
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

Author

Commented:
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
Top Expert 2009
Commented:
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.
HBF-Modified-DB.zip
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2009

Commented:
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

Author

Commented:
Thanks Helen.

I appreciate your help and advice.

Author

Commented:
Thanks Helen.

I appreciate your help and advice.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial