Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding records on a form (2000)

    Hi,
    I have the attached database which I am having some issues resolving.
    Basically FrmMain needs to be searched by URN, but also on this form the user needs to be able to search by the records in FrmDefendantSub, DefSurname and DefForename. But still display the same record on FrmMain.
    FrmMain originally had the option of adding one defendant only and therefore DefSurname & DefForename was originally on FrmMain, however there has been a need to add multiple defendants, hence FrmDefendant
    I'm initially stuck on this, but additional to this is the fact that FrmMain is filtered anyway, so its not as easy as adding a combo box via the wizard.
    Any help, naturally, would be appreciated.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding records on a form (2000)

    Please explain more clearly what you want to do. I don't see any search boxes or buttons on FrmMain.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding records on a form (2000)

    Hi,
    No there isn't any search boxes on FrmMain.
    However I do need to search the database by the field URN in FrmMain or the Field, DefSurname in FrmDefendantSub.
    Because each case can have multiple defendants the end user has requested that they can search by defendant, as well as the Unique Reference Number (URN). Both of these fields are in seperate tables.
    Normally I would simply place a combo box on FrmMain and use the wizard to search the form. But due to the filters in place from FrmStartUp this isn't possible.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding records on a form (2000)

    You should still be able to use a combo box, but you'll have to write the code yourself.
    The combo box will display the defendants' surnames but it will have DefendantID as a hidden first column.

    Create a query based on TblMain and on TblDefendant, joined on DefendantID. Add the criteria you are using in QryMain, and also add a condition on DefendantID referring to the value of the combo box.
    This query will return the records (cases) for the selected defendant.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding records on a form (2000)

    Sorry to get back to this a month after the event but I've only just got around to looking at this.
    Presumable I am duplicating QryMain with the added addition of TblDefendant, joined on DefendantID.
    In the new query, DefendantID has the criteria of:
    =[FrmMain]![Combo53]![Column0]

    Is this query replacing QryMain as the main source of data for FrmMain.

    Or do I base a new SQL statement on the data source of Combo53 including the above criteria?

    Sorry but I'm not overly clear on what I need to do.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding records on a form (2000)

    I downloaded the database from the first post in this thread again, and I fear it won't work. The tblDefendants table has multiple records for the same defendant with different DefendantIDs. You should change the design so that each defendant has a unique ID.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding records on a form (2000)

    So if I change the Index in TblDefendants, DefendantID to Yes (No Duplicates) will this solve the problem?
    Or will I need an adjoining table between TblMain and TblDefendant with something like CaseID, DefendantID and JoinedID?

    If I change the indexing, in TblDefendant, this should in theory display duplcate CaseID's on each defendant where there is more than one defendant per case. Then with each defendant the will be a unique DefendantID:
    So something like:

    DefendantID CaseID DefSurname DefForename
    42 11 SMITH Adam
    43 11 JONES Richard
    44 11 WILLIAMS Ian
    45 12 PETTEFAR Jocelyn
    46 13 JACKSON Tabitha

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding records on a form (2000)

    You need to create two tables:

    1) A table tblDefendants listing unique defendants, without case information. This table has DefendantID as primary key, plus DefSurname, DefForename and other data about the defendant.
    2) A table tblDefendantCases with CaseID and DefendantID with primary key on the combination of those fields. If a case has 2 defendants, there will be 2 records for this CaseID. If a defendant is involved in 3 cases, there will be 3 records for this DefendantID. The Finalised field may belong in this table, but that's for you to decide.

    Link tblMain to tblDefendantCases on CaseID in the relationships window.
    Link tblDefendants to tblDefendantCases on DefendantID.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding records on a form (2000)

    See the attached version. I modified the table structure and added a search combo box to FrmMain.

    Note: I disabled some code such as the code that prevents the user from closing a form, because it made testing impossible.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •