Looking up existing records in several subforms all together

[Guest]

Hi everyone - great group and hope you can help with this.
I will try to ask this as clearly as possible. I have set up a database
holding records about when a client receives therapy (OT or PT) and the
duration of the therapy. The forms are received from several different
offices, but some of the offices also send logs of 'open' clients; ie the
therapy has started but not ended. Sometimes the 'open' logs come after the
main form! As we report on both open and closed records we must know if the
client is alreay on and adust accordingly. Just to add spice to this, there
are 5 data entry modules and 1 main database. Each module holds it own data
which is then sennt to a TEMP file in the main module. The data is scanned
for problems before uploading to the main database. The forms, both open and
closed are hand written so not always clear. To see if a client is alreay on
I have set up several subforms in the data entry modules which search - the
main database by exact name ; by first initial of first name + surname and by
DOB ; the TEMP file by first inital + surname and the entry file in the data
entry module by first initial + surname. As you can imagine it looks very
klugy on screen and means the user needs to read through several subforms to
look for a match.
Does anyone have an idea of how to compress this in any way!!
Thanks for any ideas

Dika
Kernow Girl

 

[Peter Danes]

You can create a common search routine that looks in all possible places for
all possible combinations of whatever you are trying to find and call that
routine from any of several different places. You would probably have "Find"
command buttons or maybe Exit event code attached to textboxes in each of
the places from which you would call the routine.

That routine would assemble an SQL text string with all the proper search
parameters and execute it. SQL is perfectly capable of looking in many
different places for many different thing in one call. The syntax can be a
litle tricky to put together, but if you experiment with graphic query
builder to search individual tables and look at the SQL it generates, you
should be able to figure it out. Here is an example of just such a statement
from a project I'm currently building. The entire thing is generated by my
VBA code in response to options checked and text filled out on a form. Here
I have it looking for the text "xxx" anywhere in different fields of five
different tables with various types of linkage.

SELECT RM.* FROM SeznamCasopisu INNER JOIN RM ON
SeznamCasopisu.TitulCasopisuID = RM.TitulCasopisuID WHERE
(SeznamCasopisu.TitulCasopisu Like "*xxx*") OR (RM.NazevKnihyTitulClanku
Like "*xxx*") OR RM.NRMAutoID IN (SELECT Autori.NRMAutoId FROM SeznamAutoru
INNER JOIN Autori ON SeznamAutoru.AutorID = Autori.AutorID WHERE
SeznamAutoru.Autor Like "*xxx*") OR RM.NRMAutoID IN (SELECT
Keywordy.NRMAutoId FROM SeznamKeywordu INNER JOIN Keywordy ON
SeznamKeywordu.KeywordID = Keywordy.KeywordID WHERE SeznamKeywordu.Keyword
Like "*xxx*") OR RM.NRMAutoID IN (SELECT Signatury.NRMAutoId FROM
SeznamSignatur INNER JOIN Signatury ON SeznamSignatur.SignaturaID =
Signatury.SignaturaID WHERE SeznamSignatur.Signatura Like "*xxx*");

--
Pete

This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas.