Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182

    Adding "Region" Field and Autofilling Based on "Country" Field.

    Hello.

    I currently have a form called frmAlpha that is based on tblAlpha.

    This form the following fields"IncidentID", "Activity", "City", "Province", and "Country". The "Country" retrieves all of its values (Row Source) from another table called tblCountryList. tblCountryList contains a list 150 countries, and nothing else.

    I want to add an additional field to frmAlpha called "Region" where I can list ONLY the following values "Asia", "Africa", "Europe", "Latin America", and "North America". Next, I want that when I enter a value into my "Country" field (combobox) on my form, I want it to automatically update the "Region" field (also a combo box), based on tblCountryList.



    What is the best way to do this???



    Things I have tried:
    Adding an additional field to tblCountryList were I list the regions.
    Adding the "Region" field to frmAlpha via the ComboBoxWizard.
    However, when I do this, the combo box has 150 possible choices (because the Regions are shared between multiple countries.)

    I hope this makes sense...

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd probably have a "Regions" table, so you could add to the list later as needed. Your combo would get its selections from there. Failing that, you can restrict it with DISTINCT:

    SELECT DISTINCT Region FROM tblCountryList
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And by the way, you can autofill it with this method:

    BaldyWeb - Autofill
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to Paul's advice, here is an authority for UN Region codes and Countries

    Good luck

  5. #5
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    I can not really use just a "Regions" table because I need the "Countries" and "Regions" to be linked. So when I enter "Canada" as my "Country" value, "Region" automatically fills to "North America."

    I also have the problem where I have 150 values I can choose from the "Region List" combo box. This is because Access is looking through the combo box via "Country" and not "region" only/.


  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I didn't suggest using just a regions table, I suggested using one to contain the available selections.

    As to 150 values, did you notice the SQL I suggested for the regions combo?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Sorry. I think I am misunderstanding/

    What do you mean by "useing one to contain the available selections?"

    And sorry! I actually did miss the SQL note. It worked great!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    To prevent people from entering "North America" and "No. America" and "North Amurica" I'd have a table that contained the valid selections. It would also help you if you later decided to add "Central America" as an option. You just add it to the table, it flows through everywhere else.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    So... Here is what I have done:

    1. Open up frmAlpha.
    2. Click on "Add Existing Field" and Click "Region1". However, this is not a "ComboBox"
    3. I go back into design view and open the "Combo Box Wizard". The table providing the values for my combo box is tblCountryList. I select both the "Country List" and "Region List" fields. Then I press Finish. Lastly, I set the column width to {1";0"} so it only shows me Regions.

    Now, whenever I select a Region in my combobox, it does not update in my tblAlpha. Is this because I do not have a "Control Source" set for the combobox?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, it would need a control source of the region field in the underlying table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Perfect! Thank you. I think I got it to work correctly. Now all I need to do is fill out the tblCountryList with all the 150 regions.

    THank you!!!

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    I think I got it working right now

    All I need to do is make an UpdateQuery to Update the blank values in my Regions field.

    Any idea how to do this?

    1. I have tblAlpha in an UpdateQuery.
    2. I have "Country" from tblAplha selected and "Region" from tblAlpha selected.
    3. I attempted to enter this is the {Update To:} row for "Region" ----> [Forms]![FrmAlpha]![Country] = [CountryList]![Region List]. This is not doing anything however.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You could use an update query joining your activity table with the country/region table on the country. You wouldn't reference a form, unless I'm misunderstanding. You'd update the region field in the activity table to the region field in the lookup table. Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Thanks. But unfortunately, I can not upload my database as the information is private. But you are correct about not referencing a form. I wasnt thinking about that. But basically, my query looks like this:



    The criteria of {> 30000} is set because I only want to update the values of Region on in those incidents above IncidentNumber#30,000.

    Im at a loss on how what to write in the "Update To" row of "Region1". Because [Suspected Activity].[Region1] depends on [Suspected_Activity].9Identified Country]

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-12-2016, 12:58 PM
  2. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 3
    Last Post: 04-22-2013, 06:08 AM
  5. Replies: 16
    Last Post: 07-22-2011, 09:23 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