We help IT Professionals succeed at work.

Check out this week's podcast, "Dairy Farms to Databases: Community's Hand in Technology"Listen Now

x

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

cssc1
cssc1 asked
on
1,618 Views
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
Root-Cause-Analysis-for-Tunnel-Construct
Root-Cause-Analysis-for-Tunnel-Construct
Comment
Watch Question

CERTIFIED EXPERT
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
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.