Navigating Form (comprised from multiple tables) by Record

Posted on 2012-04-10
Last Modified: 2012-04-10
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!
Question by:Julz71
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 37827992
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37828603
...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

LVL 31

Expert Comment

ID: 37829316
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.

Author Closing Comment

ID: 37830066
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.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question