Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populating Form Fields (XP - SP2)

    I'm designing a form to add a new contact to a database. I'd like to populate several fields on the form (Address, City, Country, etc.) based on user's choice of company in a combo box (several of the company choices have different locations, hence the splitting up of this information). For instance, when the user chooses "BBC Broadcasting House" in the combo box, the rest of on the form are populated from the table.

    Can someone walk me through how I would accomplish this--I'm having a little trouble getting my bearings.

    Thanks.

    S.O.

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

    Re: Populating Form Fields (XP - SP2)

    Would you like the additional fields to be completely dependent on the item chosen in the combo box, or should the user be able to modify them after they have been filled in automatically?

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

    Re: Populating Form Fields (XP - SP2)

    I'll take a look at it.

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

    Re: Populating Form Fields (XP - SP2)

    S.O.

    Could you please reduce the screen shot in size? It is so wide that you have to scroll the Lounge window horizontally in most resolutions. Thanks in advance.

  5. #5
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Form Fields (XP - SP2)

    Hi Hans:

    I had originally posted something smaller but thought it was difficult to read. The one above is now a lower ressolution. I'll try to get a .pdf of the Relationships up shortly, if I can.

    Hope that helps. Thanks again.

    S.O.

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

    Re: Populating Form Fields (XP - SP2)

    Thanks.

    You do need a relationship between tblCommissioners and tblCompanyLocations on LocationID, and you should set referential integrity for this relationship with cascading updates. This ensures that you can't enter a non-existing company/location in tblCommissioners, and that you can't remove a company/location from tblCompanyLocations if there are related records in tblCommissioners.

    When you have done so, create a query based on tblCommissioners and tblCompanyLocations. Access will automatically join them on LocationID. If you allow LocationID in tblCommissioners to remain blank, double click the join and select the option to include ALL records from 'tblCommissioners' and only those records from 'tblCompanyLocations' where the join fields are equal.
    Add all fields from tblCommissioners you need (or tblCommissioners.* if you want to display all of them), and all fields from tblCompanyLocations you need except for LocationID (otherwise, LocationID would occur twice in the query results.)
    Use this query as record source for your commissioners form. When the user selects a company/location, the rest of the data will be filled in automatically. You should make the controls bound to fields from tblCompanyLocations read-only (Locked = Yes) to prevent the user from accidentally modifying a company/location.

    Similarly, you can create a query based on tblSubmissions, tblCommissioners and tblCompanyLocations (and tblProgrammeIdeas), and use this as record source for a submissions form. When the user selects a commissioner, the rest of the data for the commissioner, and for the corresponding company/location will be filled in automatically.

    Post back if this isn't clear enough, or if you have more questions.

  7. #7
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Form Fields (XP - SP2)

    Hi Hans:

    Thanks for your reply--it's good to hear from you.

    I've attached a screenshot of the Relationships window. The database is for my partner who works as Head of Development in an independent production company and needs a desktop database to track programme ideas from initial idea through to commission. Right now, I'm trying to get the basic functionality together but eventually I'd like it to function as a switchboard that brings together the various functionality of Office XP (create new documents and track different versions of proposals, send e-mail, with the help of Google Hacks maybe create and store saved searches,etc.). But I'm a far distance from that.

    The database has three main "entities": 1) the programme Idea (tblProgrammeIdeas), 2) the commissioners (tblCommissioners) and 3) a record that represents where the previous two entities merge--the submission record (tblSubmissions), which is created when a proposal is sent to a commissioner. Then the singular submission to a specific commissioner is tracked. The strength of this design is meant to be in the queries and reports.

    The information in tblCommissioners primarily need only be viewed, once the records are entered. There is a mostly finite number of commissioners that are dealt with, a lot of whom are from the same companies but that work in different departments, on different floors and sometimes different company locations (aside from all the BBC locations there's Discovery Europe and Discovery USA, etc.). So I've separated out the Company Locations (tblCompanyLocations) and wish to make all the information contained in those records available when inputting and viewing Commissioner records. On the whole, it doesn't change that often.

    I originially had a separate table for Company Names (for example, BBC, Discovery, National Geographic) and then another table for Company Locations (for example, BBC White City, BBC Manchester, etc.) which is presently tblCompanyLocations, but I decided that though it seemed atomic, it might've been an unnecessary step since it really only applied to a few companies, mostly the BBC. It seemed more economical to just describe the companies as thus, Discovery USA and Discovery Europe, since often enough they are thought of as separate anyway.

    Also, I'm not even sure if I need a relationship between tblCommissioners.LocationID and tblCompanyLocations.LocationID--it seems to me I can just build the combo box on the input form, the same way I did for Company Name (I just queried tblCompanyLocations.Company). It doesn't look like I need a tblCommissioners.CompanyID or LocationID. What I'm trying to do is select the Company (tblCompanyLocations.Company) from a queried combo box and then have the rest of the fields populated by the information contained in the same table (tblCompanyLocations.Address 1,2,3, City, State, etc.).

    Part of the objective of this project as well is to futher my own abilities with Access and, at the moment, it is certainly doing that. Though I've had a friend eyeball the design, aside from my initlal question, I'm still lacking confidence that I'm heading in the right direction with this. If this seems fundamentally flawed, please let me know! <img src=/S/please.gif border=0 alt=please width=31 height=23>

    Thanks for your help and, as always, I appreciate your advice.

    S.O.
    Attached Files Attached Files

  8. #8
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Form Fields (XP - SP2)

    Hans:

    Thank you for giving me a clear start with this. I'll let you know how I get on.

    Thanks.

    S.O.

  9. #9
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Form Fields (XP - SP2)

    Hi Hans:

    I've got on pretty well with this (actually started afresh and redid my tables, etc.). It works just as you said it would. However, when inputing new Commissioners, I would like the user to be able to click on a combo box and select from a list of Companies. Right now, they have to know what the Companies are and how they're entered into the table, in order for this to work.

    I've changed LocationID to just CompanyName and made that the primary key in tblCompanies (previously tblCompanyLocation). These particular company names can never be the same and must include some geographical description (i.e., Discovery USA). I've tried including the tblCompanies.Company key in the query that the form is based on but it's not picking up the control source. Before I muck this up too much, I thought I'd seek your advice.

    Thanks!

    S.O.

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

    Re: Populating Form Fields (XP - SP2)

    Hi Sean,

    I assume that you have changed LocationID to CompanyName in both tblCompanies and in tblCommissioners.

    In my previous reply, I described a query based on the tblCommissioners and tblCompanies tables that could act as record source of a frmCommissioners form. In the new setup, this query should include the CompanyName field from tblCommissioners, not from tblCompanies (just as it included LocationID from tblCommissioners in the previous version). By default, Access will probably create a text box for this field, but you can turn it into a combo box by right clicking the text box and selecting Change To | Combo Box. Set the Row Source of this combo box to tblCompanies. This will let the end user select a company from the list part of the combo box.

  11. #11
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Form Fields (XP - SP2)

    Thanks Hans. I did have some success creating a second query with just the tblCompanies.Company field and it worked but I'm going to rewrite the first query to include it and then get on with the rest of it. Thanks again for pointing me in the right direction--I feel like I'm making real progress with this today.

    S.O.

Posting Permissions

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