Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unanswered: Help With Relationships - Can't see all records

    This is a strange problem I hope someone can help..

    TableA is a customer table and TableB is a Notes table for the customer. Not every customer has a note in tableB. When I click on the button for finding a Customer, even though the customer exists in TableA, if there is no entry in TableB, I can't pull up the record.

    Here is the code from the control, if that helps.:
    Private Sub Find_Book_AfterUpdate()

    Me.RecordsetClone.FindFirst "[Book_ID] = " & Str(Me![Find_Book])
    Me.Bookmark = Me.RecordsetClone.Bookmark
    'Me!Combo111.Requery

    Mainform_Lock True

    End Sub

  2. #2
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    You need the relationship between the two table to be a left join:

    TableA LEFT JOIN Table B Where TableA.CustomerID=TableB.CustomerID

    That should show all the records from table A, and only those corresponding for table B.

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Thanks, that did it!

  4. #4
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    Good!! Glad to hear it was helpful.

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    Quote Originally Posted by databasemon
    Thanks, that did it!
    Hi databasemon,

    glad to hear that worked but just had one question to ask. Did you already have the Relationships for your tables set? One-To-Many? I know you can do it in the query, but thought if you had tables already related it would pull the data over anyway, at least that's the way all of mine work without having to do it in the query. Just asking.

    hope you have a nice day
    Bud

  6. #6
    Join Date
    Nov 2003
    Posts
    300
    No, for some reason they were not setup in the Tables, one index field name had changed and through the whole thing off!

Posting Permissions

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