Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Looking up existing records in several subforms all together

Discussion in 'Information Technology' started by Kernow Girl, Nov 8, 2005.

  1. Kernow Girl

    Kernow Girl
    Expand Collapse
    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
     
  2. Loading...


  3. Peter Danes

    Peter Danes
    Expand Collapse
    Guest

    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.


    "Kernow Girl" <KernowGirl@discussions.microsoft.com> pí¹e v diskusním
    pøíspìvku news:4C26B1BF-7B20-4A34-A0C3-49D5D6C0B0C8@microsoft.com...
    > 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
     

Share This Page