Help performing a search

[Someone]

Hello

I need to incorporate a search facility into my database. Let me give a
simple overview on how it's set up.

There is a main form which has a subform in datasheet view. The subform is
based on a query to a table (call it table a). The reason for this is that
the main form has unbound text boxes so users can search the data in the
subform (the view is requeried to show like records on the data entered -
Like "*" & [FormControlName] etc).

Amongst the buttons on the main form is one to open another form with three
tabs. The form opens to the same record that is selected in the main form's
subform. The default tab shows ALL fields of the record selected from the
subform. On the second tab is another subform in form view for a different
table (call it table b - it's actually a table of contact details). The
third tab is irrelevant for this post.

The relationship between table a and b is a one-to-many, where 1 record in
table a can refer to many records in table b (i.e. a record in table a could
have more than one contact name and/or address).

I've been asked to incorporate a search facility on the contacts (table).
It would need to locate the related record in table a (i.e. the master
record). The problem is, I can't figure out the best way to do it.

Ideally, I'd like to have a search form with textboxes that the user can
fill in with their required search criteria. This then finds the related
record in table a, based on the search just made on table b. If possible,
I'd like this to requery the data on the main form's subform so the user can
click open the record of their choosing. I've tried, but it's gone beyond
me. Do anyone of you have any ideas on how you would go about this and what
I'd need to do to accomplish it? You might think my idea is
over-complicated and that there is a more straight-forward idea to fulfil
this.

I do hope I haven't sounded too vague in my request. If you need further
detail, I'm happy to supply it.

Many thanks for your time
M

 

[Smartin]

Hello

I need to incorporate a search facility into my database. Let me give a
simple overview on how it's set up.

There is a main form which has a subform in datasheet view. The subform is
based on a query to a table (call it table a). The reason for this is that
the main form has unbound text boxes so users can search the data in the
subform (the view is requeried to show like records on the data entered -
Like "*" & [FormControlName] etc).

Amongst the buttons on the main form is one to open another form with three
tabs. The form opens to the same record that is selected in the main form's
subform. The default tab shows ALL fields of the record selected from the
subform. On the second tab is another subform in form view for a different
table (call it table b - it's actually a table of contact details). The
third tab is irrelevant for this post.

The relationship between table a and b is a one-to-many, where 1 record in
table a can refer to many records in table b (i.e. a record in table a could
have more than one contact name and/or address).

I've been asked to incorporate a search facility on the contacts (table).
It would need to locate the related record in table a (i.e. the master
record). The problem is, I can't figure out the best way to do it.

Ideally, I'd like to have a search form with textboxes that the user can
fill in with their required search criteria. This then finds the related
record in table a, based on the search just made on table b. If possible,
I'd like this to requery the data on the main form's subform so the user can
click open the record of their choosing. I've tried, but it's gone beyond
me. Do anyone of you have any ideas on how you would go about this and what
I'd need to do to accomplish it? You might think my idea is
over-complicated and that there is a more straight-forward idea to fulfil
this.

I do hope I haven't sounded too vague in my request. If you need further
detail, I'm happy to supply it.

Many thanks for your time
M

I did something like this to do that... Set up a button on the form that
calls a query. The query will request the search term.

In the click event of the button:
===== (VBA)
Private Sub cmdPersonSearch_Click()
DoFilterButton ("qPersonSearch")
End Sub
=====

....and also in a class module (because the 'DoFilterButton' sub handles
several different searches)...

===== (VBA)
Private Sub DoFilterButton(QueryName As String)
On Error GoTo FilterErr
Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL

Exit_DoFilterButton:
Exit Sub

FilterErr:
MsgBox Err.Description
Resume Exit_DoFilterButton
End Sub
=====

In a query named PersonSearch:

===== (SQL)
SELECT *
FROM A
WHERE A.ID IN
(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
=====

Any good?

 

[Someone]

Hi

Thanks for taking the time to respond to me.

What you said sounds perfect. I tried incorporating it, but I'm getting the
following error when attempting to perform a search:

Sub, Function, or Property not defined (Error 35)

Can you help me resolve this?

Thank you
M

Hello

I need to incorporate a search facility into my database. Let me give a
simple overview on how it's set up.

There is a main form which has a subform in datasheet view. The subform
is based on a query to a table (call it table a). The reason for this is
that the main form has unbound text boxes so users can search the data in
the subform (the view is requeried to show like records on the data
entered - Like "*" & [FormControlName] etc).

Amongst the buttons on the main form is one to open another form with
three tabs. The form opens to the same record that is selected in the
main form's subform. The default tab shows ALL fields of the record
selected from the subform. On the second tab is another subform in form
view for a different table (call it table b - it's actually a table of
contact details). The third tab is irrelevant for this post.

The relationship between table a and b is a one-to-many, where 1 record
in table a can refer to many records in table b (i.e. a record in table a
could have more than one contact name and/or address).

I've been asked to incorporate a search facility on the contacts (table).
It would need to locate the related record in table a (i.e. the master
record). The problem is, I can't figure out the best way to do it.

Ideally, I'd like to have a search form with textboxes that the user can
fill in with their required search criteria. This then finds the related
record in table a, based on the search just made on table b. If
possible, I'd like this to requery the data on the main form's subform so
the user can click open the record of their choosing. I've tried, but
it's gone beyond me. Do anyone of you have any ideas on how you would go
about this and what I'd need to do to accomplish it? You might think my
idea is over-complicated and that there is a more straight-forward idea
to fulfil this.

I do hope I haven't sounded too vague in my request. If you need further
detail, I'm happy to supply it.

Many thanks for your time
M

I did something like this to do that... Set up a button on the form that
calls a query. The query will request the search term.

In the click event of the button:
===== (VBA)
Private Sub cmdPersonSearch_Click()
DoFilterButton ("qPersonSearch")
End Sub
=====

...and also in a class module (because the 'DoFilterButton' sub handles
several different searches)...

===== (VBA)
Private Sub DoFilterButton(QueryName As String)
On Error GoTo FilterErr
Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL

Exit_DoFilterButton:
Exit Sub

FilterErr:
MsgBox Err.Description
Resume Exit_DoFilterButton
End Sub
=====

In a query named PersonSearch:

===== (SQL)
SELECT *
FROM A
WHERE A.ID IN
(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
=====

Any good?

 

[Smartin]

Hi

Thanks for taking the time to respond to me.

What you said sounds perfect. I tried incorporating it, but I'm getting the
following error when attempting to perform a search:

Sub, Function, or Property not defined (Error 35)

Can you help me resolve this?

Thank you
M

Hello

I need to incorporate a search facility into my database. Let me give a
simple overview on how it's set up.

There is a main form which has a subform in datasheet view. The subform
is based on a query to a table (call it table a). The reason for this is
that the main form has unbound text boxes so users can search the data in
the subform (the view is requeried to show like records on the data
entered - Like "*" & [FormControlName] etc).

Amongst the buttons on the main form is one to open another form with
three tabs. The form opens to the same record that is selected in the
main form's subform. The default tab shows ALL fields of the record
selected from the subform. On the second tab is another subform in form
view for a different table (call it table b - it's actually a table of
contact details). The third tab is irrelevant for this post.

The relationship between table a and b is a one-to-many, where 1 record
in table a can refer to many records in table b (i.e. a record in table a
could have more than one contact name and/or address).

I've been asked to incorporate a search facility on the contacts (table).
It would need to locate the related record in table a (i.e. the master
record). The problem is, I can't figure out the best way to do it.

Ideally, I'd like to have a search form with textboxes that the user can
fill in with their required search criteria. This then finds the related
record in table a, based on the search just made on table b. If
possible, I'd like this to requery the data on the main form's subform so
the user can click open the record of their choosing. I've tried, but
it's gone beyond me. Do anyone of you have any ideas on how you would go
about this and what I'd need to do to accomplish it? You might think my
idea is over-complicated and that there is a more straight-forward idea
to fulfil this.

I do hope I haven't sounded too vague in my request. If you need further
detail, I'm happy to supply it.

Many thanks for your time
M

I did something like this to do that... Set up a button on the form that
calls a query. The query will request the search term.

In the click event of the button:
===== (VBA)
Private Sub cmdPersonSearch_Click()
DoFilterButton ("qPersonSearch")
End Sub
=====

...and also in a class module (because the 'DoFilterButton' sub handles
several different searches)...

===== (VBA)
Private Sub DoFilterButton(QueryName As String)
On Error GoTo FilterErr
Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL

Exit_DoFilterButton:
Exit Sub

FilterErr:
MsgBox Err.Description
Resume Exit_DoFilterButton
End Sub
=====

In a query named PersonSearch:

===== (SQL)
SELECT *
FROM A
WHERE A.ID IN
(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
=====

Any good?

There is a typo in my code: the query should be named *qPersonSearch*.
I'm not sure if that's the problem or not and don't have handy access to
the help files to suss out Error 35. Can you break execution and tell me
what is throwing the error?

Also note that as coded here the button goes on the main form, not the
subform.

 

[Someone]

Hi

The code breaks on 'Private Sub btnContactSearch_Click()' (highlighted
yellow) and 'DoFilterButton' is highlighted in blue. I'm quoting the text -
the quotes don't appear in the code!

Thanks again - I do appreciate it.

M

Hi

Thanks for taking the time to respond to me.

What you said sounds perfect. I tried incorporating it, but I'm getting
the following error when attempting to perform a search:

Sub, Function, or Property not defined (Error 35)

Can you help me resolve this?

Thank you
M

Someone wrote:

Hello

I need to incorporate a search facility into my database. Let me give a
simple overview on how it's set up.

There is a main form which has a subform in datasheet view. The subform
is based on a query to a table (call it table a). The reason for this is
that the main form has unbound text boxes so users can search the data
in the subform (the view is requeried to show like records on the data
entered - Like "*" & [FormControlName] etc).

Amongst the buttons on the main form is one to open another form with
three tabs. The form opens to the same record that is selected in the
main form's subform. The default tab shows ALL fields of the record
selected from the subform. On the second tab is another subform in form
view for a different table (call it table b - it's actually a table of
contact details). The third tab is irrelevant for this post.

The relationship between table a and b is a one-to-many, where 1 record
in table a can refer to many records in table b (i.e. a record in table
a could have more than one contact name and/or address).

I've been asked to incorporate a search facility on the contacts
(table). It would need to locate the related record in table a (i.e. the
master record). The problem is, I can't figure out the best way to do
it.

Ideally, I'd like to have a search form with textboxes that the user can
fill in with their required search criteria. This then finds the related
record in table a, based on the search just made on table b. If
possible, I'd like this to requery the data on the main form's subform
so the user can click open the record of their choosing. I've tried,
but it's gone beyond me. Do anyone of you have any ideas on how you
would go about this and what I'd need to do to accomplish it? You might
think my idea is over-complicated and that there is a more
straight-forward idea to fulfil this.

I do hope I haven't sounded too vague in my request. If you need
further detail, I'm happy to supply it.

Many thanks for your time
M

I did something like this to do that... Set up a button on the form that
calls a query. The query will request the search term.

In the click event of the button:
===== (VBA)
Private Sub cmdPersonSearch_Click()
DoFilterButton ("qPersonSearch")
End Sub
=====

...and also in a class module (because the 'DoFilterButton' sub handles
several different searches)...

===== (VBA)
Private Sub DoFilterButton(QueryName As String)
On Error GoTo FilterErr
Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL

Exit_DoFilterButton:
Exit Sub

FilterErr:
MsgBox Err.Description
Resume Exit_DoFilterButton
End Sub
=====

In a query named PersonSearch:

===== (SQL)
SELECT *
FROM A
WHERE A.ID IN
(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
=====

Any good?

There is a typo in my code: the query should be named *qPersonSearch*. I'm
not sure if that's the problem or not and don't have handy access to the
help files to suss out Error 35. Can you break execution and tell me what
is throwing the error?

Also note that as coded here the button goes on the main form, not the
subform.

 

[Smartin]

Hi

The code breaks on 'Private Sub btnContactSearch_Click()' (highlighted
yellow) and 'DoFilterButton' is highlighted in blue. I'm quoting the text -
the quotes don't appear in the code!

Thanks again - I do appreciate it.

M

Hi

Thanks for taking the time to respond to me.

What you said sounds perfect. I tried incorporating it, but I'm getting
the following error when attempting to perform a search:

Sub, Function, or Property not defined (Error 35)

Can you help me resolve this?

Thank you
M


Someone wrote:


Hello

I need to incorporate a search facility into my database. Let me give a
simple overview on how it's set up.

There is a main form which has a subform in datasheet view. The subform
is based on a query to a table (call it table a). The reason for this is
that the main form has unbound text boxes so users can search the data
in the subform (the view is requeried to show like records on the data
entered - Like "*" & [FormControlName] etc).

Amongst the buttons on the main form is one to open another form with
three tabs. The form opens to the same record that is selected in the
main form's subform. The default tab shows ALL fields of the record
selected from the subform. On the second tab is another subform in form
view for a different table (call it table b - it's actually a table of
contact details). The third tab is irrelevant for this post.

The relationship between table a and b is a one-to-many, where 1 record
in table a can refer to many records in table b (i.e. a record in table
a could have more than one contact name and/or address).

I've been asked to incorporate a search facility on the contacts
(table). It would need to locate the related record in table a (i.e. the
master record). The problem is, I can't figure out the best way to do
it.

Ideally, I'd like to have a search form with textboxes that the user can
fill in with their required search criteria. This then finds the related
record in table a, based on the search just made on table b. If
possible, I'd like this to requery the data on the main form's subform
so the user can click open the record of their choosing. I've tried,
but it's gone beyond me. Do anyone of you have any ideas on how you
would go about this and what I'd need to do to accomplish it? You might
think my idea is over-complicated and that there is a more
straight-forward idea to fulfil this.

I do hope I haven't sounded too vague in my request. If you need
further detail, I'm happy to supply it.

Many thanks for your time
M

I did something like this to do that... Set up a button on the form that
calls a query. The query will request the search term.

In the click event of the button:
===== (VBA)
Private Sub cmdPersonSearch_Click()
DoFilterButton ("qPersonSearch")
End Sub
=====

...and also in a class module (because the 'DoFilterButton' sub handles
several different searches)...

===== (VBA)
Private Sub DoFilterButton(QueryName As String)
On Error GoTo FilterErr
Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL

Exit_DoFilterButton:
Exit Sub

FilterErr:
MsgBox Err.Description
Resume Exit_DoFilterButton
End Sub
=====

In a query named PersonSearch:

===== (SQL)
SELECT *

FROM A

WHERE A.ID IN
(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
=====

Any good?

There is a typo in my code: the query should be named *qPersonSearch*. I'm
not sure if that's the problem or not and don't have handy access to the
help files to suss out Error 35. Can you break execution and tell me what
is throwing the error?

Also note that as coded here the button goes on the main form, not the
subform.

Sorry, there's a very misleading statement in my OP. I must have been
tired (^:

As coded above, DoFilterButton must be in the *same* module (i.e., your
form code) as the button click event. Alternatively, you should be able
to place the generic sub in a separate module and refer to it explicitly
in the click event thus:

===== (VBA)
Private Sub cmdPersonSearch_Click()
ModuleName.DoFilterButton ("qPersonSearch")
End Sub

Sorry about that! How are we doing now?

 

[Someone]

Hi!

I don't know how much to thank you for your help. It worked and I'm so very
grateful to you.

Thank you for taking the time to help me resolve my problem.

M

Hi

The code breaks on 'Private Sub btnContactSearch_Click()' (highlighted
yellow) and 'DoFilterButton' is highlighted in blue. I'm quoting the
text - the quotes don't appear in the code!

Thanks again - I do appreciate it.

M

Someone wrote:

Hi

Thanks for taking the time to respond to me.

What you said sounds perfect. I tried incorporating it, but I'm getting
the following error when attempting to perform a search:

Sub, Function, or Property not defined (Error 35)

Can you help me resolve this?

Thank you
M


Someone wrote:


Hello

I need to incorporate a search facility into my database. Let me give
a simple overview on how it's set up.

There is a main form which has a subform in datasheet view. The
subform is based on a query to a table (call it table a). The reason
for this is that the main form has unbound text boxes so users can
search the data in the subform (the view is requeried to show like
records on the data entered - Like "*" & [FormControlName] etc).

Amongst the buttons on the main form is one to open another form with
three tabs. The form opens to the same record that is selected in the
main form's subform. The default tab shows ALL fields of the record
selected from the subform. On the second tab is another subform in
form view for a different table (call it table b - it's actually a
table of contact details). The third tab is irrelevant for this post.

The relationship between table a and b is a one-to-many, where 1
record in table a can refer to many records in table b (i.e. a record
in table a could have more than one contact name and/or address).

I've been asked to incorporate a search facility on the contacts
(table). It would need to locate the related record in table a (i.e.
the master record). The problem is, I can't figure out the best way
to do it.

Ideally, I'd like to have a search form with textboxes that the user
can fill in with their required search criteria. This then finds the
related record in table a, based on the search just made on table b.
If possible, I'd like this to requery the data on the main form's
subform so the user can click open the record of their choosing. I've
tried, but it's gone beyond me. Do anyone of you have any ideas on
how you would go about this and what I'd need to do to accomplish it?
You might think my idea is over-complicated and that there is a more
straight-forward idea to fulfil this.

I do hope I haven't sounded too vague in my request. If you need
further detail, I'm happy to supply it.

Many thanks for your time
M

I did something like this to do that... Set up a button on the form
that calls a query. The query will request the search term.

In the click event of the button:
===== (VBA)
Private Sub cmdPersonSearch_Click()
DoFilterButton ("qPersonSearch")
End Sub
=====

...and also in a class module (because the 'DoFilterButton' sub handles
several different searches)...

===== (VBA)
Private Sub DoFilterButton(QueryName As String)
On Error GoTo FilterErr
Form_A.RecordSource = CurrentDb.QueryDefs(QueryName).SQL

Exit_DoFilterButton:
Exit Sub

FilterErr:
MsgBox Err.Description
Resume Exit_DoFilterButton
End Sub
=====

In a query named PersonSearch:

===== (SQL)
SELECT *

FROM A

WHERE A.ID IN
(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
WHERE B.PersonName LIKE '*' & [Enter a name to search for] & '*');
=====

Any good?
--
Smartin



There is a typo in my code: the query should be named *qPersonSearch*.
I'm not sure if that's the problem or not and don't have handy access to
the help files to suss out Error 35. Can you break execution and tell me
what is throwing the error?

Also note that as coded here the button goes on the main form, not the
subform.

Sorry, there's a very misleading statement in my OP. I must have been
tired (^:

As coded above, DoFilterButton must be in the *same* module (i.e., your
form code) as the button click event. Alternatively, you should be able to
place the generic sub in a separate module and refer to it explicitly in
the click event thus:

===== (VBA)
Private Sub cmdPersonSearch_Click()
ModuleName.DoFilterButton ("qPersonSearch")
End Sub

Sorry about that! How are we doing now?

 

[Smartin]

Hi!

I don't know how much to thank you for your help. It worked and I'm so very
grateful to you.

Thank you for taking the time to help me resolve my problem.

M

You're welcome, and glad to know you got it working!

 

[Someone]

You're welcome, and glad to know you got it working!

I hope you're still here!

The contacts form I have has 12 fields that I want to be able to search on.
How can I incorporate this into one query? I seem to either do it wrong or
Access complains that I've exceeded 1024 characters!

Do you think this will be possible?

Thanks
M

 

[Smartin]

I hope you're still here!

The contacts form I have has 12 fields that I want to be able to search on.
How can I incorporate this into one query? I seem to either do it wrong or
Access complains that I've exceeded 1024 characters!

Do you think this will be possible?

Thanks
M

I'm not quite sure what you mean by this... you want to search 12 fields
for the same term? This sounds like an ugly design, and I hope it's not
what you mean, but such a search could be accomplished by modifying the
query:

===== (SQL)
PARAMETERS [Enter a name to search for] Text;
SELECT *
FROM A
WHERE A.ID IN
(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
WHERE
B.PersonName LIKE '*' & [Enter a name to search for] & '*') OR
B.Something1 LIKE '*' & [Enter a name to search for] & '*') OR
B.Something2 LIKE '*' & [Enter a name to search for] & '*') OR
....repeat ad nauseum...
;
=====

Clarify?

 

[Someone]

I hope you're still here!

The contacts form I have has 12 fields that I want to be able to search
on. How can I incorporate this into one query? I seem to either do it
wrong or Access complains that I've exceeded 1024 characters!

Do you think this will be possible?

Thanks
M

I'm not quite sure what you mean by this... you want to search 12 fields
for the same term? This sounds like an ugly design, and I hope it's not
what you mean, but such a search could be accomplished by modifying the
query:

===== (SQL)
PARAMETERS [Enter a name to search for] Text;
SELECT *
FROM A
WHERE A.ID IN
(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID
WHERE
B.PersonName LIKE '*' & [Enter a name to search for] & '*') OR
B.Something1 LIKE '*' & [Enter a name to search for] & '*') OR
B.Something2 LIKE '*' & [Enter a name to search for] & '*') OR
...repeat ad nauseum...
;
=====

Clarify?

Hello again

What I meant to say was that the form has 12 different fields (contact,
jobtitle, address1, address2, town, county, postcode, country, telephone,
fax, email, website).

I'd like for the user to be able to enter criteria into any one or more of
these fields to find matching/like records.

I tried the idea you mentioned above, but the records returned for any
search in any field are the same, which isn't correct.

One point: in your SQL statement, you've put the following:

(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID

Did you actually mean B.AID or B.ID? If you did mean B.AID, how do I write
that in SQL?

Thanks!
M

 

[Smartin]

Hello again

What I meant to say was that the form has 12 different fields (contact,
jobtitle, address1, address2, town, county, postcode, country, telephone,
fax, email, website).

I'd like for the user to be able to enter criteria into any one or more of
these fields to find matching/like records.

I tried the idea you mentioned above, but the records returned for any
search in any field are the same, which isn't correct.

Right, I showed a way to search all the contact fields at once for the
same term. So you want to be able to search a specific contact field for
a specific term? This is not difficult in principle but it does create a
bit of a design nightmare.

Space is limited. How to add, say, 12 command buttons to fire 12
different searches? Yuck. You might be able to condense this to one
combo box (to pick the field to search) and one button (to fire the
search), but this eliminates your ability to entire criteria for
multiple fields.

Alternatively you could build a new form "Search" with 12 text boxes or
whatever and some way to do the "and" and "or" logic that you might
want, and add one button to the main form that calls up "Search".
Building the SQL from the "Search" stuff is bound to be ugly.

Consider this though: you have contact records with 12 fields. If you
want to find all contacts with "david" anywhere in the contact info, the
code I posted last night will return "David Smith" from the name, or
"(e-mail address removed)" from the email, or "1234 David street" from the
address, etc. Wouldn't this be acceptable?

I have a feeling from what you said next that you don't quite have the
SQL corresponding correctly to your tables, so read on...

One point: in your SQL statement, you've put the following:

(SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.AID

Did you actually mean B.AID or B.ID? If you did mean B.AID, how do I write
that in SQL?

Thanks!
M

B.AID is a way to illustrate that it is a foreign key in the "B" table
referring to the "ID" field in the "A" table. Let's change the table and
field names to make this easier to visualize.

"A" is the table bound to the main form. Let's call the table "Jobs".
"B" is the table bound to the subform, let's call that "Contacts". These
tables might be constructed thus:

Jobs
----
JobID
JobDate
JobType
etc...

Contacts
--------
ContactID
JobID <-- links this contact to a job. One job can have many contacts
ContactName
ContactTelephone
etc...

The query now looks like this:

SELECT Jobs.JobID FROM Jobs LEFT JOIN Contacts ON Jobs.JobID =
Contacts.JobID

Does this make it clearer?

 

[Someone]

Right, I showed a way to search all the contact fields at once for the
same term. So you want to be able to search a specific contact field for a
specific term? This is not difficult in principle but it does create a bit
of a design nightmare.

Space is limited. How to add, say, 12 command buttons to fire 12 different
searches? Yuck. You might be able to condense this to one combo box (to
pick the field to search) and one button (to fire the search), but this
eliminates your ability to entire criteria for multiple fields.

Alternatively you could build a new form "Search" with 12 text boxes or
whatever and some way to do the "and" and "or" logic that you might want,
and add one button to the main form that calls up "Search". Building the
SQL from the "Search" stuff is bound to be ugly.

Consider this though: you have contact records with 12 fields. If you want
to find all contacts with "david" anywhere in the contact info, the code I
posted last night will return "David Smith" from the name, or
"(e-mail address removed)" from the email, or "1234 David street" from the
address, etc. Wouldn't this be acceptable?

I have a feeling from what you said next that you don't quite have the SQL
corresponding correctly to your tables, so read on...



B.AID is a way to illustrate that it is a foreign key in the "B" table
referring to the "ID" field in the "A" table. Let's change the table and
field names to make this easier to visualize.

"A" is the table bound to the main form. Let's call the table "Jobs". "B"
is the table bound to the subform, let's call that "Contacts". These
tables might be constructed thus:

Jobs
----
JobID
JobDate
JobType
etc...

Contacts
--------
ContactID
JobID <-- links this contact to a job. One job can have many contacts
ContactName
ContactTelephone
etc...

The query now looks like this:

SELECT Jobs.JobID FROM Jobs LEFT JOIN Contacts ON Jobs.JobID =
Contacts.JobID

Does this make it clearer?

Hi

You don't mind helping me so much do you? I feel like I'm intruding!

Here is the SQL for that I wrote based on what you gave earlier in this
thread:

SELECT *
FROM tbl_Direction
WHERE (((tbl_Direction.ID) In (SELECT tbl_Direction.ID FROM tbl_Direction
LEFT JOIN tbl_Contacts ON tbl_Direction.ID = tbl_Contacts.SPID
WHERE tbl_Contacts.Contact LIKE '*' &
[Forms]![frm_Contact_Search]![txtContact] & '*')));

Does it look correct? It seems to work, although depending on the search
criteria, I am curious as to whether it really is correct or not!

The matching field between the two tables is an autonumber (which is also
the primary key in each table). While there could be a better field to
match the two, the way I have the forms set out means that the user would
have to re-type this other field for each contact associated with the record
in tbl_Direction. I therefore chose to use an autonumber and assign a
relationship between the autonumber in each table. It works for my purposes
anyway

What I created was a form that has an unbound field for each field of the
tbl_Contacts table. The idea was that the user could search on a particular
field and it would return appropriate records based on that particular
field. From what you've now clarified, it seems I'd only need one field and
that would return matching records whether the matching field is a person's
name or part of an address - am I right? The idea seems quite good,
although I'd be concerned if a user wanted to search on a contact name and
it returned records where the name happened to appear in the address
somewhere, for example.

Also, I tried to incorporate the idea of OR in the query (where you said 'ad
nauseum'), but I get error messages when trying to save, telling me the
syntax is wrong. The only way I can get it to save is by using the two
where clauses for each field, which makes the query very large. At this
stage, I get the error that I've exceeded 1,024 characters.

It looks like I will have to go with your original idea, assuming I've
written it correctly!

What a performance, eh?

Thanks again
M

 

[Smartin]

Hi

You don't mind helping me so much do you? I feel like I'm intruding!

Not at all. This is what I love about Usenet... where else can you tap
in to the knowledge of others so easily? And believe me, I'm no
expert--I come here to get information too. I enjoy sharing what little
information I have.

Here is the SQL for that I wrote based on what you gave earlier in this
thread:

SELECT *
FROM tbl_Direction
WHERE (((tbl_Direction.ID) In (SELECT tbl_Direction.ID FROM tbl_Direction
LEFT JOIN tbl_Contacts ON tbl_Direction.ID = tbl_Contacts.SPID
WHERE tbl_Contacts.Contact LIKE '*' &
[Forms]![frm_Contact_Search]![txtContact] & '*')));

Does it look correct? It seems to work, although depending on the search
criteria, I am curious as to whether it really is correct or not!

Looks like it should work. Curious why you use the subquery? Seems like
this could be simplified:

SELECT *
FROM tbl_Direction
LEFT JOIN tbl_Contacts ON tbl_Direction.ID = tbl_Contacts.SPID
WHERE tbl_Contacts.Contact LIKE '*' &
[Forms]![frm_Contact_Search]![txtContact] & '*')));

My only other thought is to make it case-insensitive, something like

WHERE UCase(tbl_Contacts.Contact) LIKE '*' &
UCase([Forms]![frm_Contact_Search]![txtContact]) & '*')));

....that way a search for "SMITH" will always find "Smith". This may not
be an issue for you, so this is just a thought.

The matching field between the two tables is an autonumber (which is also
the primary key in each table). While there could be a better field to
match the two, the way I have the forms set out means that the user would
have to re-type this other field for each contact associated with the record
in tbl_Direction. I therefore chose to use an autonumber and assign a
relationship between the autonumber in each table. It works for my purposes
anyway

I think that's the best way to go actually. The keys don't need to be
exposed to the user, and autonumber ensures uniqueness.

What I created was a form that has an unbound field for each field of the
tbl_Contacts table. The idea was that the user could search on a particular
field and it would return appropriate records based on that particular
field. From what you've now clarified, it seems I'd only need one field and
that would return matching records whether the matching field is a person's
name or part of an address - am I right? The idea seems quite good,
although I'd be concerned if a user wanted to search on a contact name and
it returned records where the name happened to appear in the address
somewhere, for example.

Exactly! Your requirements will determine which way is best. There's a
trade-off of course... The more "precise" search demands more code (not
necessarily a bad thing) and a more complicated UI (possibly a bad
thing). The "universal" search simplifies the UI (probably a good thing)
and but could return undesired records (your call on this one (^: ).

Also, I tried to incorporate the idea of OR in the query (where you said 'ad
nauseum'), but I get error messages when trying to save, telling me the
syntax is wrong. The only way I can get it to save is by using the two
where clauses for each field, which makes the query very large. At this
stage, I get the error that I've exceeded 1,024 characters.

It looks like I will have to go with your original idea, assuming I've
written it correctly!

What a performance, eh?

Thanks again
M

You're welcome and good luck!

 

[Someone]

Hi

You don't mind helping me so much do you? I feel like I'm intruding!

Not at all. This is what I love about Usenet... where else can you tap
in to the knowledge of others so easily? And believe me, I'm no
expert--I come here to get information too. I enjoy sharing what little
information I have.

Here is the SQL for that I wrote based on what you gave earlier in this
thread:

SELECT *
FROM tbl_Direction
WHERE (((tbl_Direction.ID) In (SELECT tbl_Direction.ID FROM tbl_Direction
LEFT JOIN tbl_Contacts ON tbl_Direction.ID = tbl_Contacts.SPID
WHERE tbl_Contacts.Contact LIKE '*' &
[Forms]![frm_Contact_Search]![txtContact] & '*')));

Does it look correct? It seems to work, although depending on the search
criteria, I am curious as to whether it really is correct or not!

Looks like it should work. Curious why you use the subquery? Seems like
this could be simplified:

SELECT *
FROM tbl_Direction
LEFT JOIN tbl_Contacts ON tbl_Direction.ID = tbl_Contacts.SPID
WHERE tbl_Contacts.Contact LIKE '*' &
[Forms]![frm_Contact_Search]![txtContact] & '*')));

My only other thought is to make it case-insensitive, something like

WHERE UCase(tbl_Contacts.Contact) LIKE '*' &
UCase([Forms]![frm_Contact_Search]![txtContact]) & '*')));

