Multiple comboboxes filtering each other

[Kevin via AccessMonster.com]

Hello all,

I have a form with four comboboxes that are set to draw their RowSource from
different fields of the same form (Patient Information). The four boxes are:
MR Number (the unique primary key of the table), Last Name, First Name, and
Date of Birth. I would like to be able to select a value for one of these
comboboxes and have that value update the others in the following manner:
entry of the MR Number populates the other three boxes; entry of Last Name
allows only selections associated with said name in the other three (i.e.
both John Smith and Patrick Smith exist; entry of "Smith" into Last Name will
have First Name only display options "Patrick" and "John". Continuous
selection would ideally narrow down the remaining comboboxes until all are
filled with the information from a unique patient). I am still relatively new
to Access, and have very limited familiarity with VBA, however, I am more
than willing to try any advice that you can offer.
Thank you very much in advance for your time!

 

[Guest]

If you have more than one name associated with the same ID number, then I
would say the first thing you should do is re-think the structure of your
tables. You need to get them properly normalized or you will always have
problems with your database. At a minimum, each patient should have a unique
ID number.

 

[Kevin via AccessMonster.com]

Beetle,

Thanks for your response, and sorry if I wasn't clear; each patient does have
their own unique id number. I would like to be able to search by last name as
well (in case the user does not have the record number available), and I
figured that some people might share a last name (which is why I included the
first name field as well). Basically, I am looking for a way to design a form
where I can search by either record number or name, narrow it down to exactly
the patient I had in mind (easy if I know their record number, not as easy if
I don't), and the link to a form (via a command button) to view the contact
informaiton for that patient. All of these aspects (id, names, contact info)
are derived from the same table. Perhaps you could suggest a more direct way
to accomplish this than the one I originally proposed? Thanks again,

Kevin

If you have more than one name associated with the same ID number, then I
would say the first thing you should do is re-think the structure of your
tables. You need to get them properly normalized or you will always have
problems with your database. At a minimum, each patient should have a unique
ID number.

Hello all,

[quoted text clipped - 12 lines]

than willing to try any advice that you can offer.
Thank you very much in advance for your time!

 

[Guest]

Ok, I misunderstood you. Here are a few options you could try. The first two
involve displaying the First and Last name in the same combo box, which would
probably be the easiest approach.

First, you could concantenate the First and Last name with a SQL statement
like the following;

SELECT [LastName] & "," & [FirstName] AS Expr1, tblPatients.LastName
FROM tblPatients
ORDER BY tblPatients.LastName;

This would display it as Last Name, First Name in one combo box, sorted by
Last Name. You will probably need to make your combo box wider in order to
see everything.

Second, you could display the names in two separate columns within one combo
box using a SQL statement like;

SELECT tblPatients.LastName, tblPatients.FirstName
FROM tblPatients
ORDER BY tblPatients.LastName;

This would display them in two separate columns, sorted by Last Name. If you
do this you will need to make sure the column count is set to 2 in the
properties of your combo box. Also, you will need to set the column widths
appropriately, plus make your combo box wider

Lastly, if you really want two have two separate fields for Last Name and
First Name, and are trying to set up a multi-parameter search form, Allen
Browne has a great example, with instructions and code samples, on his
website at this link;

http://allenbrowne.com/ser-62.html

However, if you are new to VBA, this option might seem rather complex. It
involves building a long SQL string statement to filer by whatever selections
are made in the boxes.

HTH

Beetle,

Thanks for your response, and sorry if I wasn't clear; each patient does have
their own unique id number. I would like to be able to search by last name as
well (in case the user does not have the record number available), and I
figured that some people might share a last name (which is why I included the
first name field as well). Basically, I am looking for a way to design a form
where I can search by either record number or name, narrow it down to exactly
the patient I had in mind (easy if I know their record number, not as easy if
I don't), and the link to a form (via a command button) to view the contact
informaiton for that patient. All of these aspects (id, names, contact info)
are derived from the same table. Perhaps you could suggest a more direct way
to accomplish this than the one I originally proposed? Thanks again,

Kevin

If you have more than one name associated with the same ID number, then I
would say the first thing you should do is re-think the structure of your
tables. You need to get them properly normalized or you will always have
problems with your database. At a minimum, each patient should have a unique
ID number.

Hello all,

[quoted text clipped - 12 lines]

than willing to try any advice that you can offer.
Thank you very much in advance for your time!

 

[Kevin via AccessMonster.com]

Beetle,

Thanks again for your response. I've taken a look at Allen Browne's page, and
I've tried to adopt his strategy for the multi-parameter search. I believe
that I've done all of the substitutions correctly, however, I ran into one
catch. I would like my search form to be seperate from my display form; i.e.
only the unbound search comboboxes are initially displayed, along with a
command button to "search." Clicking this option would bring up the search
results in a seperate form. At the end of Allen's code, he applies the string
that he developed with all of the "Where" statements as the filter of the
same form that the search items are on. Is there a way that I could change
this so that I could save the string and apply it as a filter to the form
that I'm opening? I have the coding to open the new form tacked on at the end
of Allen's adopted coding for the "OnClick" event of the command button, and
would like to apply the filter at this point. Thanks!

Kevin

Ok, I misunderstood you. Here are a few options you could try. The first two
involve displaying the First and Last name in the same combo box, which would
probably be the easiest approach.

First, you could concantenate the First and Last name with a SQL statement
like the following;

SELECT [LastName] & "," & [FirstName] AS Expr1, tblPatients.LastName
FROM tblPatients
ORDER BY tblPatients.LastName;

This would display it as Last Name, First Name in one combo box, sorted by
Last Name. You will probably need to make your combo box wider in order to
see everything.

Second, you could display the names in two separate columns within one combo
box using a SQL statement like;

SELECT tblPatients.LastName, tblPatients.FirstName
FROM tblPatients
ORDER BY tblPatients.LastName;

This would display them in two separate columns, sorted by Last Name. If you
do this you will need to make sure the column count is set to 2 in the
properties of your combo box. Also, you will need to set the column widths
appropriately, plus make your combo box wider

Lastly, if you really want two have two separate fields for Last Name and
First Name, and are trying to set up a multi-parameter search form, Allen
Browne has a great example, with instructions and code samples, on his
website at this link;

http://allenbrowne.com/ser-62.html

However, if you are new to VBA, this option might seem rather complex. It
involves building a long SQL string statement to filer by whatever selections
are made in the boxes.

HTH

Beetle,

[quoted text clipped - 23 lines]

 

[Kevin via AccessMonster.com]

After concatenating the fields for first and last name (using the first
method that Beetle described), can I reference these fields for use in the
filter (to select a unique record for the rest of the form)? Here is the code
(adopted from Allen Browne) for the on click event of my search command
button:

Private Sub cmdSearchPatientInfo_Click()

Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to
append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in
a JET query string.

If Not IsNull(Me.Combo2) Then
strWhere = strWhere & "([Medical Record Number] = """ & Me.Combo2 &
""") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.Combo6) Then
strWhere = strWhere & "([Last Name] Like ""*" & Me.Combo6 & "*"") AND
"
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "You have not entered any search criteria.", vbInformation,
"Whoops!"
DoCmd.Echo False
DoCmd.Close acForm, "editPatientInfo"
DoCmd.OpenForm "editPatientInfo"
DoCmd.Echo True
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
Me.Detail.Visible = True
End If
End Sub

Searching by medical record number works exactly as I would like, however,
searching by name simply brings up an empty detail section. The comboboxes
containing MR number (Combo2) and Name (Combo6) are contained in the header
of the form. Any help would be greatly appreciated.