Search all subforms

[Robert Jacobs]

Please Help!

I have read over and over again to go to Allen Browne's website to
filter subforms, but I just don't understand it, and I can't get it to
work in my situation. Any advice is very appreciated.

I have 2 forms. Mainform=Customers Subform(linked)=Service
Requests.

The two forms have a one to many relationship (many service requests
per customer) and are related by customer number (auto number that
automatically is filled in subform to match field on mainform). Each
service request has a txtBox for TicketID. I want to be able to
search through all of the TicketIDs in all of the customer's service
requests (TicketID is not an auto number and can't be used as primary
key-it is a number typed from a tablet of paper with a number at the
top-there could be multiple instances of this number if he orders a
new tablet of paper and it starts with the same number-very very small
company). I have created another subform (which may not be needed,
but I thought it might help) that just displays the TicketIDs (all
TicketIDs related to the customer number) from the service request
table on the mainform.

The search button that the wizard creates will not even search the
TicketIDs on the current customer ID's page, none the less searching
through all customer's pages to find the corresponding Ticket IDs.
And when I put the search button in the subform, it will only search
for TicketIDs related to the current customer number (which I like)

Is what I am trying to do possible? I am VERY new to Access (about 2
weeks into it) and just want to make this database for my family's
Clock Shop. Thank you for any help you can provide!

 

[Allen Browne]

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

 

[Robert Jacobs]

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.











- Show quoted text -

Allen,

Thanks for your response. Yes, I had read your MANY MANY posts
regarding this, and read the link you provided. I am just unable to
see how it will fit into my design. I don't have 3 different tables,
and the relationship between the tables I do have don't seem to be the
same as what you have. Also, it looks like for this to work, I need
the TicketID to be the primary key, but it can't be due to the fact
that there may be more than one instance of this ID appearing. I may
be WAY off, but I just am not able to link your code and example to
fit my needs. I'm sorry for the lack of intelligence in this area, I
really don't understand it very well (I'm a very very week coder-this
is the first thing I've ever worked on). If you could show me how
your article is relevant to what I'm doing, or help me fill in the
blanks, I would be very greatful!

 

[Allen Browne]

Hmm: if you use an INNER JOIN as described in that article, and there are
duplicate TicketIDs in the related table, it should work but you would get
some records occurring twice (where the person has 2 tickets.)

An alternative approach might be to use a subquery as the Filter of the
form. This won't give the duplicates problem.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[Robert Jacobs]

Hmm: if you use an INNER JOIN as described in that article, and there are
duplicate TicketIDs in the related table, it should work but you would get
some records occurring twice (where the person has 2 tickets.)

An alternative approach might be to use a subquery as the Filter of the
form. This won't give the duplicates problem.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.






- Show quoted text -

It's kind of like searching through all customers' service requests
for something like Date Received. In other words, if I am trying to
find the next instance of 4/06/07 (which there may be multiple service
requests started on this date), I want to be able to search for
4/06/07 and it find one record, search for next and find the next
record, and so on. Same idea. The TicketID is just a text box that
could be anything (even letters) that I want to be able to search
through. I don't have any queries setup now (not sure how they work
yet). I basically want the exact same seach funtion provided by the
search button wizard that will just search ALL subforms.

 

[Robert Jacobs]

Hmm: if you use an INNER JOIN as described in that article, and there are
duplicate TicketIDs in the related table, it should work but you would get
some records occurring twice (where the person has 2 tickets.)

An alternative approach might be to use a subquery as the Filter of the
form. This won't give the duplicates problem.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.






- Show quoted text -

Actually, do you know of a way to add a textbox to the main form. Add
a button to the main form that checks the ENTIRE service requests
table for a match to what was typed in the textbox? Then, because the
customer ID on the main form and the subform are identical, have it
take you to the corresponding customer ID on the main form? Then if
you search again for the same thing, if it exists it will take you to
the next customer ID who's service request includes it?

 

[Allen Browne]

Well, you can do that kind of thing with with a FindFirst on the
RecorsetClone of the form/subform, and matching bookmarks. (You may need to
DLookup() the foreign key value in the subform's table first, to get the
foreign key value.)

But one thing at a time: a subquery in the Filter string for the main form
so it contains only those records that have the TicketID in the suborm might
be the first step.

 

[Robert Jacobs]

Well, you can do that kind of thing with with a FindFirst on the
RecorsetClone of the form/subform, and matching bookmarks. (You may need to
DLookup() the foreign key value in the subform's table first, to get the
foreign key value.)

But one thing at a time: a subquery in the Filter string for the main form
so it contains only those records that have the TicketID in the suborm might
be the first step.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.







- Show quoted text -

Allen,

Thank you so much for your reply. I am obviously not cut out for
database creation. I can't even figure out where I'm supposed to type
the info. for a query/subquery, none the less understand what pieces
I'm supposed to use for in my situation. Thanks for your help. I
know it should make sense (at least if one were used to this type of
work, or even understood the entire concept of how a database
functions), but I'm apparently getting in to far over my head here.
This was just supposed to be something fun to do on the side (which,
until now it has been), but I just can't grasp ANY of the
documentation I've read on the subject (primarily offered by you).
Even the descriptions and notes are in a foreign language to me. Oh
well.

Again, thanks.