Cascade filling of the connected lists

In clause published earlier "Directories" except for the description of process and the brief theory of creation of help forms and tables the example of use of function AppendLookupTable also was resulted, allowing to add absent value in the list. Addition occured automatically after direct input of the necessary value in the list. However this function allowed to add data only in the single list. In practice often enough there is a necessity of similar autofilling, but already connected lists. About a variant of the decision – completion of function AppendLookupTable speech in this clause also will go.

For consideration of an example we shall create three help tables:

Name of the table	Name of a fiele	Type of a field	The connected table	Field in the connected table
The countries	id_countries	The counter
	countr	The text
Manufacturers	id_Manufacturers	The counter
	id_countries	Numerical (the long whole)	The countries	id_countries
	Manufacturers	The text
The goods	Id_goods	The counter
	id_id_Manufacturers	Numerical (the long whole)	The countries	id_countries
	goods	The text

We index fields of tables for reception of an opportunity of automatic tracking uniqueness of values. Otherwise can happens, that the same value in the directory repeats. For this purpose we open the table in a mode of the designer, we press on a badge in the menu "Indexes" (a badge of a lightning). In the appeared dialogue window to a field "Index" we write the name of an index, for example "Country". Then in a floor « the Name of a field » it is chosen from the list "Country" and in the bottom part of the form « the Unique index » - Yes is chosen from the list. Now at attempt to enter already existing value there will be a corresponding message and input will be blocked.

In tables "Manufacturers" and "Goods" the index should be compound as it is necessary to trace uniqueness of pair the Country – the Manufacturer. In fact the different countries can let out the identical goods. An example of creation of a compound index:

Name of the table	Name of an index	Name of a field	Index
The countries	The country	The country	Unique index - Yes
Manufacturers	The company	id_goods	Unique index - Yes
		The company
The goods	The goods	id_Manufacturers	Unique index - Yes
		The goods

We connect the attitude one to many help tables: the Countries – Manufacturers – the Goods. Thus have received system from three connected directories in which except for data are established as dependences between tables. We shall create as for demonstration of an example of autofilling two working tables: Orders and the List of the goods.

Name of the table	Name of a field	Type of a field	The connected table	Field in the connected table
Orders	id_Orders	The counter
	id_country	Numerical (the long whole)
	id_Manufacturers	Numerical (the long whole)
The list of the goods	id_List	The counter
	id_Orders	Numerical (the long whole)	Orders	id_Orders
	Id_country	(The long whole)	The goods	Id_country

We connect tables "Orders" and « the List of the goods » a parity one to many on fields «id_Orders» (on one order there can be many goods).
Now we shall consider a variant of creation of the form – the directory. It is possible two variants:

1. To make the directory of "Country". Then two directories: « the Countries – manufacturers » and « Manufacturers - the goods ». To be filled they should serially: First "Countries", then « the Countries – manufacturers », then « Manufacturers – the goods ».
2. To make one two-level directory: « the Countries – manufacturers - the goods ».

In the second case it is necessary to provide the procedure interfering data input in the form "Manufacturers" if data in the form of "Country" are not got. Otherwise we shall receive not connected record. For this purpose procedure of event forms Enter serves.

Private Sub subManufacturers_Enter()
      If IsNull(Me.id_countr) Then
            MsgBox "all over again specify the country of the manufacturer ", vbCritical, "admin"
            Страна.SetFocus
      End If
End Sub

As we see, at attempt to enter data in the form when the key from the main form is not entered

If IsNull(Me.id_countr) Then

There is a message on a mistake and focus of input is transferred on a floor of the main form. Similar procedure of blocking is made and for the subordinated tabulared form on the form "Orders".

On the form "Orders" there are two lists: the Countries and Manufacturers. And the second is connected with the first through the reference to it in inquiry (a source of the data). Open the form "Orders" in the designer and look at a source of lines of the list the Manufacturer (id_Manufacturer). On a floor the condition of selection is established

Eval("Forms!Orders!id_countr")

Similarly and on a floor(field) with the list of the tabulared subordinated form «subOrders»

Eval("Forms!Orders!id_Manufacturers")

Unusual in these references is their processing through function Eval (). But about it hardly later.
The help form can be started double click under the list or under the menu at the left. It was in detail told About realization of the similar interface in clause "Directories".

Now we shall consider realization of cascade filling of lists. As it was already spoken, the problem consists in that at autofilling the connected list except for entering new value into the table – a source, it is necessary as to bring and key value from the main list. Otherwise we shall receive not connected record. For this reason the new variable ctl1 is entered.

Function NotInList was finished in view of that through it it was possible to add absent values of willows usual lists, and not just connected. In this case by a call of function as the second argument it is necessary to repeat the reference to the list. It will be the instruction what to add data it is necessary only in one list. It is necessary to do a call of function of autofilling of lists of procedure NotInList.

