Navigating Form (comprised from multiple tables) by Record

I am VERY new to Access (only 8 weeks using it now), so please excuse my ignorance!  
I have created a database using Access 2010 to provide a solution to a long standing problem with difficulty accessing legal and formality information within my organisation.  The database comprises 29 tables, each dealing with a different aspect of law and custom, plus 4 forms to access various aspects of the information.  The information is for reference only (there is no requirement for the user to enter any fresh data).  The forms each use information from between 8 - 17 of the tables (55-120 fields per form).  On a simple level they work well.  Each record is sorted by country alphabetically and, by using the arrow buttons automicatically created at the bottom of the form, I can navigate through the records for each country.  I have input information for 10 test countries at present, however, there will be records for over 80 countries, so this way of moving between records will not suffice in the long run.

I would like to create a list box or combo box (or other method? navigation form maybe?) to show the countries alphabetically, so that when the relevant country is selected in the box, the form shows all the information for that country.  It works for test forms that only have information from one table, but as soon as there are multiple tables involved, it only changes the record information for the one table.  The rest of the form is unchanged and therefore shows information from the previous country record.

Any help would be greatly appreciated, but please be aware that I have no experience writing code so, if the solution includes code, a real idots guide is required! :^)

Thank you!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
...I too am a bit confused...

At some level you will either have to "Join" or UNION" all of the tables in a query to make then act as one dataset.
So you will have to post some samples of these tables....

Then you can create a combobox that filters this Dataset based on the Value selected.

Some comments:
<Any help would be greatly appreciated, but please be aware that I have no experience writing code so, if the solution includes code, a real idots guide is required! :^)>
Your question here *Requires* that you have a good working knowledge of Code.
This site is really not designed to "Teach" VBA, or create step by step: " idiot's guides")
Some experts here will take the time to do this, but it is not something you can "demand"

Sound fair?

Something like this:
Create a form based on the "Combined DataSet"
Then drop a combobox on the form and select:
"I want the combobox to get the values from another table or query"
Then follow the rest of the wizard steps for your "Country" table...
This will create a combobox that will allow you to select the country
Then on the After Update event of the combobox, put code like this:

Sub YourComboBox_AfterUpdate()
    Me.Filter="Country=" & "'" & me.YourComboBox & "'"
End sub

Or like this if you are using a table with a numeric ContyID and the Name
Sub YourComboBox_AfterUpdate()
    Me.Filter="CountryID=" & me.YourComboBox
End sub

Helen FeddemaCommented:
I usually make an unbound combo box in the form header, with a row source that is a cut-down version of the form's record source, and this code on its AfterUpdate event to go to the selected record:

Private Sub cboSelect_AfterUpdate()
'Created by Helen Feddema 29-Apr-2011
'Last modified by Helen Feddema 29-Apr-2011

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

The key field is used to do the synching; one or more other fields are displayed in the drop-down list for selection.
Julz71Author Commented:
The youtube tutorials covered a fair bit of what I already knew, but by publishing my form in SharePoint I have been able to add the navigation control I require (as suggested and explained in one of the tutorials) and I have solved my issue.  Thank you so much for your help.

I have taken on board the other advice also submitted and I am sure it will be useful in the future.  Thank you for your time.

As a side issue, I certainly wasn't 'demanding' a certain form of response, but was I aware that I would be wasting advisors time if I did not warn of my level of understanding.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.