By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,557 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Searching by unique ID text boxes or name combo boxes

100+
P: 114
I have an Access database of employees.
Each employee has a unique identification number called "BANNER_ID".
Each employee's last name and first name are in the database but are not unique (obviously).
The data is stored in a table named EMPLOYEE.

I want to create a search option whereby managers can search for an employee by "BANNER_ID"
by entering the number and pressing a command button named "Search".

I want to create an accompanying search option whereby managers can search for an employee
by selecting his or her last name from a combo box (LAST_NAME) and then first name from
another combo box (FIRST_NAME).
When a last name is selected, I would like the combo box of first names to narrow to only
first names that are associated with the selected last name.

I want both search options to be available.

I have been helped with the following code that searches for employees based on their
last name. However, I am having trouble adapting it to work with my new needs listed above.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch01_Click()
  2.     Dim strStudentRef As String
  3.     Dim strSearch As String
  4.  
  5. 'Check txtSearch01 for Null value or Nill Entry first.
  6.  
  7.     If IsNull(Me![txtSearch01]) Or (Me![txtSearch01]) = "" Then
  8.         MsgBox "Please enter a last name!", vbOKOnly, "Martin DH Message"
  9.         Me![txtSearch01].SetFocus
  10.     Exit Sub
  11. End If
  12. '---------------------------------------------------------------
  13.  
  14. 'Performs the search using value entered into txtSearch01
  15. 'and evaluates this against values in LAST_NAME
  16.  
  17.     DoCmd.ShowAllRecords
  18.     DoCmd.GoToControl ("LAST_NAME")
  19.     DoCmd.FindRecord Me!txtSearch01
  20.  
  21.     LAST_NAME.SetFocus
  22.     strStudentRef = LAST_NAME.Text
  23.     txtSearch01.SetFocus
  24.     strSearch = txtSearch01.Text
  25.  
  26. 'If matching record found sets focus in LAST_NAME and shows msgbox
  27. 'and clears search control
  28.  
  29.     If strStudentRef = strSearch Then
  30.         MsgBox "Match Found For: " & strSearch, , "Martin DH Message"
  31.         LAST_NAME.SetFocus
  32.         txtSearch01 = ""
  33.  
  34.     'If value not found sets focus back to txtSearch01 and shows msgbox
  35.         Else
  36.             MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
  37.             , "Martin DH Message"
  38.             txtSearch01.SetFocus
  39.     End If
  40. End Sub
  41.  
I would appreciate any help available in developing these search options. Thank you!

Martin DH
Mar 22 '07 #1
Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,324
The Cascading Combo/List Boxes tutorial will help you set up those comboboxes.
Mar 23 '07 #2

100+
P: 114
I have looked over the combo boxes tutorial as well as some other information. I now have a combo box that limits the options in the second combo box, both of which display the selected results in a list (exactly as I wanted it - thank you!). Now, I want to be able to select a record in the list and thereby open another form that contains the detailed information of the record. Here is the code included in my "search" subform:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. On Error Resume Next
  3.    ResetCombos Me
  4. End Sub
  5. Private Sub cmdReset_Click()
  6. On Error Resume Next
  7.    ResetCombos Me
  8. End Sub
  9. Private Sub cboLAST_NAME_AfterUpdate()
  10. On Error Resume Next
  11.    SetListBox Me.lstList, Me.[cboLAST_NAME], Me.[cboFIRST_NAME]
  12. End Sub
  13. Private Sub cboFIRST_NAME_AfterUpdate()
  14. On Error Resume Next
  15.    SetListBox Me.lstList, Me.[cboFIRST_NAME]
  16. End Sub
  17. Private Sub lstList_Click()
  18. On Error GoTo Err_cmdOpen_Click
  19.  
  20. DoCmd.OpenForm "Search/Edit Employee Files", , , "BANNER_ID=" & Me.BANNER_ID
  21.  
  22. Exit_cmdOpen_Click:
  23.     Exit Sub
  24.  
  25. Err_cmdOpen_Click:
  26.     MsgBox Err.Description
  27.     Resume Exit_cmdOpen_Click
  28.  
  29. End Sub
I do believe my problem is in the DoCmd.OpenForm code - still new at this and just not sure how to make it work.

Thank you for your help!

Martin DH
Apr 7 '07 #3

Rabbit
Expert Mod 10K+
P: 12,324
If Banner_ID is text you have to surround it with quotes, so:
Expand|Select|Wrap|Line Numbers
  1. "'" & Me.Banner_ID & "'"
Apr 7 '07 #4

100+
P: 114
Ha, of course, quotes - thank you.
Everything is working until I select a record from the list box: at this point a dialog box opens asking me to enter a parameter value for Me!BANNER_ID (the text unique ID for these records). If I do not, the record's detail is not retrieved; if I do enter the correct BANNER_ID, the record's detail is pulled up on the opened form.

