Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Clear Spring, Maryland, USA
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box to fill in multiple fields (XP)

    On a form I am trying to be able to pick a record from a combo box (referencing a different table with four fields) and have the values of those four fields be set for four cooresponding fields in the current record in the table that the form is referencing. Is the combo box the way to do it, or is there a different way?

  2. #2
    Lounger
    Join Date
    Feb 2003
    Location
    Clear Spring, Maryland, USA
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box to fill in multiple fields (XP)

    <P ID="edit" class=small>(Edited by charlotte on 15-Mar-03 16:16. to acivate link to post)</P>Found my own answer by modifying post <!post=230910,230910>230910<!/post>. Works great!

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box to fill in multiple fields (XP)

    Why would you be copying four fields from one table to another this way? The idea of a relational design is that the data lives in one place, so if your table with four fields had a fifth field that was a unique key, like an autonumber, all you would have to insert into your record in the other table would be the unique key that identifes the four fields in the other table. You can still make those four values appear in unbound controls on a form so that the user can see them, but the values themselves wouldn't be in the underlying table, only the key value that would link to the other table.
    Charlotte

  4. #4
    Lounger
    Join Date
    Feb 2003
    Location
    Clear Spring, Maryland, USA
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box to fill in multiple fields (XP)

    OK. I think that would work better. I have added an autonumber field to my churches table (what used to be the four field table). I have added a number field called ChurchID field to my Students table (the one the main form is based on). I have set up a relationship between the two. I then added a combo box to my form that displays the four main fields (the indexed field is hidden). This allows me to pick the church, but how do I then display (display only, not to be changed directly) the church name, address, phone & pastor (the four other fields). What specific controls/boxes, etc. do I add to the form. I tried all kinds of things and always got "#Name?".

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box to fill in multiple fields (XP)

    I would setup 4 unbound text boxes for those 4 fields and populate these fields in the AfterUpdate event of the ComboBox.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Lounger
    Join Date
    Feb 2003
    Location
    Clear Spring, Maryland, USA
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box to fill in multiple fields (XP)

    OK. I've done all that and it works fine. The only problem I still have is this? If someone changes a value in the churches table, how can I get my main form to update these text boxes with the new values without having to re-pick the record through the combo-box? If I re-pick the record it updates just fine, but I don't want to have to do that for each student each time I open the form or run a report just to make sure the infomation is current. Any suggestions?

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

    Re: Combo Box to fill in multiple fields (XP)

    Try the following.[/list]<LI>Make a backup copy of the database first, so that you can go back if something goes wrong.
    <LI>Create a query based on the Students and Churches tables. The tables should be joined on ChurchID automatically if you have set up a relationship between them.
    <LI>Double click the join and seelct the option where all records from the Students table are displayed.
    <LI>Add Students.* to the query grid, and the four fields from the Churches table you want to display (don't include the ChurchID field from the Churches table, ChurchID is already included in Students.*).
    <LI>Save this query.
    <LI>Set the Record Source of the form to the name of this query.
    <LI>Set the Control Source of the four text boxes to the appropriate field names.
    <LI>Remove the code that sets the value of the four text boxes in the AfterUpdate event of the combo box.[/list]HTH

  8. #8
    Lounger
    Join Date
    Feb 2003
    Location
    Clear Spring, Maryland, USA
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box to fill in multiple fields (XP)

    We're getting there! I did what you said and it worked perfectly for one field. Let me explain my main table more thoroughly and hopefully that will explain the problem I have left. I have three fields in my main table that reference churches: the student's church (StudentChurchID), the father's church (FathersChurchID) and the mother's church (MothersChurchID). I have set up a relationship from each of these fields to the same ChurchID field in the Churches table. Doing what you recommended worked great for the StudentChurchID, but not for the other two. When I pick the specific church record from the combo-box for the father or mother's church it keeps the text fields from the student church field. Clear? Any suggestions?

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box to fill in multiple fields (XP)

    >>When I pick the specific church record from the combo-box for the father or mother's church it keeps the text fields from the student church field. <<
    It seems that you have included the 4 fields based upon the Student's Church. Have you the same 4 fields for the Mother's and the same 4 fields again for the Father's church? If so, you will have to do what Hans suggested for each of the Mother's and Father's churches.
    Hope this makes sense.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    Lounger
    Join Date
    Feb 2003
    Location
    Clear Spring, Maryland, USA
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box to fill in multiple fields (XP)

    Actually, I'm not sure what you mean. Am I allowd to create three links (StudentChurchID, FathersChurchID, MothersChurchID) to the same index field (ChurchID) in the churches table? What I want is to be able to pick three different churches (StudentChurchID, FathersChurchID, MothersChurchID) and display the values from the four fields of the Churches table on my main form depending on what churchid is selected in each combo-box. Thanks for your patience and help.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box to fill in multiple fields (XP)

    Yes, that's right. What you have at the moment is a link from your main table in the query to the churches table via the student Church link, right?
    What you can do is to go onto the query and add the churches table twice more and link the Father's Church to one of them and the Mother's Church to the other one. Then just select the relevant fields from the Father's Churches table and the Mother's Churches table to display on the form.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Combo Box to fill in multiple fields (XP)

    Add the Churches table two more times to the query, so that there are three copies in total, labeled something like Churches, Churches1 and Churches2. Leave the first one joined on ChurchID <-> StudentChurchID, and join the second one on ChurchID <-> FathersChurchID and the third one on ChurchID <-> MothersChurchID. If necessary, remove other links.

    Add the field you want to display from each of the copies of the Churches table. If you wish, you can give them specific captions, either by putting the caption before the field name, followed by a colon, or by setting the Caption property for the fields in the Properties window.

  13. #13
    Lounger
    Join Date
    Feb 2003
    Location
    Clear Spring, Maryland, USA
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box to fill in multiple fields (XP)

    Works perfectly! Kudos to you all.

Posting Permissions

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