search form w/ no duplicates

M

miss031

I made a search form that uses one text box to return results from a query
base on the last name, company name or phone number entered in the text box,
and displays the results in the detail of the form. This worked fine until I
tried to normalize my data. I took phone numbers out of my main contacts
table, and made the tables below, and now it returns duplicate records if a
contact has 2 different phone numbers. I only want to return each contact
once. I think its my query, because it returns duplicate records for each
phone number, but I don't know how to fix it. PLease help!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
New normalized tables:

"table_phone_numbers"
phone_ID phone_number
1 555-5555
2 555-6666

"table_phone_types"
type_ID phone_type
1 Home
2 Work

"table_contact_phones"
ID type_ID phone_ID contact_ID
1 2 1 10
2 1 2 6

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I used a query to join the tables to the contact table, as follows:

SELECT DISTINCT table__contacts.contact_ID, table__contacts.contactlastname,
table__contacts.contactfirstname, table_bidder_registration.bidder_bidnumber,
table_seller_registration.seller_lotnumber, tbl_phone_numbers.phone_number,
table_addresses.contactcity, table_contact_companies.contactcompany
FROM tbl_phone_numbers INNER JOIN (table_contact_companies INNER JOIN
(table_addresses INNER JOIN (((table__contacts LEFT JOIN
table_bidder_registration ON table__contacts.contact_ID =
table_bidder_registration.bidder_contactID) LEFT JOIN
table_seller_registration ON table__contacts.contact_ID =
table_seller_registration.seller_contactID) INNER JOIN
tbl_contact_phone_numbers ON table__contacts.contact_ID =
tbl_contact_phone_numbers.contact_ID) ON table_addresses.ID =
table__contacts.contact_address_ID) ON table_contact_companies.companyID =
table__contacts.contact_company_ID) ON tbl_phone_numbers.phone_number_ID =
tbl_contact_phone_numbers.phone_number_id;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The code for my search box is as follows:

Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
Else

