Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    showing value from recordset (2000 all updates)

    I have a form called frmFamilies, based on tblFamily. There are 3 tab pages on the form. One of the tabs has a subform called fsubContactData, based on tblContactData. They are joined by FamilyID.

    The subform is a continuous form with two fields, which are ContactType and ContactData.
    ContactType is a combo box from which can be selected "Home Phone," "Work Phone 1," "Work Phone 2," "Cell Phone," "Fax," "email address" or "Other." Any or all of these could be selected and a value placed in ContactData. And they could be selected in any order.

    At the top of the main form, I would like to show the Home Phone value if there is one...if not the Work Phone 1 value if there is one...if not the Cell Phone value if there is one.

    If the user were to enter the selections in that order - eg. with Home Phone first if there is one, Work Phone 1 if there isn't a Home Phone, etc. - then this would be a piece of cake. Just grab the top value.

    Is there a way to create a recordset, loop through it, pulling the Home Phone value if there is one, or the Work Phone 1 value if there isn't a Home Phone value, or the Cell Phone value if there isn't either a Home Phone or a Work Phone 1 value?

    Thanks.

    Tom

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

    Re: showing value from recordset (2000 all updates)

    You can use queries to retrieve the phone number you want, then use DLookup on your main form to display it (the query itself will not be updateable)

    See attached demo.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: showing value from recordset (2000 all updates

    Hans
    Thanks for the insight and the example you attached. I wasn't 100% clear why it worked, but it certainly worked. In particular, I wasn't sure about the query called "qryMinCTID."

    In your example, you had both a ContactType and a ContactData table. In my design, both the ContactType and the ContactData are in one table. I could switch the design, but thought I would try something before I did that. So I created a query that includes the FamilyID field from tblFamilies plus ContactType and ContactData from tblContactData...then, using the Switch function, I created a sort field. It seems that the DLookup function, in frmFamilies, pulls the top value from the query, so it doesn't work without the Sort field being there.

    I have attached a zipped copy of a scaled down sample. I wonder if you could look at it and give any thoughts.

    Thanks.

    Tom

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

    Re: showing value from recordset (2000 all updates

    I don't have much time right now, I'll look at it later today.

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

    Re: showing value from recordset (2000 all updates

    If you simply want to return the "first" contact data available for a family, your approach will work. I thought you only wanted to look at Home Phone, Work Phone 1 and Cell Phone; that requires a slightly more elaborate approach. Added: The attached database shows how.

    I would use a separate ContactType table in any case, since this lets you store a numeric ContactTypeID in the ContactData table instead of the full text. This lets you avoid using the Switch function and it takes up less space in the table.

  6. #6
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: showing value from recordset (2000 all updates

    Hans
    At the outset, I was thinking of showing just Home Phone, Work Phone 1 or Cell Phone...whichever was available. And the approach in your first attachment did that. I wasn't sure how because I couldn't totally figure out how the one query worked, but now that I look at the SQL for that query I understand it a bit better.

    Then I wondered why not show the Primary Contact, whatever that happened to be...but selecting in the priority order of Home Phone if there was one, Work Phone 1 if there was one, Cell Phone if there was one, etc. down the line. I may still revert to the initial plan. Not sure yet. (showing "Grapevine" as the primary contact method would look neat)

    It wasn't a matter of simply returning the "first" contact data available, but making sure that the various contact data for a family sorted in the proper order then the DLookup function picked the top in each case.

    I agree with your suggestion about using a separate ContactType table, and will switch to that approach. As you say, the bonus is I can eliminate the Switch function.

    I notice that when a new Family record is constructed, the DLookup function shows #error. This would be because, as you said in your first post, the query that populates the DLookup function is not updateable. As soon as you close the Family form and reopen it, the DLookup function works. I will figure out a way around that glitch.

    Thanks for your second attachment. I will have a further look at it.

    Thanks again, Hans, for your help.

    Tom

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

    Re: showing value from recordset (2000 all updates

    You can get around the #Error by creating another query - see the new version of my original attachment. The main form is based on a query that uses DLookup to retrieve the primary contact; the text box on the form is now bound to this field. The query qryMinCTID returns the first available ContactTypeID for each family (if any), not just Home/Work 1/Cell Phone.

Posting Permissions

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