Urgh...I'm stuck on building a search feature.  Here are the details:

I have a form, frmSearch, that contains two unbound combo boxes and a
command button:

cboSearchByLastName
cboSearchByCity
cmdSearch

The combo boxes list distinct values for last name and city, respectively,
as pulled from from my main table, tblContacts.

The row source for cboSearchByLastName is:
SELECT DISTINCT tblContacts.ContactNameLast
FROM tblContacts
WHERE tblContacts.ContactNameLast Is Not Null
ORDER BY tblContacts.ContactNameLast;

The row source for cboSearchByCity is:
SELECT DISTINCT tblContacts.ContactCity
FROM tblContacts
WHERE tblContacts.ContactCity Is Not Null
ORDER BY tblContacts.ContactCity;

The command button's OnClick event launches a macro that opens frmEditData.

frmEditData contains all fields from tblContacts and related subforms for
affiliations, mailing distributions and additional information on property
holdings, all joined on ContactID.  (Note the relationships between the
tables are all working properly in other areas of the database).

The data source for frmEditData is the following:
SELECT tblContacts.*
FROM tblContacts
WHERE tblContacts.ContactNameLast LIKE
[Forms]![frmSearch]![cboSearchByLastName] & "*"
OR tblContacts.ContactCity Like [Forms]![frmSearch]![cboSearchByCity] & "*";


frmSearch worked fine when I only had cboSearchByLastName on it.  I could
choose a last name from the list or type just a few letters of a name, click
on the search button and frmEditData would launch, showing just the records
I wanted/expected to see.

Then, when I added cboSearchByCity to frmSearch and the OR clause to
frmEditData's data source, it stopped working----or, rather, it doesn't work
for searching on city.  It does work still for searching on last name.  If I
select a city, it appears to be finding all records where the last name is
not null, regardless of what city they live in.

I'd like to be able to add a couple more unbound fields to frmSearch and
have them pass values to frmEditData in a similar fashion, but am
discouraged that I seem to be unable to have frmEditData evaluate more than
one value statement connected by OR.

I'm probably missing something obvious...so maybe some fresh eyeballs would
help.

Thanks in advance...

--Shelley

Shelley,

For a semi complete answer you could investigate using some VBA to
control the filter properties of the form, similar to using the filter
control on the toolbar but you can have multiple selections declared in
the code.

I am not overly familiar with using this method as I mainly use list
boxes for search scenarios and adjust the SQL in the Rowsource property
to display selected data in the list box.

Maybe this is useful, maybe not.....

Regards

Paul


-----Original Message-----
From: Shelley Curnow [mailto:SCurnow@h...] 
Sent: 24 March 2002 21:40
To: Access
Subject: [access] Trouble with passing values to form's data source

Urgh...I'm stuck on building a search feature.  Here are the details:

I have a form, frmSearch, that contains two unbound combo boxes and a
command button:

cboSearchByLastName
cboSearchByCity
cmdSearch

The combo boxes list distinct values for last name and city,
respectively,
as pulled from from my main table, tblContacts.

The row source for cboSearchByLastName is:
SELECT DISTINCT tblContacts.ContactNameLast
FROM tblContacts
WHERE tblContacts.ContactNameLast Is Not Null
ORDER BY tblContacts.ContactNameLast;

The row source for cboSearchByCity is:
SELECT DISTINCT tblContacts.ContactCity
FROM tblContacts
WHERE tblContacts.ContactCity Is Not Null
ORDER BY tblContacts.ContactCity;

The command button's OnClick event launches a macro that opens
frmEditData.

frmEditData contains all fields from tblContacts and related subforms
for
affiliations, mailing distributions and additional information on
property
holdings, all joined on ContactID.  (Note the relationships between the
tables are all working properly in other areas of the database).

The data source for frmEditData is the following:
SELECT tblContacts.*
FROM tblContacts
WHERE tblContacts.ContactNameLast LIKE
[Forms]![frmSearch]![cboSearchByLastName] & "*"
OR tblContacts.ContactCity Like [Forms]![frmSearch]![cboSearchByCity] &
"*";


frmSearch worked fine when I only had cboSearchByLastName on it.  I
could
choose a last name from the list or type just a few letters of a name,
click
on the search button and frmEditData would launch, showing just the
records
I wanted/expected to see.

Then, when I added cboSearchByCity to frmSearch and the OR clause to
frmEditData's data source, it stopped working----or, rather, it doesn't
work
for searching on city.  It does work still for searching on last name.
If I
select a city, it appears to be finding all records where the last name
is
not null, regardless of what city they live in.

I'd like to be able to add a couple more unbound fields to frmSearch and
have them pass values to frmEditData in a similar fashion, but am
discouraged that I seem to be unable to have frmEditData evaluate more
than
one value statement connected by OR.

I'm probably missing something obvious...so maybe some fresh eyeballs
would
help.

Thanks in advance...

--Shelley

Shelley wrote:

I have a form, frmSearch, that contains two unbound combo boxes and a
command button:

cboSearchByLastName
cboSearchByCity
cmdSearch

the command button's OnClick event launches a macro that opens
frmEditData.

frmEditData contains all fields from tblContacts and related subforms
for
affiliations, mailing distributions and additional information on
property
holdings, all joined on ContactID.  (Note the relationships between the
tables are all working properly in other areas of the database).

