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

Handling Combo Boxes

P: 15
I am developing an application for a hospital which provides Outpatient billing information. I have created appropriate tables required. I have to develop a client server system where from multiple systems differnt people will enter data into the forms relted to the patients. The details of all the patients and their visits are stored in the MSAccess database and retrieved accordingly upon request and reports will be created.
I started creating forms and I have to create a combo box which provides following functionality:

---when you start typing the name, the drop down menu would appear offering options including

<new patient> : selecting this option would take you to the new patient registration form

a list of names in which the information you typed is contained in the last name of all patients in the database

a list of names in which the information you typed is contained in the first names of all patients

a list of names in which the Soundex of the letters you typed matches in the Soundex of either the first or last names


For example, you type in "Smith" and the drop menu looks something like:

<new patient>

Smith, John

Blackwell, Smithson

Smythe, Robert



Selecting on the drop down menu should return the auto number index of that patient in the "patients" table.

How to handle this kind of dynalic information as depending on the user typed text in combo box the patients with the same name if exist in database should be listed.

I am grouping this kind of functionality so that it will be easy to enter new patients data with minimum clicks.

It would be great if you can provide some suggestions on this .

Thank you,
Pdesh3.
May 7 '07 #1
Share this Question
Share on Google+
19 Replies


JConsulting
Expert 100+
P: 603
Going to have to ask some questions to get this one going.

1st - Combo boxes have a fixed "selection" behavior. You can start to type in it, and it "auto-corrects" the entry to assist you in finding the value you're entering. This logic is based solely on a character match. If you type in A, it goes to whatever name begins with A. If you follow it by a D, then it goes to the first name that starts with AD...etc.

That being said...what you're asking isn't really possible using a combo box.

If your requirements allow it, a textbox can be used to type in. A button can be used to launch your "search".

Most of what you want is easy enough. The part about smith returning smyth, that's a little more involved and requires character replacement, and a lot of overhead "guessing" when it comes to replacing patterns.

Let us know how you wish to proceed.
J
May 8 '07 #2

Denburt
Expert 100+
P: 1,356
Here is an article to help you with a search form:
http://www.thescripts.com/forum/thread590551.html

Adding a new record and all of your requests are possible the question is how savvy are you with MS Access? Do you know how to do any coding in VBA?
May 8 '07 #3

ADezii
Expert 5K+
P: 8,638
I am developing an application for a hospital which provides Outpatient billing information. I have created appropriate tables required. I have to develop a client server system where from multiple systems differnt people will enter data into the forms relted to the patients. The details of all the patients and their visits are stored in the MSAccess database and retrieved accordingly upon request and reports will be created.
I started creating forms and I have to create a combo box which provides following functionality:

---when you start typing the name, the drop down menu would appear offering options including

<new patient> : selecting this option would take you to the new patient registration form

a list of names in which the information you typed is contained in the last name of all patients in the database

a list of names in which the information you typed is contained in the first names of all patients

a list of names in which the Soundex of the letters you typed matches in the Soundex of either the first or last names


For example, you type in "Smith" and the drop menu looks something like:

<new patient>

Smith, John

Blackwell, Smithson

Smythe, Robert



Selecting on the drop down menu should return the auto number index of that patient in the "patients" table.

How to handle this kind of dynalic information as depending on the user typed text in combo box the patients with the same name if exist in database should be listed.

I am grouping this kind of functionality so that it will be easy to enter new patients data with minimum clicks.

It would be great if you can provide some suggestions on this .

Thank you,
Pdesh3.
I do believe I solved the most difficult part of your question which was basically - How do I dynamically build a RowSource for a Combo Box based on partial First and Last Names and include some sort of SoundEx capability into the mix? I'll post the code and logic, then let you decide. My sample data was based on the following:

Table Name:
tblEmployeesPFD
Last Name Field: LastName
First Name Field: FirstName
Combo Box Name: cboNames
Column Count: 2
Bound Column: 1
Initial Row Source Type: Table/Query
Initial Row Source: SELECT tblEmployeePFD.LastName, tblEmployeePFD.FirstName FROM tblEmployeePFD Order By tblEmployeePFD.LastName;
  1. Set the Auto Expand property of the Combo Box (cboNames) to No.
  2. We will be using the Text, NOT Value, Property of the Combo Box to build the RowSource with each character entered.
  3. The following code must go in the Change() Event of cboNames, it can go no where else.
  4. The code will generate a RowSource based on each character(s) entered into the Text Box portion of the cboNames using the Like Operator within an SQL String and automatically update the Text Box.
  5. I've had very little time to completely test the code, so let me know how you make out. There are also other Moderators/Experts more proficient in SQL than I, who can guide you through the rest of the operation.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboNames_Change()
  2.   Me![cboNames].RowSource = "SELECT tblEmployeePFD.LastName, tblEmployeePFD.FirstName FROM tblEmployeePFD " _
  3.                           & "WHERE tblEmployeePFD.LastName Like '*" & Me![cboNames].Text & "*'" & " Or " _
  4.                           & "tblEmployeePFD.FirstName Like '*" & Me![cboNames].Text & "*'" _
  5.                           & "Order By tblEmployeePFD.LastName;"
  6. End Sub
May 8 '07 #4

JConsulting
Expert 100+
P: 603
I do believe I solved the most difficult part of your question which was basically - How do I dynamically build a RowSource for a Combo Box based on partial First and Last Names and include some sort of SoundEx capability into the mix? I'll post the code and logic, then let you decide. My sample data was based on the following:

Table Name:
tblEmployeesPFD
Last Name Field: LastName
First Name Field: FirstName
Combo Box Name: cboNames
Column Count: 2
Bound Column: 1
Initial Row Source Type: Table/Query
Initial Row Source: SELECT tblEmployeePFD.LastName, tblEmployeePFD.FirstName FROM tblEmployeePFD Order By tblEmployeePFD.LastName;
  1. Set the Auto Expand property of the Combo Box (cboNames) to No.
  2. We will be using the Text, NOT Value, Property of the Combo Box to build the RowSource with each character entered.
  3. The following code must go in the Change() Event of cboNames, it can go no where else.
  4. The code will generate a RowSource based on each character(s) entered into the Text Box portion of the cboNames using the Like Operator within an SQL String and automatically update the Text Box.
  5. I've had very little time to completely test the code, so let me know how you make out. There are also other Moderators/Experts more proficient in SQL than I, who can guide you through the rest of the operation.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboNames_Change()
  2.   Me![cboNames].RowSource = "SELECT tblEmployeePFD.LastName, tblEmployeePFD.FirstName FROM tblEmployeePFD " _
  3.                           & "WHERE tblEmployeePFD.LastName Like '*" & Me![cboNames].Text & "*'" & " Or " _
  4.                           & "tblEmployeePFD.FirstName Like '*" & Me![cboNames].Text & "*'" _
  5.                           & "Order By tblEmployeePFD.LastName;"
  6. End Sub

To add to this...since there will be a followup on click event to use to filter the form...the rowsource should also include the Employee ID in the rowsource. It should be the bound column.

In the after update event, you have the option to either filter a current form's recordset, or use the combo box itself as the criteria for the form's recordset.

Here to help.
J
May 8 '07 #5

ADezii
Expert 5K+
P: 8,638
To add to this...since there will be a followup on click event to use to filter the form...the rowsource should also include the Employee ID in the rowsource. It should be the bound column.

In the after update event, you have the option to either filter a current form's recordset, or use the combo box itself as the criteria for the form's recordset.

Here to help.
J
Good point JConsulting, just wanted to get the heavy stuff out of the way first. (LOL).
May 8 '07 #6

P: 15
Thank you for the suggestions.

Here I made few changes.

