Filter Combo Box Listing

  • Thread starter Thread starter alhotch
  • Start date Start date
A

alhotch

I have a form with the first four fields named "FirstName", "LastName",
Phone", and EmailAddress". The field "EmaiAddress" is a combo box. The row
source for this CBO is a table named Email.

The table Email has five fields: "EmailID (PK), "FirstName", "LastName",
"FullName" (first and last name concatenated), and "EmaiAddress".

When I first open the form to enter NEW information, I key in the FirstName,
LastName, and Phone fields. I then tab to the fourth field called
EmailAddress (the CBO). I'd like the values displayed by the CBO to show ONLY
those records that match the First and Last names on the new form just
opened. I've tried using the Forms! statement to look at the values just
entered in the FirstName and LastName fields but it looks like Access can't
find the just opened form.

Is it possible that using the Forms! statement is not appropriate for these
type of filtering ? What am I doing wrong ? Can I filter the values returned
from the CBO based upon values entered in a new form ?
 
alhotch,
Well, I think a bit of design change is in order.
Sounds like you have a table of customers (ex. tblCustomers), and that
this table is used to create multiple transactions against those
customers... such as an invoice today, another next week, next month etc...
Each of your customers records should have a unique key identifier, like
an autonumber field... something like a CustID.

If it's really a matter of one email per one customer, then the email
should be included in the Customer table... thus no need for the combo, and
that is usually the case.
It's very rare, and problematic... for a vendor to allow multiple email
addresses for a customer...

IF (and I do mean IF) it's possible for a customer to have more than one
email address associated with them, then a separate email table is called
for.
In that table there needs to be a CustID field, so each email record is
associated with the correct customer. tblCustomers CustID (the One)
to tblEmails EmailAddress (the Many)
Your combo query would use CustID to filter the email addresses
returned, to only those associated with the form's CustID.

Let's start with that... There are a few more points/suggestions I'd
like to make, but let's see what you have to say to this point.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Thanks for the response, Al

My application is a shuttle reservation system. I do have a table of email
address. This table was populated from the Importing the addresses from
Outlook Express into a csv file and the Email table was built from that.

Yes, there are duplicate email addresses in this Email table. If I chose to
"look" at these duplicates where I want the reservationist to "make a
choice", the DLookUp function is out since DLookUp only returns the first
occurance. I tried DLookUp and it will do what I want (almost). What I would
like to do is open the form, enter the FirstName, then LastName in the new
(data entry) form. Then, use the CBO for the EmailAddress field and select
which email address is appropriate for this passenger.
This selection would based based on a SELECT query (or Row Source property)
by looking at the Email table, finding ALL occurances of the FullName value
(ie. John Smith), then looking at the new data just entered in the form
(FirstName LastName) Since FullName already is First/Last name, I need to
concatenate the FirstName and LastName values from the just entered data in
the new form and make a comparasion. I would prefer to display all occurances
of FullName ( such as all email addesses listed for a "John Smith") from the
email table and place the selected value in the EmailAddress field of the
newly opened form underlying table.

From an SQL standpoint, here is my current code (that does not work):

SELECT Email.FullName FROM Email WHERE
(((Email.[FullName])=[Forms]![Reservations].[Reservations].[FirstName] & " "
& [Reservations].[LastName]));

As you can see, I use the Forms! statement to read the values of FirstName
and LastName from the Reservations table. However, I'm not sure I can use a
concatenation (calculated) value for the "ControlName" in the function:
Forms![FormName].[ControlName].

I want to make it easy for the reservationist to enter the appropriate email
address. As this is a new application, I already have over 4000 email address
in OutLook Express. Porting these email address over to this new addition to
the current reservation system and making them available to data entry
personnel, should reduce key entry time and accuracy.
 
Let me restate my question in more simpler terms. I want to know if I can
concaenate a [FirstName] & " " & [LastName] value and use it in the
"controlname" parameter of a "Forms!" statement. In other words, will the
following SQL statement work ?

SELECT ALL [tblNames].[NamesID],[tblNames].[Address] FROM tblNames WHERE
[tblNames].[FirstName] & " " &
[tblNames].[LastName]=Forms![frmNames].[FirstName] & " " & [LastName] ORDER
BY [tblNames].[Address] DESC

If not, why ? When I run this statement, I get the FirstName displayed (I
use this SELECT statement as the RowSource control in a ComboBox) but NOT the
concatenation of both FirstName and LastName. I've tried to put "( )" around
the two values - [FirstName] & " " & [LastName] but get syntax errors.
 