End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like ""*" &
strWord & _
"*"") OR ([table_contact_companies.contactcompany]
Like ""*" & strWord & "*"") OR ([phone_number] Like ""*" & strWord & "*"")
OR ([bidder_bidnumber] Like ""*" & strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
 
A

Allen Browne

If you use JOINs on multiple tables, and there a client has multiple
matches, you will get them listed multiple times. That's how queries work.

You therefore need a different approach, probably using subqueries. The form
will be baesd on just the contacts table (so each record shows up once
only.) You will then use subqueries in the WHERE clause (or in the Filter of
the form) to retrieve the contacts based on records in other tables.

For more details, see:
Subquery Basics: Filters and Searches
at:
http://allenbrowne.com/subquery-01.html#Search

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

miss031 said:
I made a search form that uses one text box to return results from a query
base on the last name, company name or phone number entered in the text
box,
and displays the results in the detail of the form. This worked fine
until I
tried to normalize my data. I took phone numbers out of my main contacts
table, and made the tables below, and now it returns duplicate records if
a
contact has 2 different phone numbers. I only want to return each contact
once. I think its my query, because it returns duplicate records for each
phone number, but I don't know how to fix it. PLease help!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
New normalized tables:

"table_phone_numbers"
phone_ID phone_number
1 555-5555
2 555-6666

"table_phone_types"
type_ID phone_type
1 Home
2 Work

"table_contact_phones"
ID type_ID phone_ID contact_ID
1 2 1 10
2 1 2 6

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I used a query to join the tables to the contact table, as follows:

SELECT DISTINCT table__contacts.contact_ID,
table__contacts.contactlastname,
table__contacts.contactfirstname,
table_bidder_registration.bidder_bidnumber,
table_seller_registration.seller_lotnumber,
tbl_phone_numbers.phone_number,
table_addresses.contactcity, table_contact_companies.contactcompany
FROM tbl_phone_numbers INNER JOIN (table_contact_companies INNER JOIN
(table_addresses INNER JOIN (((table__contacts LEFT JOIN
table_bidder_registration ON table__contacts.contact_ID =
table_bidder_registration.bidder_contactID) LEFT JOIN
table_seller_registration ON table__contacts.contact_ID =
table_seller_registration.seller_contactID) INNER JOIN
tbl_contact_phone_numbers ON table__contacts.contact_ID =
tbl_contact_phone_numbers.contact_ID) ON table_addresses.ID =
table__contacts.contact_address_ID) ON table_contact_companies.companyID =
table__contacts.contact_company_ID) ON tbl_phone_numbers.phone_number_ID =
tbl_contact_phone_numbers.phone_number_id;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The code for my search box is as follows:

Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
Else

End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like ""*" &
strWord & _
"*"") OR ([table_contact_companies.contactcompany]
Like ""*" & strWord & "*"") OR ([phone_number] Like ""*" & strWord & "*"")
OR ([bidder_bidnumber] Like ""*" & strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
Click to expand...
 
M

miss031

I tried a few things that were suggested there, but I can't get anything to
work. I want to return only one instance of a contact_ID even if that
contact_ID has 2 phone number records.

If i enter the phone number in the search box on my form, only the record
matching that phone number returns, but if I enter a name instead, I get 2
instances of every name (one for each phone number). I don't want my user to
have to enter the name and phone number, but I don't not how to formulate the
subquery to filter out the duplicates.

Any help with this would be appreciated.

Also, how do I get my subform to load blank?

Allen Browne said:
If you use JOINs on multiple tables, and there a client has multiple
matches, you will get them listed multiple times. That's how queries work.

You therefore need a different approach, probably using subqueries. The form
will be baesd on just the contacts table (so each record shows up once
only.) You will then use subqueries in the WHERE clause (or in the Filter of
the form) to retrieve the contacts based on records in other tables.

For more details, see:
Subquery Basics: Filters and Searches
at:
http://allenbrowne.com/subquery-01.html#Search

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

miss031 said:
I made a search form that uses one text box to return results from a query
base on the last name, company name or phone number entered in the text
box,
and displays the results in the detail of the form. This worked fine
until I
tried to normalize my data. I took phone numbers out of my main contacts
table, and made the tables below, and now it returns duplicate records if
a
contact has 2 different phone numbers. I only want to return each contact
once. I think its my query, because it returns duplicate records for each
phone number, but I don't know how to fix it. PLease help!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
New normalized tables:

"table_phone_numbers"
phone_ID phone_number
1 555-5555
2 555-6666

"table_phone_types"
type_ID phone_type
1 Home
2 Work

"table_contact_phones"
ID type_ID phone_ID contact_ID
1 2 1 10
2 1 2 6

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I used a query to join the tables to the contact table, as follows:

SELECT DISTINCT table__contacts.contact_ID,
table__contacts.contactlastname,
table__contacts.contactfirstname,
table_bidder_registration.bidder_bidnumber,
table_seller_registration.seller_lotnumber,
tbl_phone_numbers.phone_number,
table_addresses.contactcity, table_contact_companies.contactcompany
FROM tbl_phone_numbers INNER JOIN (table_contact_companies INNER JOIN
(table_addresses INNER JOIN (((table__contacts LEFT JOIN
table_bidder_registration ON table__contacts.contact_ID =
table_bidder_registration.bidder_contactID) LEFT JOIN
table_seller_registration ON table__contacts.contact_ID =
table_seller_registration.seller_contactID) INNER JOIN
tbl_contact_phone_numbers ON table__contacts.contact_ID =
tbl_contact_phone_numbers.contact_ID) ON table_addresses.ID =
table__contacts.contact_address_ID) ON table_contact_companies.companyID =
table__contacts.contact_company_ID) ON tbl_phone_numbers.phone_number_ID =
tbl_contact_phone_numbers.phone_number_id;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The code for my search box is as follows:

Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
Else

End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like ""*" &
strWord & _
"*"") OR ([table_contact_companies.contactcompany]
Like ""*" & strWord & "*"") OR ([phone_number] Like ""*" & strWord & "*"")
OR ([bidder_bidnumber] Like ""*" & strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
Click to expand...
Click to expand...
 
A

Allen Browne

Did you try the subquery in the form's Filter string?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

miss031 said:
I tried a few things that were suggested there, but I can't get anything to
work. I want to return only one instance of a contact_ID even if that
contact_ID has 2 phone number records.

If i enter the phone number in the search box on my form, only the record
matching that phone number returns, but if I enter a name instead, I get 2
instances of every name (one for each phone number). I don't want my user
to
have to enter the name and phone number, but I don't not how to formulate
the
subquery to filter out the duplicates.

Any help with this would be appreciated.

Also, how do I get my subform to load blank?

Allen Browne said:
If you use JOINs on multiple tables, and there a client has multiple
matches, you will get them listed multiple times. That's how queries
work.

You therefore need a different approach, probably using subqueries. The
form
will be baesd on just the contacts table (so each record shows up once
only.) You will then use subqueries in the WHERE clause (or in the Filter
of
the form) to retrieve the contacts based on records in other tables.

For more details, see:
Subquery Basics: Filters and Searches
at:
http://allenbrowne.com/subquery-01.html#Search

miss031 said:
I made a search form that uses one text box to return results from a
query
base on the last name, company name or phone number entered in the text
box,
and displays the results in the detail of the form. This worked fine
until I
tried to normalize my data. I took phone numbers out of my main
contacts
table, and made the tables below, and now it returns duplicate records
if
a
contact has 2 different phone numbers. I only want to return each
contact
once. I think its my query, because it returns duplicate records for
each
phone number, but I don't know how to fix it. PLease help!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
New normalized tables:

"table_phone_numbers"
phone_ID phone_number
1 555-5555
2 555-6666

"table_phone_types"
type_ID phone_type
1 Home
2 Work

"table_contact_phones"
ID type_ID phone_ID contact_ID
1 2 1 10
2 1 2 6

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I used a query to join the tables to the contact table, as follows:

SELECT DISTINCT table__contacts.contact_ID,
table__contacts.contactlastname,
table__contacts.contactfirstname,
table_bidder_registration.bidder_bidnumber,
table_seller_registration.seller_lotnumber,
tbl_phone_numbers.phone_number,
table_addresses.contactcity, table_contact_companies.contactcompany
FROM tbl_phone_numbers INNER JOIN (table_contact_companies INNER JOIN
(table_addresses INNER JOIN (((table__contacts LEFT JOIN
table_bidder_registration ON table__contacts.contact_ID =
table_bidder_registration.bidder_contactID) LEFT JOIN
table_seller_registration ON table__contacts.contact_ID =
table_seller_registration.seller_contactID) INNER JOIN
tbl_contact_phone_numbers ON table__contacts.contact_ID =
tbl_contact_phone_numbers.contact_ID) ON table_addresses.ID =
table__contacts.contact_address_ID) ON
table_contact_companies.companyID =
table__contacts.contact_company_ID) ON
tbl_phone_numbers.phone_number_ID =
tbl_contact_phone_numbers.phone_number_id;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The code for my search box is as follows:

Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
Else

End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like ""*"
&
strWord & _
"*"") OR
([table_contact_companies.contactcompany]
Like ""*" & strWord & "*"") OR ([phone_number] Like ""*" & strWord &
"*"")
OR ([bidder_bidnumber] Like ""*" & strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
M

miss031

I don't know what to use as the subquery.

Allen Browne said:
Did you try the subquery in the form's Filter string?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

miss031 said:
I tried a few things that were suggested there, but I can't get anything to
work. I want to return only one instance of a contact_ID even if that
contact_ID has 2 phone number records.

If i enter the phone number in the search box on my form, only the record
matching that phone number returns, but if I enter a name instead, I get 2
instances of every name (one for each phone number). I don't want my user
to
have to enter the name and phone number, but I don't not how to formulate
the
subquery to filter out the duplicates.

Any help with this would be appreciated.

Also, how do I get my subform to load blank?

Allen Browne said:
If you use JOINs on multiple tables, and there a client has multiple
matches, you will get them listed multiple times. That's how queries
work.

You therefore need a different approach, probably using subqueries. The
form
will be baesd on just the contacts table (so each record shows up once
only.) You will then use subqueries in the WHERE clause (or in the Filter
of
the form) to retrieve the contacts based on records in other tables.

For more details, see:
Subquery Basics: Filters and Searches
at:
http://allenbrowne.com/subquery-01.html#Search

I made a search form that uses one text box to return results from a
query
base on the last name, company name or phone number entered in the text
box,
and displays the results in the detail of the form. This worked fine
until I
tried to normalize my data. I took phone numbers out of my main
contacts
table, and made the tables below, and now it returns duplicate records
if
a
contact has 2 different phone numbers. I only want to return each
contact
once. I think its my query, because it returns duplicate records for
each
phone number, but I don't know how to fix it. PLease help!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
New normalized tables:

"table_phone_numbers"
phone_ID phone_number
1 555-5555
2 555-6666

"table_phone_types"
type_ID phone_type
1 Home
2 Work

"table_contact_phones"
ID type_ID phone_ID contact_ID
1 2 1 10
2 1 2 6

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I used a query to join the tables to the contact table, as follows:

SELECT DISTINCT table__contacts.contact_ID,
table__contacts.contactlastname,
table__contacts.contactfirstname,
table_bidder_registration.bidder_bidnumber,
table_seller_registration.seller_lotnumber,
tbl_phone_numbers.phone_number,
table_addresses.contactcity, table_contact_companies.contactcompany
FROM tbl_phone_numbers INNER JOIN (table_contact_companies INNER JOIN
(table_addresses INNER JOIN (((table__contacts LEFT JOIN
table_bidder_registration ON table__contacts.contact_ID =
table_bidder_registration.bidder_contactID) LEFT JOIN
table_seller_registration ON table__contacts.contact_ID =
table_seller_registration.seller_contactID) INNER JOIN
tbl_contact_phone_numbers ON table__contacts.contact_ID =
tbl_contact_phone_numbers.contact_ID) ON table_addresses.ID =
table__contacts.contact_address_ID) ON
table_contact_companies.companyID =
table__contacts.contact_company_ID) ON
tbl_phone_numbers.phone_number_ID =
tbl_contact_phone_numbers.phone_number_id;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The code for my search box is as follows:

Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
Else

End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like ""*"
&
strWord & _
"*"") OR
([table_contact_companies.contactcompany]
Like ""*" & strWord & "*"") OR ([phone_number] Like ""*" & strWord &
"*"")
OR ([bidder_bidnumber] Like ""*" & strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Perhaps this is beyond you then.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

miss031 said:
I don't know what to use as the subquery.

Allen Browne said:
Did you try the subquery in the form's Filter string?

miss031 said:
I tried a few things that were suggested there, but I can't get anything
to
work. I want to return only one instance of a contact_ID even if that
contact_ID has 2 phone number records.

If i enter the phone number in the search box on my form, only the
record
matching that phone number returns, but if I enter a name instead, I
get 2
instances of every name (one for each phone number). I don't want my
user
to
have to enter the name and phone number, but I don't not how to
formulate
the
subquery to filter out the duplicates.

Any help with this would be appreciated.

Also, how do I get my subform to load blank?

:

If you use JOINs on multiple tables, and there a client has multiple
matches, you will get them listed multiple times. That's how queries
work.

You therefore need a different approach, probably using subqueries.
The
form
will be baesd on just the contacts table (so each record shows up once
only.) You will then use subqueries in the WHERE clause (or in the
Filter
of
the form) to retrieve the contacts based on records in other tables.

For more details, see:
Subquery Basics: Filters and Searches
at:
http://allenbrowne.com/subquery-01.html#Search

I made a search form that uses one text box to return results from a
query
base on the last name, company name or phone number entered in the
text
box,
and displays the results in the detail of the form. This worked
fine
until I
tried to normalize my data. I took phone numbers out of my main
contacts
table, and made the tables below, and now it returns duplicate
records
if
a
contact has 2 different phone numbers. I only want to return each
contact
once. I think its my query, because it returns duplicate records
for
each
phone number, but I don't know how to fix it. PLease help!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
New normalized tables:

"table_phone_numbers"
phone_ID phone_number
1 555-5555
2 555-6666

"table_phone_types"
type_ID phone_type
1 Home
2 Work

"table_contact_phones"
ID type_ID phone_ID contact_ID
1 2 1 10
2 1 2 6

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I used a query to join the tables to the contact table, as follows:

SELECT DISTINCT table__contacts.contact_ID,
table__contacts.contactlastname,
table__contacts.contactfirstname,
table_bidder_registration.bidder_bidnumber,
table_seller_registration.seller_lotnumber,
tbl_phone_numbers.phone_number,
table_addresses.contactcity, table_contact_companies.contactcompany
FROM tbl_phone_numbers INNER JOIN (table_contact_companies INNER
JOIN
(table_addresses INNER JOIN (((table__contacts LEFT JOIN
table_bidder_registration ON table__contacts.contact_ID =
table_bidder_registration.bidder_contactID) LEFT JOIN
table_seller_registration ON table__contacts.contact_ID =
table_seller_registration.seller_contactID) INNER JOIN
tbl_contact_phone_numbers ON table__contacts.contact_ID =
tbl_contact_phone_numbers.contact_ID) ON table_addresses.ID =
table__contacts.contact_address_ID) ON
table_contact_companies.companyID =
table__contacts.contact_company_ID) ON
tbl_phone_numbers.phone_number_ID =
tbl_contact_phone_numbers.phone_number_id;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The code for my search box is as follows:

Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
Else

End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like
""*"
&
strWord & _
"*"") OR
([table_contact_companies.contactcompany]
Like ""*" & strWord & "*"") OR ([phone_number] Like ""*" & strWord &
"*"")
OR ([bidder_bidnumber] Like ""*" & strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
M

miss031

Not really, but thanks. I found the answer here:

http://www.mvps.org/access/queries/qry0020.htm

Allen Browne said:
Perhaps this is beyond you then.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

miss031 said:
I don't know what to use as the subquery.

Allen Browne said:
Did you try the subquery in the form's Filter string?

I tried a few things that were suggested there, but I can't get anything
to
work. I want to return only one instance of a contact_ID even if that
contact_ID has 2 phone number records.

If i enter the phone number in the search box on my form, only the
record
matching that phone number returns, but if I enter a name instead, I
get 2
instances of every name (one for each phone number). I don't want my
user
to
have to enter the name and phone number, but I don't not how to
formulate
the
subquery to filter out the duplicates.

Any help with this would be appreciated.

Also, how do I get my subform to load blank?

:

If you use JOINs on multiple tables, and there a client has multiple
matches, you will get them listed multiple times. That's how queries
work.

You therefore need a different approach, probably using subqueries.
The
form
will be baesd on just the contacts table (so each record shows up once
only.) You will then use subqueries in the WHERE clause (or in the
Filter
of
the form) to retrieve the contacts based on records in other tables.

For more details, see:
Subquery Basics: Filters and Searches
at:
http://allenbrowne.com/subquery-01.html#Search

I made a search form that uses one text box to return results from a
query
base on the last name, company name or phone number entered in the
text
box,
and displays the results in the detail of the form. This worked
fine
until I
tried to normalize my data. I took phone numbers out of my main
contacts
table, and made the tables below, and now it returns duplicate
records
if
a
contact has 2 different phone numbers. I only want to return each
contact
once. I think its my query, because it returns duplicate records
for
each
phone number, but I don't know how to fix it. PLease help!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
New normalized tables:

"table_phone_numbers"
phone_ID phone_number
1 555-5555
2 555-6666

"table_phone_types"
type_ID phone_type
1 Home
2 Work

"table_contact_phones"
ID type_ID phone_ID contact_ID
1 2 1 10
2 1 2 6

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I used a query to join the tables to the contact table, as follows:

SELECT DISTINCT table__contacts.contact_ID,
table__contacts.contactlastname,
table__contacts.contactfirstname,
table_bidder_registration.bidder_bidnumber,
table_seller_registration.seller_lotnumber,
tbl_phone_numbers.phone_number,
table_addresses.contactcity, table_contact_companies.contactcompany
FROM tbl_phone_numbers INNER JOIN (table_contact_companies INNER
JOIN
(table_addresses INNER JOIN (((table__contacts LEFT JOIN
table_bidder_registration ON table__contacts.contact_ID =
table_bidder_registration.bidder_contactID) LEFT JOIN
table_seller_registration ON table__contacts.contact_ID =
table_seller_registration.seller_contactID) INNER JOIN
tbl_contact_phone_numbers ON table__contacts.contact_ID =
tbl_contact_phone_numbers.contact_ID) ON table_addresses.ID =
table__contacts.contact_address_ID) ON
table_contact_companies.companyID =
table__contacts.contact_company_ID) ON
tbl_phone_numbers.phone_number_ID =
tbl_contact_phone_numbers.phone_number_id;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The code for my search box is as follows:

Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
Else

End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like
""*"
&
strWord & _
"*"") OR
([table_contact_companies.contactcompany]
Like ""*" & strWord & "*"") OR ([phone_number] Like ""*" & strWord &
"*"")
OR ([bidder_bidnumber] Like ""*" & strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Excellent. Glad you were able to sort it out using subqueries or one of the
other techniques
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

search priority 3
Error 3048 "Cannot open any more databases" 7
subform - Dcount and filter 1
Search Form with a SubForm 3
Modified Allenbrowne Search Form displays error 2
Allen Browne's Search Form - Debugging help needed 2
Filtering a multiple item form 1
Filter for words at the beginning or end using a Form Command Butt 5

Top