Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    check for related record in another table (97) (97)

    Hi. i'm a rookie and would like to know how to check if a record in one table has a related record in another (ie. unique identifier in record from first table = unique indentifier in a record from table 2)? thanks, Van

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

    Re: check for related record in another table (97) (97)

    It depends on how and where you want to do this.

    If you want to create a query that shows which records don't have a match:
    <UL><LI>Click 'New' in the Queries tab of the database window.
    <LI>Select the 'Find Unmatched Query Wizard' and click OK.
    <LI>In the first step, select the "one" table. Click 'Next'.
    <LI>In the second step, select the "other" table. Click 'Next'.
    <LI>In the third step, select the unique identifier on both sides and click '< = >'. Then click 'Next'.
    <LI>In the fourth step, select each of the fields you want to display and click >. Then click 'Next'.
    <LI>In the last step, change the name of the query if you like, and specify whether you want to see the design or the result. Then click 'Finish'.[/list]If you want to create a query that shows which records have a match, create a query based on both tables and join them on the unique identifier. Add the fields you want to see to the query grid. Then switch to datasheet view.

    If you would like something else, post back.

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for related record in another table (97) (97)

    thanks Hans, but that's not what i'm after. In form view (looking at a single record), i'd like to be able to have a flag which would either be visible or not depending on whether or not this person has another record in another table. the unique identifier (primary key) I am using is called "UR#". thanks. Van

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

    Re: check for related record in another table (97) (97)

    Thanks for the clarification. Let's say the the "other" table is named tblOther, and that UR# is a numeric field.

    We'll start by something simple. It's not exactly what you want, but it'll give you an idea how it works. Put a text box in the detail section of the form, and set its Control Source property to

    =DCount("*", "tblOther", "[UR#]=" & [UR#])

    Switch to form view. As you move from record to record, the text box should display 0 if there is no matching record, 1 (or more) if there is a matching record.

    You say that you want to make a flag visible/invisible. You can use the above expression in code to accomplish this. Let's say that the control you want to show/hide is named ctlFlag (I don't know what type it will be, but that is not really important). Write code in the On Current event of the form:

    Private Sub Form_Current()
    Me.[ctlFlag].Visible = DCount("*", "tblOther", "[UR#]=" & Me.[UR#])
    End Sub

  5. #5
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for related record in another table (97) (97)

    thanks Hans, that the kind of thing i'm after.....i'm sure i'll be able to make it work. much appreciated. Van

  6. #6
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for related record in another table (97) (97)

    sorry Hans, is was able to get the text box to read either 0 or 1 no problems.....works well. however, i'm unable to get the flag portion to work. the flag is a label (ie. Label106), visible property set to false. i've tried various code, etc. but to no avail. have you any suggestions? thanks again, Van

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

    Re: check for related record in another table (97) (97)

    I have attached a very simple demo database (Access 97, zipped), with two tables and a form. The On Current event of the form makes a label visible/invisible using the code I posted earlier. Open the form to see it in action, then switch to design view to see how it works. Don't forget to look at the code.
    Attached Files Attached Files

  8. #8
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for related record in another table (97) (97)

    thanks heaps Hans. it works great! i just had to move that line of code futher up in the hierarchy (above some code which calculates the patient's age from their date of birth). thanks again. Van

  9. #9
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for related record in another table (97) (97)

    Hans, i've been working through updating my database with your eloquent solution, however i have run into a snag in that i can seem to make it work on a continuous form??? (works well without probs in single form view). have you any suggestions? thanks. Van

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

    Re: check for related record in another table (97) (97)

    A continuous form essentially only contains one set of controls, even if it displays many records. So if you make a label in the detail section (in)visible, it becomes (in)visible in all records.

    You must use a text box to simulate it in a continuous form. Try the following:
    <UL><LI>Delete the label.
    <LI>Delete the part in the On Current event handler that shows/hides the label.
    <LI>Put a text box in the detail section where the label was.
    <LI>Set the Control Source to:

    =IIf(DCount("*", "tblOther", "[UR#]=" & [UR#]) > 0, "Match!")

    where tblOther is the name of the table to check for a match.
    <LI>Set the Enabled property to False and the Locked property to True.
    <LI>You'll probably want to set the Border Style and Background Style to Transparent.[/list]HTH

Posting Permissions

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