Let me restate my question in more simpler terms. I want to know if I can
concaenate a [FirstName] & " " & [LastName] value and use it in the
"controlname" parameter of a "Forms!" statement. In other words, will the
following SQL statement work ?

SELECT ALL [tblNames].[NamesID],[tblNames].[Address] FROM tblNames WHERE
[tblNames].[FirstName] & " " &
[tblNames].[LastName]=Forms![frmNames].[FirstName] & " " & [LastName] ORDER
BY [tblNames].[Address] DESC

If not, why ? When I run this statement, I get the FirstName displayed (I
use this SELECT statement as the RowSource control in a ComboBox) but NOT the
concatenation of both FirstName and LastName. I've tried to put "( )" around
the two values - [FirstName] & " " & [LastName] but get syntax errors.
Click to expand...

You can't assume that Access will understand the isolated reference to
[LastName]; and SELECT ALL is not valid SQL. Try

SELECT [tblNames].[NamesID],[tblNames].[Address] FROM tblNames WHERE
[tblNames].[FirstName] & " " & [tblNames].[LastName] =
Forms![frmNames]![FirstName] & " " & Forms![frmNames]![LastName]
ORDER BY [tblNames].[Address] DESC

Or search the two name fields independently:

SELECT [tblNames].[NamesID],[tblNames].[Address]
FROM tblNames
WHERE [tblNames].[FirstName] = Forms![frmNames]![FirstName]
AND [tblNames].[LastName] = Forms![frmNames]![LastName]
ORDER BY [tblNames].[Address] DESC

or, even better,

SELECT [tblNames].[NamesID],[tblNames].[Address]
FROM tblNames
WHERE ([tblNames].[FirstName] = Forms![frmNames]![FirstName]
OR Forms![frmNames]![FirstName] IS NULL)
AND ([tblNames].[LastName] = Forms![frmNames]![LastName]
OR Forms![frmNames]![LastName] IS NULL)
ORDER BY [tblNames].[Address] DESC

to match all the records for "Zybrowski" if the FirstName control is left
blank.
 
All is well now, John. Once again, you have been MOST helpful in solving
problems. I used the last ("or, even better") solution as it will indeed
match records where the FirstName is blank. I was trying to concatenate the
"controlname" portion of the Forms! statement BUT forgot that I could string
two Forms! statements together. Learning something new EVERYTIME I research
and use these forums !!!

Much Thanks, Again !

John W. Vinson said:
Let me restate my question in more simpler terms. I want to know if I can
concaenate a [FirstName] & " " & [LastName] value and use it in the
"controlname" parameter of a "Forms!" statement. In other words, will the
following SQL statement work ?

SELECT ALL [tblNames].[NamesID],[tblNames].[Address] FROM tblNames WHERE
[tblNames].[FirstName] & " " &
[tblNames].[LastName]=Forms![frmNames].[FirstName] & " " & [LastName] ORDER
BY [tblNames].[Address] DESC

If not, why ? When I run this statement, I get the FirstName displayed (I
use this SELECT statement as the RowSource control in a ComboBox) but NOT the
concatenation of both FirstName and LastName. I've tried to put "( )" around
the two values - [FirstName] & " " & [LastName] but get syntax errors.
Click to expand...

You can't assume that Access will understand the isolated reference to
[LastName]; and SELECT ALL is not valid SQL. Try

SELECT [tblNames].[NamesID],[tblNames].[Address] FROM tblNames WHERE
[tblNames].[FirstName] & " " & [tblNames].[LastName] =
Forms![frmNames]![FirstName] & " " & Forms![frmNames]![LastName]
ORDER BY [tblNames].[Address] DESC

Or search the two name fields independently:

SELECT [tblNames].[NamesID],[tblNames].[Address]
FROM tblNames
WHERE [tblNames].[FirstName] = Forms![frmNames]![FirstName]
AND [tblNames].[LastName] = Forms![frmNames]![LastName]
ORDER BY [tblNames].[Address] DESC

or, even better,

SELECT [tblNames].[NamesID],[tblNames].[Address]
FROM tblNames
WHERE ([tblNames].[FirstName] = Forms![frmNames]![FirstName]
OR Forms![frmNames]![FirstName] IS NULL)
AND ([tblNames].[LastName] = Forms![frmNames]![LastName]
OR Forms![frmNames]![LastName] IS NULL)
ORDER BY [tblNames].[Address] DESC

to match all the records for "Zybrowski" if the FirstName control is left
blank.
Click to expand...
 
Back
Top