I have created a form where one text box and two command buttons named Search and Clear. Under all these fields I am also including a subform which displays all the values stored in Patient table.
When I enter any word in the name text box and click enter then the values in sub form only display the names of the patient whose first name or last name matches with the given name. When button Clear is hit ( in future I am planning to make it cancel button , so that to cancel the operation ) textbox becomes empty.
I have written code for implementing this but finding problems when I hit the search button nothing is done. Can you please look at the code below and tell me the changes.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub btnClear_Click()
  5.  
  6.  
  7. Me.txtName = ""
  8.  
  9.  
  10.  
  11. End Sub
  12.  
  13. Private Sub btnSearch_Click()
  14.  
  15. Me.frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails " & BuildFilter
  16.  
  17. Me.frmSubPatient.Requery
  18. End Sub
  19.  
  20.  
  21. Private Sub Form_Load()
  22.  
  23.  
  24. btnClear_Click
  25.  
  26. End Sub
  27.  
  28. Private Function BuildFilter() As Variant
  29. Dim varWhere As Variant
  30.  
  31.  
  32. varWhere = Null
  33.  
  34. If Me.txtName > "" Then
  35. varWhere = varWhere & "[FirstName] LIKE """ & Me.txtName & "*"" AND "
  36. End If
  37.  
  38. If Me.txtName > "" Then
  39. varWhere = varWhere & "[LastName] LIKE """ & Me.txtName & "*"" AND "
  40. End If
  41.  
  42.  
  43. If IsNull(varWhere) Then
  44. varWhere = ""
  45. Else
  46. varWhere = "WHERE " & varWhere
  47.  
  48. ' strip off last "AND" in the filter
  49. If Right(varWhere, 5) = " AND " Then
  50. varWhere = Left(varWhere, Len(varWhere) - 5)
  51. End If
  52. End If
  53.  
  54. BuildFilter = varWhere
  55.  
  56.  
  57.  
  58. End Function
  59.  
I am also planning to display a pop up message when new patient name is entered in the text box. The pop message like " New patient !! do you want to enter the details?" when OK button is pressed new patient registration form should be displayed. Can we implement this?

Thank you,
Pdesh3
May 9 '07 #7

Denburt
Expert 100+
P: 1,356
Use " or " instead of " and " unless you want smith to be in both the first and last name.
May 9 '07 #8

P: 15
Use " or " instead of " and " unless you want smith to be in both the first and last name.
When I execute the code it is saying "Method or Datamember not found" for
btnSearch_Click() method
May 9 '07 #9

JConsulting
Expert 100+
P: 603
Thank you for the suggestions.

Here I made few changes.

I have created a form where one text box and two command buttons named Search and Clear. Under all these fields I am also including a subform which displays all the values stored in Patient table.
When I enter any word in the name text box and click enter then the values in sub form only display the names of the patient whose first name or last name matches with the given name. When button Clear is hit ( in future I am planning to make it cancel button , so that to cancel the operation ) textbox becomes empty.
I have written code for implementing this but finding problems when I hit the search button nothing is done. Can you please look at the code below and tell me the changes.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub btnClear_Click()
  5.  
  6.  
  7. Me.txtName = ""
  8.  
  9.  
  10.  
  11. End Sub
  12.  
  13. Private Sub btnSearch_Click()
  14.  
  15. Me.frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails " & BuildFilter
  16.  
  17. Me.frmSubPatient.Requery
  18. End Sub
  19.  
  20.  
  21. Private Sub Form_Load()
  22.  
  23.  
  24. btnClear_Click
  25.  
  26. End Sub
  27.  
  28. Private Function BuildFilter() As Variant
  29. Dim varWhere As Variant
  30.  
  31.  
  32. varWhere = Null
  33.  
  34. If Me.txtName > "" Then
  35. varWhere = varWhere & "[FirstName] LIKE """ & Me.txtName & "*"" AND "
  36. End If
  37.  
  38. If Me.txtName > "" Then
  39. varWhere = varWhere & "[LastName] LIKE """ & Me.txtName & "*"" AND "
  40. End If
  41.  
  42.  
  43. If IsNull(varWhere) Then
  44. varWhere = ""
  45. Else
  46. varWhere = "WHERE " & varWhere
  47.  
  48. ' strip off last "AND" in the filter
  49. If Right(varWhere, 5) = " AND " Then
  50. varWhere = Left(varWhere, Len(varWhere) - 5)
  51. End If
  52. End If
  53.  
  54. BuildFilter = varWhere
  55.  
  56.  
  57.  
  58. End Function
  59.  
I am also planning to display a pop up message when new patient name is entered in the text box. The pop message like " New patient !! do you want to enter the details?" when OK button is pressed new patient registration form should be displayed. Can we implement this?

Thank you,
Pdesh3
If I may?

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2. Dim strWhere As String
  3. strWhere = BuildFilter
  4. Me.frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails" & strWhere
  5. Me.frmSubPatient.Form.Requery
  6. End Sub
  7. Private Sub Form_Load()
  8. 'btnClear_Click  ' This is not needed. As long as your text box is unbound, it will be blank when the form opens
  9. End Sub
  10. Public Function BuildFilter() As String
  11. BuildFilter = ""
  12. If Nz(Me.txtName, "") <> "" Then BuildFilter = " WHERE [FirstName] LIKE *'" & Me.txtName & "'* OR [LastName] LIKE *'" & Me.txtName & "'*;"
  13. End Function
  14.  
May 9 '07 #10

P: 15
If I may?

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2. Dim strWhere As String
  3. strWhere = BuildFilter
  4. Me.frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails" & strWhere
  5. Me.frmSubPatient.Form.Requery
  6. End Sub
  7. Private Sub Form_Load()
  8. 'btnClear_Click  ' This is not needed. As long as your text box is unbound, it will be blank when the form opens
  9. End Sub
  10. Public Function BuildFilter() As String
  11. BuildFilter = ""
  12. If Nz(Me.txtName, "") <> "" Then BuildFilter = " WHERE [FirstName] LIKE *'" & Me.txtName & "'* OR [LastName] LIKE *'" & Me.txtName & "'*;"
  13. End Function
  14.  
I tried this also , but giving the same error as "Method or Data Member not found" for btnSearch_Click() method
May 9 '07 #11

ADezii
Expert 5K+
P: 8,638
Thank you for the suggestions.

Here I made few changes.

I have created a form where one text box and two command buttons named Search and Clear. Under all these fields I am also including a subform which displays all the values stored in Patient table.
When I enter any word in the name text box and click enter then the values in sub form only display the names of the patient whose first name or last name matches with the given name. When button Clear is hit ( in future I am planning to make it cancel button , so that to cancel the operation ) textbox becomes empty.
I have written code for implementing this but finding problems when I hit the search button nothing is done. Can you please look at the code below and tell me the changes.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub btnClear_Click()
  5.  
  6.  
  7. Me.txtName = ""
  8.  
  9.  
  10.  
  11. End Sub
  12.  
  13. Private Sub btnSearch_Click()
  14.  
  15. Me.frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails " & BuildFilter
  16.  
  17. Me.frmSubPatient.Requery
  18. End Sub
  19.  
  20.  
  21. Private Sub Form_Load()
  22.  
  23.  
  24. btnClear_Click
  25.  
  26. End Sub
  27.  
  28. Private Function BuildFilter() As Variant
  29. Dim varWhere As Variant
  30.  
  31.  
  32. varWhere = Null
  33.  
  34. If Me.txtName > "" Then
  35. varWhere = varWhere & "[FirstName] LIKE """ & Me.txtName & "*"" AND "
  36. End If
  37.  
  38. If Me.txtName > "" Then
  39. varWhere = varWhere & "[LastName] LIKE """ & Me.txtName & "*"" AND "
  40. End If
  41.  
  42.  
  43. If IsNull(varWhere) Then
  44. varWhere = ""
  45. Else
  46. varWhere = "WHERE " & varWhere
  47.  
  48. ' strip off last "AND" in the filter
  49. If Right(varWhere, 5) = " AND " Then
  50. varWhere = Left(varWhere, Len(varWhere) - 5)
  51. End If
  52. End If
  53.  
  54. BuildFilter = varWhere
  55.  
  56.  
  57.  
  58. End Function
  59.  