So, how do I get past this dialog box (ie selecting a record from the list box opens the record's detail immediately).

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub Form_Load()
  4. On Error Resume Next
  5.    ResetCombos Me
  6. End Sub
  7. Private Sub cmdReset_Click()
  8. On Error Resume Next
  9.    ResetCombos Me
  10. End Sub
  11. Private Sub cboLAST_NAME_AfterUpdate()
  12. On Error Resume Next
  13.    SetListBox Me.lstList, Me.[cboLAST_NAME]
  14. End Sub
  15. Private Sub lstList_Click()
  16. On Error GoTo Err_cmdOpen_Click
  17.  
  18.     Dim stDocName As String
  19.     Dim stLinkCriteria As String
  20.  
  21.     stDocName = "Search/Edit Employee Files"
  22.  
  23.     stLinkCriteria = "[BANNER_ID]=" & "Me![BANNER_ID]"
  24.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  25.  
  26. Exit_cmdOpen_Click:
  27.     Exit Sub
  28.  
  29. Err_cmdOpen_Click:
  30.     MsgBox Err.Description
  31.     Resume Exit_cmdOpen_Click
  32.  
  33. End Sub
Thank you!
Apr 10 '07 #5

Rabbit
Expert Mod 10K+
P: 12,324
You misunderstood what I meant about enclosing Banner_ID in quotes. Take a closer look at my previous post.
Apr 10 '07 #6

100+
P: 114
So, now my code is as below; I thought I saw what you were getting at with the quotes. When I select the record from the list box (created by the combo box selection) it simply leaves the first record on screen.

Expand|Select|Wrap|Line Numbers
  1.     Dim stDocName As String
  2.     Dim stLinkCriteria As String
  3.  
  4.     stDocName = "Search/Edit Employee Files"
  5.  
  6.     stLinkCriteria = "[BANNER_ID]=" & "'" & Me![BANNER_ID] & "'"
  7.     DoCmd.OpenForm stDocName, , , stLinkCriteria
Thanks for your help Rabbit.
Apr 17 '07 #7

Rabbit
Expert Mod 10K+
P: 12,324
Not a problem.
Apr 17 '07 #8

100+
P: 114
Do you have any solution to the new issue, though? When I select the record from the list box (created by the combo box selection) it simply leaves the first record in the database on screen instead of opening the selected record.

Expand|Select|Wrap|Line Numbers
  1.     Dim stDocName As String
  2.     Dim stLinkCriteria As String
  3.  
  4.     stDocName = "Search/Edit Employee Files"
  5.  
  6.     stLinkCriteria = "[BANNER_ID]=" & "'" & Me![BANNER_ID] & "'"
  7.     DoCmd.OpenForm stDocName, , , stLinkCriteria
martin dh
Apr 19 '07 #9

Rabbit
Expert Mod 10K+
P: 12,324
Do you have any solution to the new issue, though? When I select the record from the list box (created by the combo box selection) it simply leaves the first record in the database on screen instead of opening the selected record.

Expand|Select|Wrap|Line Numbers
  1.     Dim stDocName As String
  2.     Dim stLinkCriteria As String
  3.  
  4.     stDocName = "Search/Edit Employee Files"
  5.  
  6.     stLinkCriteria = "[BANNER_ID]=" & "'" & Me![BANNER_ID] & "'"
  7.     DoCmd.OpenForm stDocName, , , stLinkCriteria
martin dh
It seems then you don't want to use the Banner_ID from the form but the Banner_ID from the listbox. These are not the same thing.
Apr 19 '07 #10

100+
P: 114
I'm sorry; I don't understand why the BANNER_IDs would not be the same thing. As a user searches for an employee via the combo boxes, the matching records appear in the list box, the user should then be able to click on a record, which opens the record in the form below so that the user can review/edit the record. This happens because when a record is selected, the db pulls the full record and displays it based on the BANNER_ID that matches one in the EMPLOYEE table. BANNER_ID is the unique id for the employee records. Could you explain where my logic is falling short and if there is a way to accomplish this task? Thanks so much!

Martin DH
Apr 24 '07 #11

Rabbit
Expert Mod 10K+
P: 12,324
I'm sorry; I don't understand why the BANNER_IDs would not be the same thing. As a user searches for an employee via the combo boxes, the matching records appear in the list box, the user should then be able to click on a record, which opens the record in the form below so that the user can review/edit the record. This happens because when a record is selected, the db pulls the full record and displays it based on the BANNER_ID that matches one in the EMPLOYEE table. BANNER_ID is the unique id for the employee records. Could you explain where my logic is falling short and if there is a way to accomplish this task? Thanks so much!

Martin DH
If the form is bound to a row source, i.e. a table, query, etc., then it has access to those fields for whichever record it is on. So Me.FieldName refers to the field of that record. A bound control is linked to a field while an unbound control is not. So if the control named ControlA is bound to a field named FieldA, there are two ways to access and change that value. One is through the field name. Me.FieldA, the other is through the control, Me.ControlA. A listbox, can only return one value and can only be bound to one field. A listbox only displays values.

So let's say you have a form, FormA, that is bound to table TableA with 5 records, and it has a field called ID.

Let's also say you have a listbox called ListA which displays all the IDs from TableA.

So, when you open the form and it starts on the first record, the form's ID value is the first ID value. When you click a different record on that list, it does NOT change the form's ID value, it changes the value that the List returns.
Apr 24 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.