Private Sub id_Countr_NotInList(NewData As String, Response As Integer)
      Set ctl = Me!id_Countr 'we establish(install) the reference(link) to the list of "Country"
      ' we start function of cascade addition, and as the second argument
      ' the first list is specified. That is function works as usual procedure
      ' additions in one list
      Response = AppendLookupTable(ctl, ctl, ctl.Text, 0)
      Me.id_Countr.RowSource = Me.id_Countr.RowSource ' we update the list of the countries
      Set ctl = Nothing 'it is cleared a variable
End Sub

In this case after check of a condition (cascade or usual addition) absent value is added just as in old function

If cbo.Name = cbo1.Name Then
      Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
      With rst
            .AddNew
            rst(1) = NewData
            .Update
            .Close
      End With

If it is necessary to add data in the connected list then by a call of function AppendLookupTable it is specified as arguments two lists.

Private Sub id_Manufacturers_NotInList(NewData As String, Response As Integer)
      Set ctl = Me.id_Manufacturers 'we establish(install) the reference(link) to the list of "Company"
      Set ctl1 = Me.id_Countr 'we establish(install) the reference(link) to the list of "Country"
      ' we start function of cascade addition in two lists
      Response = AppendLookupTable(ctl, ctl1, ctl.Text, Nz(Me.id_Countr, 0))
      Set ctl = Nothing 'it is cleared a variable
      Set ctl1 = Nothing 'it is cleared a variable
      Me.id_Manufacturers.RowSource = Me.id_Manufacturers.RowSource 'we update the list of the companies
      ' in default from filling
      If Response = 0 Then
            Me.id_Manufacturers = Null 'we null the list of the companies
            id_Countr.SetFocus  'we establish(install) focus on the list of the country
      End If
End Sub

And now in occasion of Eval (). The matter is that procedure of addition of absent value in the table – a source of the list occurs by means of objective model DAO. If in a line of inquiry there will be an expression of type Forms! Orders! id_Countres – there will be a message on a mistake of type: « the parameter Is required ». Because DAO, nothing knows the nobility, about the open forms, on it and swears. To it have given line SQL, it tries to open corresponding рекордсет. The field of the table with Forms tries to find! And certainly does not find.

Therefore, when work with inquiry by means of DAO is used, references(links) to elements of forms need to be made out through Eval (). For example so:

Eval("Forms!Orders!id_Countr")

These recommendations are fair and at work with objective model ADO. Often the first on what those who for the first time has decided to translate the project with mdb on adp stumble are a mistake at performance of inquiries where there are references to elements of the form. In fact now data processing occurs on a server on which there are no forms. However, it already absolutely other theme, to given clause not having the attitude – transition from mdb to adp.

In summary I shall stop on one question which as often is set by beginning developers: how to disconnect standard message Access? For example, at use of procedure on removal of record

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

If the user will press "Cancelling" there will be a message: « performance of macro DoMenuItem Is interrupted ».
To get rid of it it is possible on a miscellaneous. For example, "to protect" this procedure of a call by commands of switching-off/inclusion of standard messages Access.

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True

But in this case messages are disconnected all, and not just « performance of macro DoMenuItem Is interrupted ». And if at performance procedures will arise what or other mistake – anybody about it "does not learn". And in fact mistakes happen also fatal, to "start" from the program. Even worse if inclusion/switching-off of standard messages the developer will forget to include then in a code of the program them.

Therefore more reasonable variant – to create the procedure of processing of mistakes in which depending on an error code it is possible to deduce the messages (or to not deduce anything). An example of such procedure (on removal of records)

Function sDeleteRecord() As Boolean
On Error GoTo Err_
      DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
      DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
      sDeleteRecord = True
Exit_:
      Exit Function
Err_:
      ErrNum = Err.Number
      sDeleteRecord = False
      Err.Clear
      Resume Exit_
End Function

For its work in global module Constants variable Public ErrNum As Long is entered. Through it number of a mistake is transferred. Procedure of removal of record looks(appears) so (for the form « the DIRECTORY of the country manufacturers the goods »):

Private Sub butDelete_Click()
      sDeleteRecord
      Select Case ErrNum
            Case 2501
                  Err.Clear
            Case 3396
                  MsgBox "Data it is impossible to delete, differently in the table [the List of the goods] there will be not connected records!", vbCritical, "admin"
      End Select
End Sub

Here two mistakes are possible:

• 2501 – interruption of a command of removal (have decided not to delete)
• 3396 – infringement of integrity of data

The first mistake can be ignored simply - Err. Clear, and here in case of occurrence of the second – will not prevent to deduce the corresponding message that it was clear why it is impossible to delete. Thus, it is possible to create the procedures of processing of mistakes of performance. To learn numbers of mistakes, it is possible to take advantage Err. Number. For example, at debugging new procedure to include in it обработчик mistakes:

On Error GoTo Err_

      …..
      …..
      …..

Exit_:
      Exit Sub
Err_:
      MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation
      Err.Clear ' dump of a mistake
Resume Exit_

Example under given clause you can below...

The author: Admin SprawK It is added: 28.12.2007