RE: Multiple comboboxes filtering each other

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Beetle wrote:
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]
than willing to try any advice that you can offer.
Thank you very much in advance for your time!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200707/1

.

Relevant Pages

  • RE: Multiple comboboxes filtering each other
    ... you could concantenate the First and Last name with a SQL statement ... you could display the names in two separate columns within one combo ... This would display them in two separate columns, ... and sorry if I wasn't clear; each patient does have ...
    (microsoft.public.access.forms)
  • Re: SQL Question
    ... to display "X" if the field is True, ... > SQL statements to display conditional output. ... > control output based on the result of a test condition. ... > As a SQL statement for a list box, ...
    (microsoft.public.access.formscoding)
  • Re: Get Values SQL in table
    ... > data set in the text box, ... > can take the results of the query and display them in columns. ... > is only capable of displaying the actual text string, ... within the SQL statement or the table that is the data source. ...
    (microsoft.public.access.modulesdaovba)
  • Re: How do I select a certain number of records randomly from a ta
    ... No, it is supposed to be a SQL statement, although I forgot to put the field ... You don't indicate where you want to display these questions when you are ... If you have a database of addresses, ... Dale ...
    (microsoft.public.access.queries)
  • Multiple fields in a single textbox
    ... My database stores City and State in two separate columns, ... to display them on my form as a single field. ... each field into my forn as a separate textbox. ... Properties window and I'd be done, but this causes the value in my ...
    (microsoft.public.dotnet.languages.vb)