Handbooks
Creation of tables
In each database there are directories which serve for storage of often entered data. For example, if in the table « Attributes of the customer » are fields of type "Country", "City" instead of constantly entering manually these data, it is possible to get corresponding directories of cities and to substitute data from them. But the matter is that directories can be as simple, consisting of one table, and complex, compound. In this clause we shall consider examples of the organization of a various sort of directories.
The idea of clause was prompted with the following message:
Prompt how correctly to develop structure of base for realization of post base. Correct communication of tables interests: the Country, Region, City, Street.
The beginning developer meant how correctly to establish communications between help tables and the basic into which data from directories are inserted.
We create four help tables: the Addressee, the Directory of the country, the Directory regions, the Directory of city. In each table the key field corresponding it (Type the Counter) – id should be obligatory.
Usually key field of the table which is the unique identifier of record in this table, name an internal key (in our case it is fields of type "Counter"), and key fields, through which external tables are adhered to given – accordingly by external keys (in our case it is numerical fields the Long whole)
Name of the table
Name of a field
Type of a field
The connected table
Field in the connected table
The addressee
id
The counter
idCountry
The long whole
The directory of the country idCountry idRegions
The long whole
The directory regions
idRegions
idCity
The long whole
The directory of city idCity Street
Text
The house
Text
The directory of the country
id
The counter
The addressee
idCountry
Designation
Text
The directory regions
id
The counter
The addressee
idRegions
Designation
Text
The directory of city
id
The counter
The addressee
idCity
Designation
Text
At managements for beginning developers often there are the reasonings, what field of the table to make key. Is defined key fields and examples of communications between them are resulted. In my opinion, such reasonings only confuse beginners. In fact basically, all is simple enough: in 99 % of cases the best key field – the counter. It of 100 % is unique and at installation of communication with the basic table with a corresponding field Numerical communication is automatically defined as one to many (one record in the directory and many similar records in the basic table).
Probably, that to whom that would will seem unnecessary an explanation like obvious, but to me not time was necessary to collide with bases beginning (usually students) in which tables at all there were no key fields. Therefore I shall explain all in detail.
Apparently, in directories only two fields: key and a designation. And the basic table consists basically of numerical fields, except for a field "Street". Here that also is shown the basic feature of construction of relational databases.
Instead of storing in the table "Addressee" of the name of the countries, regions, cities in the form of the text, we shall keep them better in the form of numbers (values of key fields of directories).
At installation of communications between the basic table and help Access having found out a key field in the basic table, will go on the established communication in help and considers therefrom corresponding value from a field of the table. From what field is will depend on inquiry which we shall create further.
Directories of "Street" and "House" to create, as to me it is thought, there is no sense. Streets will be too much, them to enter manually easier.
As in help tables the field "Designation" unique, not supposing will not prevent to make recurrences. For this purpose in the designer of the table in property of a field « Index a field » we shall choose « Yes. Recurrences are not supposed ». If now to press in the designer of tables button " Indexes " (on it the badge of a lightning) we shall see, that in the table except for the basic index of a key field was added one more – "Designation".Names of fields of tables should not contain blanks, differently there can be problems with VBA, and unequivocally there will be problems, at carry of base on SQL Server. If the name of a field consists of two words it is possible to choose for example òàêàé a variant: StreetName or Street_Name.
Though according to Help, the name can include any combination of letters, figures, and special signs except for a point (.), an exclamation mark (!), íàäñòðî÷íîãî a sign (`) and square brackets ([ ]), but it is desirable to not use in names of fields of the table any symbols, except for letters and figures. The matter is that for example a name of a field of the table of type « the Street ¹ » in the project mdb most likely will not cause any conflicts, and here at carry of base on SQL Server, the master of carry " will simply throw out" it from the table.
At creation of the same help tables business will go more quickly if in window of the project of "Table" to allocate the table, then Ctrl + C or in the contextual menu at the right click of the mouse to choose "to copy", then is chosen to insert, in the appeared window we set a name of the new table and we press «OK». Here therefore I in all directories have entered that same weeding «id», "Designation". In the table "Addressee" as we create external key fields (the Long Whole) - idÑòðàíà, idÐåãèîí, idÃîðîä. Do not forget to clean in them value by default = 0.
Now it is necessary to establish only communications between tables. We press in a window of the project on the button « the Scheme of data » or the right button of the mouse, and in the contextual menu it is chosen « the Scheme of data ». There was a dialogue window « Addition of the table ». We click twice under all names of tables and we close a window.
We have tables for example so: at the left the basic – "Addressee", on the right other. We direct the cursor in the table "Addressee" at a field «idLand» it is pressed and we drag on a floor «idLand» in the table the Directory of the country. In the appeared dialogue window « Change of communications » we establish tags: « Maintenance of integrity of data », « Cascade updating of the connected fields » also we press «OK».
Tag « Maintenance of integrity of data »
It have established to exclude an opportunity of input in the table "Addressee" in a floor «idÑòðàíà» values which is not present in a similar floor of the help table « the Directory of the country ». I advise so to do always. It you in many respects will get rid of a problem of "dust" in a database – presence with what of not connected records. The truth is also other ways «shcmutzig», but not superfluous in the block diagram of base will already try to reduce them to a minimum.
Tag « Cascade updating of the connected fields »
In this case it would be possible and to not put. The matter is that the situation when it is necessary to update the connected fields, here hardly will occur, in fact a key field in the help table to change it is possible only program. Nevertheless, in our case will not be worse from it.
And here « Cascade removal of the connected records » in the given example I did not recommend to put a tag. Otherwise there can be rather sad situation: the user will decide to remove the name of city from the corresponding directory (the pier, it is not necessary any more), and together with it the records connected with it in the table "Addressee" will leave also all is and there is a cascade removal. However, at any attempt of removal Access gives out the corresponding prevention, but to hope that the user will adequately react to it, I would not become. Cascade removal can be put in case of, for example at communication of tables "Orders" and « Orders data ». Delete the order, and together with it automatically and all data on it, as what for data under the order which is not present more.
I shall stop on one moment: much « too big number » probably was necessary to collide with a mistake(an error). If do not wish to have with it problems, take to itself for a rule to not do a field with lists in the table (beginners usually for this purpose use the master of substitutions). The matter is that from for discrepancies of formats of units of measure of width of columns in different versions Access, this width can instead of standard 2,54sm become … 57,2 sm (too greater). Therefore the list is better for making on the form, and in the table leave simply a field
Help system without ... Help tables
Usually help tables serve as sources of data for lists or fields with lists. But before to pass to creation of forms – directories, we shall consider all over again variants of the organization of these lists without use of help tables (see form Example1).
The reference of the table to (Example1. W1)
It is the most simple way of the organization of the directory. We create the form "Addressee", we cling to it a source – the table "Addressee". We create a field with the list, which source – inquiry to a field "Street" from the same table. The inquiry should be necessarily grouped and in a condition of selection it is necessary to exclude empty drains (the Addressee. Street = Is Not Null). For updating the list it is possible on property of the form « After updating » to hang up Street. Requery. Property « To be limited to the list » should be "is not present". Otherwise it will be impossible to add new data.
Advantages: simplicity of realization, help tables, all program code – one line are not necessary.
Similar problems demand program decisions: creations of the functions which are watching(are keeping up) correctness of input, finding and correcting wrong values in the table. But then the main advantage of such variant – simplicity is lost.However, blanks can be won for example so:
Trim ([Street])
And for abnormality of input to beat on hands. Besides, if in the list numbers of houses in the form of numbers this way basically is worthy are got for example. To distinguish number from the text it is possible to take advantage for example of such procedure:
If Val (House) = 0 Then
MsgBox " Not a correct format of data! ", vbCritical, "admin"
House = Null
End IfBut in our example I have got(started) for numbers of houses type weeding "Text" - in fact number can be for example 1/2 or barks. 3. Therefore, with houses we shall understand on another.
Addition absent value in the list of values (Example1. W2)
For realization of this way we use as a source of data of the help list – « the List of values ». A basis of this way – interception of event of the list « Absence in the list ». Here an example of procedure:
Private Sub Home_NotInList (NewData As String, Response As Integer)
Dim ctl As Control ' Returns object Control specifying on a floor(field) with the list
Set ctl = Me!House ' the Invitation to confirm input of new value
If MsgBox (" Value is absent in the list. To add? ", vbOKCancel) = vbOK Then
' Value of argument "Response" defines(determines) addition in the list
Response = acDataErrAdded
' Adds value of argument "NewData" in a source of lines
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' By pressing button " Cancelling " suppresses a conclusion
' messages on a mistake(an error) also cancels changes
Response = acDataErrContinue
ctl.Undo
End If
End SubLet's test procedure: we shall open all over again the list, we shall look what there numbers and we shall enter such which there is not present. At transition to new record or other element of the form there will be a message with the offer to get new value of the list. We shall tell "Yes" - value will be got, we shall tell is not present – will be erased.
In both examples there is a common fault: it will be complex to realize updating of the same data in the table "Addressee" - in fact to update that them actually there is nothing, there is no corresponding help table. That is, if, for example you decide to change the name "Moscow" on « Moscow » it is necessary program to catch in the table all corresponding records and to change them. And here in case of with use of help tables for this purpose it is enough to change data in the directory.
Addition absent value in the list of values (Example1. W3)
Zuerst small deviation: when the given dispatch was made, Walera Krurk has again surprised the public, having offered very interesting idea. In brief an essence of its offer in the following:
Often happens, that variants for substitutions of values in a floor of the form it is not a lot of and consequently to get for this purpose the separate table - the directory it is not desirable. To insert in advance all values into a field with the list too not an output, in fact sometimes it is necessary to change contents of the list. Valery suggests the compromise – to unite help data for several lists in one table. And at the same time has offered the original interface for data input/editing. I shall not tell in detail, plagiarism differently will turn out, interested persons can will familiarize with an example under the reference
In the previous example the variant of addition of absent value in the list of substitutions was considered. But data were not kept, because for this purpose the help table is necessary. Now I shall show a similar example, but already with use of the help table, that the problem of preservation of new value in the directory will be solved. To add data as well as in the previous example we shall be programm. For this purpose we shall take advantage DAO - Data Access Objects - objective model of access to data. Objects of access to data were created, as objective - the focused interface for a kernel of databases Jet of firm Microsoft just that it was possible programm to bring, change, delete data in tables.
Let's create function AppendLookupTable and we shall place in the general module that it could be caused from different forms, for different fields with lists, and not just for concrete.
For work with the table program (through DAO), all over again it is necessary to declare an objective variable and to appropriate to it a source of data:
Dim rst As DAO.Recordset ‘ it is declared(announced)
Set rst = CurrentDb.OpenRecordset(ComboBox.RowSource)And further standard processing Recordset
rst.AddNew
rst(i) = NewData
rst.Update
rst.CloseAt work with the table/inquiry through DAO after performance of actions with lines updating a source rst. Update should be obligatory. Otherwise the compiler there and then will give out a mistake. Also it is not forgotten to close a source after work - we release memory. Basically it is not obligatory, at an output from procedure Access it all the same will null, but it is necessary to develop such good habit – to release variables in the end of procedures.
Let's cause function to similarly previous example:
Private Sub City_NotInList(NewData As String, Response As Integer)
Dim ctl As Control ' Returns object Control specifying on a floor with the list
Set ctl = Me!????? ' is appointed by a variable ctl current control
' the Invitation to confirm input of new value
Response = AppendLookupTable(ctl, ctl.Text)
Me.?????.RowSource = Me.?????.RowSource ' updating of the list (in fact there there was a new element)
End SubForms – Handbooks
Now we shall borrow at last in help forms. We shall begin with simple: we shall create the tabulared form – the directory. On event "Opening" of the form we hang up procedure:
Private Sub Form_Open(Cancel As Integer)
Form.Caption = Me.Form.Name ' it is appropriated(given) to the name of the form its(her) name in base
DoCmd.MoveSize , , 5000, 3000 ' we set the sizes of the form (width, height)
DoCmd.GoToRecord , , acNewRec ' we establish focus on last record
End SubWe have received the elementary form the directory. It is necessary to think up, how it(her) to cause? Usually apply two ways:
1.Time the directory - a source of a field with the list why twice to not click under the corresponding list for its opening? And that it as that to legalize, we shall paint the list in dark blue (it is possible, certainly, and any another) color and we shall declare in the information to the program, that at double click on a field of dark blue color the help table appropriate it opens. We create procedure of opening of the tabulared form:
Private Sub idRegions_DblClick(Cancel As Integer)
DoCmd.OpenForm "Regions", acFormDS, , , , acWindowNormal
End SubacFormDS – the instruction(indication) that the form opens in a mode of the table
acWindowNormal – we specify type of border (a kind of the form) – usual Windows ("Normal" in the designer of forms)It is necessary to make one more important thing: to update the list at closing the directory. In fact if we have brought there new data (or have removed) changes it is desirable to display there and then in the list.
Private Sub Form_Close()
If IsLoaded("Example1") Then Forms!Example1.idRegion.Requery ' ????????? ???? ?? ??????? id?????? ?? ????? ??????1
End SubIsLoaded – this function of check, whether is opened the form "Example1". It is taken from base «Borey».
2. We shall create on the main form the list with names of directories, and them we shall open double click under the necessary name
For the organization of the similar interface the service table «FormsParametr» and function fOpenForm is required to us. In this example for a change we shall apply other kind of the form.
The directory at us will consist of two forms: the simple form will serve as the container for tabulared. A field of grey color – a field of the filter. Start to enter into it the first letters of a required designation – and contents of the table begin to be filtered dynamically. The filtration occurs by means of function «fFilForm».
And now the most interesting: I have made only one similar form the directory (the basic form the "Directory", repaired tabulared «SubFrm»), and in their list at me two: the Countries, Regions. And you will not find forms in base « the Directory of the country », « the Directory regions ». Whence they undertake? All is very simple. We shall consider(examine) function «fOpenForm».
Function fOpenForm(idForm As String) As Boolean
On Error GoTo Err_
strFormName = DLookup("FormName", "FormParametr", idForm)
strTextFormName = DLookup("FormCaption", "FormParametr", idForm)
strTableName = DLookup("Table", "FormParametr", idForm)
DoCmd.OpenForm strFormName
fOpenForm = True
Exit_:
Exit Function
Err_:
MsgBox Err.Description
Err.Clear
fOpenForm = False
Resume Exit_
End FunctionHere we see procedure of assignment of values to three variables: strFormName, strTextFormName, strTableName. And now we shall look to the very top of module " Module1 ". There we shall see
Public strFormName As String
Public strTextFormName As String
Public strTableName As StringHere we have approached to such important concept, as area of visibility of variables. Time these(it) three variables are declared in section General of the general module with keyword Public they become visible for all modules of the appendix. For example, they will be seen" with procedures of the module of the form "Directory" to appropriate to the form ñîîòâåòñâóþùèå parameters. We shall consider them.
In the table «FormParametr» there are three fields: FormName (a name of the form in the appendix), FormCaption (a text designation of the form, or simply heading of the form), the Table (a source of data of the form – a name of the table). In function «fOpenForm» there is an assignment to these variables of values by means of
DLookup("FormName", "FormParametr", idForm)
Apparently, values undertake from fields of the service table «FormParametr». And then a command on opening of form DoCmd. OpenForm strFormName
Now we shall glance in the module of the form "Directory". We shall consider the procedure occuring at opening of the form:
Private Sub Form_Open(Cancel As Integer)
Form.Caption = strTextFormName
Set idField = Me.?1
strSql = "SELECT [" & strTableName & "].id, [" & strTableName & "].Name FROM [" & strTableName & "]"
strSql1 = " ORDER BY [" & strTableName & "].Name"
Subfrm.Form.RecordSource = strSql & strSql1
End SubOpening of the form occurs after assignment of value to global variables. The form receives the designation and a source of data at opening.
The breadboard model of the form has as a result turned out. At start to the form the text designation is appropriated and the source of lines by means of global variables clings, values for which undertake from the service table. Thus, becomes possible using only one breadboard model of the form "to create" set of the same directories. For this purpose it is enough to fill with necessary data the service table only. The only stipulation: in all help tables should be two fields, to refer to they should equally: id, the Designation. It imposes restriction on possible(probable) variants of directories, but in fact it is a question of simple help forms.
And now, try(taste): "create" the directory of "City", having filled with corresponding data the table «FormParametr»
Till now we considered only the elementary directories. But present: in each country tens regions, in each region tens cities. For the country of cities one hundred will turn out not. It is not convenient to choose from the list with several hundreds records. It would be better, if having chosen region, in the list of cities cities corresponding it would be displayed only. For this purpose directories be required a special kind: multilevel.
The author: Admin