I am also planning to display a pop up message when new patient name is entered in the text box. The pop message like " New patient !! do you want to enter the details?" when OK button is pressed new patient registration form should be displayed. Can we implement this?

Thank you,
Pdesh3
  1. Remove the Reference to the BuildFilter() Function on the RecordSource line for your Sub-Form and see if you get an Error - if not, then the problem lies within the Function.
  2. Make a slight change in syntax.
    Expand|Select|Wrap|Line Numbers
    1. If Me.txtName > "" Then
    2. varWhere = varWhere & "[FirstName] LIKE '" & Me.txtName & "*'" AND "
May 9 '07 #12

P: 15
  1. Remove the Reference to the BuildFilter() Function on the RecordSource line for your Sub-Form and see if you get an Error - if not, then the problem lies within the Function.
  2. Make a slight change in syntax.
    Expand|Select|Wrap|Line Numbers
    1. If Me.txtName > "" Then
    2. varWhere = varWhere & "[FirstName] LIKE '" & Me.txtName & "*'" AND "
Hi,
Thank you for the help , now it is working. But I got one more problem that when I close the form and open again it is showing previous execution results instead of showing all Patients' details in the Sub form below.
May 9 '07 #13

Denburt
Expert 100+
P: 1,356
Use the "on close" event to reset the recordsource.

Expand|Select|Wrap|Line Numbers
  1. Me!frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails "
  2.  
May 9 '07 #14

P: 15
Use the "on close" event to reset the recordsource.

Expand|Select|Wrap|Line Numbers
  1. Me!frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails "
  2.  
I tried it, it is not working. It is just displaying only one row of the table in sub form :-(
May 9 '07 #15

Denburt
Expert 100+
P: 1,356
I tried it, it is not working. It is just displaying only one row of the table in sub form :-(
My apologies that should be the "On Open" event... I have been stuck in the "On Close" event all day lol.
May 9 '07 #16

P: 15
My apologies that should be the "On Open" event... I have been stuck in the "On Close" event all day lol.
I tried this also. Its not working. See the code below:

Private Sub Form_OnOpen()


Me.frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails"

End Sub
May 9 '07 #17

Denburt
Expert 100+
P: 1,356
I tried this also. Its not working. See the code below:
Me.frmSubPatient.Requery
Private Sub Form_OnOpen()


Me.frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails"

End Sub
You will have to requery the form to see the results

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_OnOpen()
  2.  
  3.  
  4.     Me.frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails"
  5.     Me.frmSubPatient.Requery
  6. End Sub
May 9 '07 #18

P: 15
You will have to requery the form to see the results

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_OnOpen()
  2.  
  3.  
  4.     Me.frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails"
  5.     Me.frmSubPatient.Requery
  6. End Sub
I tried this:

Private Sub Form_OnOpen()


Me.frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails"
Me.frmSubPatient.Form.Requery

End Sub

Its not working :-(
May 10 '07 #19

Denburt
Expert 100+
P: 1,356
Have you tried to insert a breakpoint (F9), then step through the procedure just to verify that the open event is working as expected? It may not even be calling the open event for some reason. I just setup up a similar scenario and had no problems, I didn't even need to requery just set the recordsource and I was up and running as expected.
May 11 '07 #20

Post your reply

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