....that way a search for "SMITH" will always find "Smith". This may not
be an issue for you, so this is just a thought.

The matching field between the two tables is an autonumber (which is also
the primary key in each table). While there could be a better field to
match the two, the way I have the forms set out means that the user would
have to re-type this other field for each contact associated with the record
in tbl_Direction. I therefore chose to use an autonumber and assign a
relationship between the autonumber in each table. It works for my purposes
anyway

I think that's the best way to go actually. The keys don't need to be
exposed to the user, and autonumber ensures uniqueness.

What I created was a form that has an unbound field for each field of the
tbl_Contacts table. The idea was that the user could search on a particular
field and it would return appropriate records based on that particular
field. From what you've now clarified, it seems I'd only need one field and
that would return matching records whether the matching field is a person's
name or part of an address - am I right? The idea seems quite good,
although I'd be concerned if a user wanted to search on a contact name and
it returned records where the name happened to appear in the address
somewhere, for example.

Exactly! Your requirements will determine which way is best. There's a
trade-off of course... The more "precise" search demands more code (not
necessarily a bad thing) and a more complicated UI (possibly a bad
thing). The "universal" search simplifies the UI (probably a good thing)
and but could return undesired records (your call on this one (^: ).

Also, I tried to incorporate the idea of OR in the query (where you said 'ad
nauseum'), but I get error messages when trying to save, telling me the
syntax is wrong. The only way I can get it to save is by using the two
where clauses for each field, which makes the query very large. At this
stage, I get the error that I've exceeded 1,024 characters.

It looks like I will have to go with your original idea, assuming I've
written it correctly!

What a performance, eh?

Thanks again
M

You're welcome and good luck!

--
Smartin

Hi

After some general Googling and trial and error by me, I've worked out what
I needed to do.

I still select all from table a and left join to table b, but I then have a
where statement for each field showing on the search form thus:

WHERE tbl_Contacts.Contact LIKE "*" & [Forms]![frmName]![frmControl] & "*"
OR [Forms]![frmName]![frmControl] Is Null
WHERE tbl_Contacts.JobTitle LIKE "*" & [Forms]![frmName]![frmControl] & "*"
OR [Forms]![frmName]![frmControl] Is Null
etc

The Is Null part was always essential, as otherwise, records where the field
is blank would always be returned.

Anyway, after all this, we've got it to work!

Many thanks for all your help - it's been most appreciated.

M