Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-08-09
6
Medium Priority
?
1,215 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
0
Comment
Question by:cssc1
  • 3
  • 3
6 Comments
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40250827
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

0
 

Author Comment

by:cssc1
ID: 40250948
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
0
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 40251011
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
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40251016
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
0
 

Author Comment

by:cssc1
ID: 40251041
Thanks Helen.

I appreciate your help and advice.
0
 

Author Closing Comment

by:cssc1
ID: 40251043
Thanks Helen.

I appreciate your help and advice.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

577 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