Results 1 to 12 of 12
  1. #1
    jjcaprio is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    5

    Populating multiple fields

    I have a form that lists hospitals during a disaster. I have the table populated with name, address, phone etc. I want to be able to just select the hospital name and automatically populate the other fields in the record from the table.



    Hospitals

    Hospital Hospital address Hospital Phone Helipad(yes-no) Burn (yes-no)

    The first three are now set as combo boxes that show the data but I have to find the match, The last two are yes no

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    All you need in your table is the HospitalID field and the two tables can be joined on that field. You can then use a query to display all of the fields from both tables as if they were in one table.

  3. #3
    jjcaprio is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    5

    Further explanation

    I probably didn't explain this correctly, I have a table created listed below, it has 5 columns and around 2000 records. In a form, I have 3 drop down boxes one for the name, address and phone. Also have two check boxes, one if there is a helipad and one if the hospital is a burn center.

    I have an ICS form that we use to list medical information for a disaster, I have attached the pdf that was used as a basis for creating the form. You will see that there is a place on the form to list the hospitals that we will be using during the deployment. My goal is to click the hospital drop down and select the hospital I want, from there I need the additional 4 fields to automatically populate.

    Hospital Hospital Address Hospital Phone Helipad Burn Center
    Abbott Northwestern Hospital 800 E 28th St 612-863-4000 No No

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    On what table did you base your ICS form? What is the structure?

  5. #5
    jjcaprio is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    5

    More Info

    Ok, I have a form called ICS206, there is a section of the form to list the 6 closest hospitals that we will use in a disaster response. The form is not currently set up correctly. For this section there are 6 rows with 7 fields and the fields in each row are as follows

    Hospital Combo Box linked to a table called Hospital resources
    Hospital address Combo Box linked to a table called Hospital resources
    City Combo Box linked to a table called Hospital resources
    State Combo Box linked to a table called Hospital resources
    Hospital Phone Combo Box linked to a table called Hospital resources
    Helipad Yes/no check box
    Burn Center Yes/no checkbox

    Currently when you select the hospital it brings up all 1000 plus hospitals in alphabetical order, then you go to address, it brings up all 1000 plus addresses. What I need is to set up the form to select the hospital and automatically fill in the address, city state etc without having to even go into the other fields.

    The hospital resource table is a basic table with 7 columns listed in order as listed above, hospital, hospital address, city, etc.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would add a HospitalID field (AutoNumber) to the Hospital table and then use the cbo wizard on your Disaster form to select the hospital, inserting the HospitalID into your Disaster table. If the Disaster table and the Hospital table are joined on the HospitalID field, you can display all of the fields of both tables on your Disaster form as if they are in the same table. Changing the HospitalID field will automatically change the other fields from the Hospital table that you are displaying.

  7. #7
    jjcaprio is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    5

    ???

    Sorry I really dont understand, I do have an autonumber field on the hospital table, but not sure what you want me to do, I have attached the 206 form so you can see the area I am trying to automate.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Is that a Form or a Report? If Form, does it have a RecordSource of a table or a query? It should be a query that joins the Hospital table to the Disaster table on the HispitalID field.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Remember, in a RDBMS the data should only exist in one table. Everywhere else it is needed is done with the use of a join in a query on a ForeignKey field.

  10. #10
    jjcaprio is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    5
    This has a table associated called ICS-206, The hospital resource is a seperate table that just lists the hospitals so we can fill just the hospital section of that form. Once the form is completed all of the data is stored in the ICS-206 Table

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    All you need in the ICS-206 table to completely describe the hospital is one LongInteger field named HospitalID (or whatever you called it in the Hospital table). This will be a ForeignKey field where you put the PrimaryKey from the Hospital table. With the query wizard create a query that joins the ICS-206 with the Hospital table on the HospitalID field. Then pull whatever fields you want displayed from both tables. That query is what your ICS-206 form will be based on and not the ICS-206 table directly. If you can post your db, it would be easy to make the changes and post it back.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I was afraid of this. The tables are a long way from normalized and so I now see why you were having so much trouble understanding my advice. For now I suppose we just need a bandaid for a solution. A ComboBox has a Column property so you can say Me.ComboBoxName.Column(2) [zero based] to reference the 3rd column of the DropDown. You can fill in the other fields (ugghh!!) of the ICS-206 Medical Plan tables in the AfterUpdate event of the ComboBox for the HospitalName.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. multiple fields in separate records
    By Fredo0709 in forum Database Design
    Replies: 9
    Last Post: 04-09-2010, 12:23 PM
  2. Replies: 4
    Last Post: 06-10-2009, 12:43 PM
  3. Populating other fields from combo box.
    By nkenney in forum Forms
    Replies: 1
    Last Post: 05-14-2009, 10:39 PM
  4. Multiple Fields In One Combo Box.
    By caljohn527 in forum Forms
    Replies: 1
    Last Post: 02-20-2009, 03:07 PM
  5. One table multiple fields identical properties.
    By swampdonkey in forum Queries
    Replies: 2
    Last Post: 09-29-2006, 10:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums