Navigating Form (comprised from multiple tables) by Record

Posted on 2012-04-10
Medium Priority
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 1500 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

by:Helen Feddema
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

807 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