The data source for frmEditData is the following:
SELECT tblContacts.*
FROM tblContacts
WHERE tblContacts.ContactNameLast LIKE
[Forms]![frmSearch]![cboSearchByLastName] & "*"
OR tblContacts.ContactCity Like [Forms]![frmSearch]![cboSearchByCity] &
"*";

frmSearch worked fine when I only had cboSearchByLastName on it.  I
could choose a last name from the list or type just a few letters of a
name,
click on the search button and frmEditData would launch, showing just the
records I wanted/expected to see.

Then, when I added cboSearchByCity to frmSearch and the OR clause to
frmEditData's data source, it stopped working----or, rather, it doesn't
work for searching on city.  It does work still for searching on last name.
If I select a city, it appears to be finding all records where the last
name
is not null, regardless of what city they live in.

I'd like to be able to add a couple more unbound fields to frmSearch and
have them pass values to frmEditData in a similar fashion, but am
discouraged that I seem to be unable to have frmEditData evaluate more
than one value statement connected by OR.

I would like to clarify that you (i.e.Shelley) are searching for either:

a name (selected from a combo box) OR  a city (selected from another combo
box)

and would like to add other "or" searches?

As it stands I would expect that if you entered a name, this would work but
if you entered no name but a city I would expect all non-null names
regardless of city. This is because you have added an asterisk to the LIKE
clause and have an OR modifier.

The effect is as follows:

no name entered but the asterisk added to the end equates to ALL so all are
returned
the OR statement  is operating but is hidden in the all names being
returned

in other words both are happening!

You will need to extend your code to check that something has been input to
each element of your input form - what you are trying to do is:


if something has been keyed into the name field, search on the name field
if something has been keyed into the city field, search on the city

you might want to extend that to searching on both

this would be:

if something has been keyed into the name field but not the city field,
search on the name field alone
if something has been keyed into the city field but not the name field,
search on the city field alone
if something has been keyed into both fields, search on both

Obviously, the more fields you add, the more complicated it gets!

If it helps, create a database with the bare minimum required (i.e. the
tables directly involved, the form and any queries directly involved) and
e-mail it to me at

joe.dunn@c...

I can fix it and mail it back in a day

Joe Dunn


*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

Joe...THANK you!  I knew it was something fairly obvious (the "*").  I
appreciate your offer to hammer out the coding for me---I *think* I can take
it from here (at least want to give it a try), but may follow up with a
question or two, if you don't mind.

thx

Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
tel:    (xxx) xxx-xxxx
fax:   (xxx) xxx-xxxx

-----Original Message-----
From: joe.dunn@c... [mailto:joe.dunn@c...] 
Sent: Monday, March 25, 2002 9:16 AM
To: Access
Subject: [access] RE: Trouble with passing values to form's data source


Shelley wrote:

I have a form, frmSearch, that contains two unbound combo boxes and a
command button:

cboSearchByLastName
cboSearchByCity
cmdSearch

the command button's OnClick event launches a macro that opens
frmEditData.

frmEditData contains all fields from tblContacts and related subforms
for
affiliations, mailing distributions and additional information on
property
holdings, all joined on ContactID.  (Note the relationships between the
tables are all working properly in other areas of the database).

The data source for frmEditData is the following:
SELECT tblContacts.*
FROM tblContacts
WHERE tblContacts.ContactNameLast LIKE
[Forms]![frmSearch]![cboSearchByLastName] & "*"
OR tblContacts.ContactCity Like [Forms]![frmSearch]![cboSearchByCity] &
"*";

frmSearch worked fine when I only had cboSearchByLastName on it.  I
could choose a last name from the list or type just a few letters of a
name,
click on the search button and frmEditData would launch, showing just the
records I wanted/expected to see.

Then, when I added cboSearchByCity to frmSearch and the OR clause to
frmEditData's data source, it stopped working----or, rather, it doesn't
work for searching on city.  It does work still for searching on last name.
If I select a city, it appears to be finding all records where the last
name
is not null, regardless of what city they live in.

I'd like to be able to add a couple more unbound fields to frmSearch and
have them pass values to frmEditData in a similar fashion, but am
discouraged that I seem to be unable to have frmEditData evaluate more
than one value statement connected by OR.

I would like to clarify that you (i.e.Shelley) are searching for either:

a name (selected from a combo box) OR  a city (selected from another combo
box)

and would like to add other "or" searches?

As it stands I would expect that if you entered a name, this would work but
if you entered no name but a city I would expect all non-null names
regardless of city. This is because you have added an asterisk to the LIKE
clause and have an OR modifier.

The effect is as follows:

no name entered but the asterisk added to the end equates to ALL so all are
returned
the OR statement  is operating but is hidden in the all names being
returned

in other words both are happening!

You will need to extend your code to check that something has been input to
each element of your input form - what you are trying to do is:


if something has been keyed into the name field, search on the name field
if something has been keyed into the city field, search on the city

you might want to extend that to searching on both

this would be:

if something has been keyed into the name field but not the city field,
search on the name field alone
if something has been keyed into the city field but not the name field,
search on the city field alone
if something has been keyed into both fields, search on both

Obviously, the more fields you add, the more complicated it gets!

If it helps, create a database with the bare minimum required (i.e. the
tables directly involved, the form and any queries directly involved) and
e-mail it to me at

joe.dunn@c...

I can fix it and mail it back in a day

Joe Dunn