Solved

Navigating Form (comprised from multiple tables) by Record

Posted on 2012-04-10
4
244 Views
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!
0
Comment
Question by:Julz71
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 37827992
0
 
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 & "'"
    me.FilterOn=True
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
    me.FilterOn=True
End sub

JeffCoachman
0
 
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

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   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.
0
 

Author Closing Comment

by:Julz71
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.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now