Access database Development Techniques (III)

The use of the module skills

The most important feature of access is that you do not have to write programs in general, but you may have to write programs in the module with Visual Basic in order to perform complex or professional operations, and then execute the appropriate programs by using the command buttons in the form to do the appropriate tasks. such as ranking, segmentation and other statistics. Note the following questions and tips when writing a Visual Basic program.

1, the use of variables

The application of ① object variable
Using Visual Basic to write programs in access requires the use of their data access objects. It includes objects such as Database Engine (DBEngine), Workspace (Workspace), databases, and tables, queries, recordsets, and so on in database objects. such as default workspace: Dbengine.workspaces (0), current database: Dbengine.workspaces (0). Databases (0). We can use an object variable to represent an object, which will be very handy for programming. Assign a value to an object variable by using the SET statement instead of directly assigning a value. For example:
Dim Thedb as Database
Set thedb = dbengine.workspaces (0). Databases (0)

Application of ②variant data type
A Variant data type is any data type that is not displayed as a statement (such as Dim, Private, public, or static) for other types of variables. A Variant is a special type of data that can contain any kind of data in addition to fixed-length String data and user-defined types. A Variant can also contain Empty, Error, nothing, and null special values. The Empty value is used to mark a Variant variable that has not yet been initialized (given an initial value). A Variant containing Empty represents 0 in the context of a numeric value, or a 0-length string ("") if used in the context of a string. Null indicates unknown or missing data, or invalid data. You should not confuse Empty with Null. Null indicates that the variant variable does contain an invalid data.
You can define the variable as a Variant data type when there are some quantities that may appear to be null values. Variant data types provide a lot of flexibility for programmers, but at the expense of the speed and length of the code.

③ indirectly referencing database objects and qualifying conditions through variables
In order to increase the flexibility, improve the control ability, reduce the repetition of the Code and use the circular statement, the variables should be used in the program to indirectly reference the database object and the qualification condition. For example, the ranking of the grades of students in each grade of the processing program, only the grade table name of different grades, we can use the program in a variable such as "BZCJB" to express, the specific processing and then replaced with a specific table name, such as the replacement of the first grade of the score table name "G1CJB".
There are some problems to be noticed in the concrete application, such as:
Dim Thedb as Database
Set thedb = dbengine.workspaces (0). Databases (0)
Dim THECSB as Recordset
Set THECSB = thedb. OpenRecordset (BZCJB, Db_open_dynaset)
Here "BZCJB" can represent any one grade, if "BZCJB" itself is an existing table name, then the last statement of the "BZCJB" should be enclosed in quotes, as follows:
Set THECSB = thedb. OpenRecordset ("BZCJB", Db_open_dynaset)
Again, for example, the DCount function can be used to determine the number of records in a particular set of records. You can use the DCount function in Visual Basic, macros, query expressions, or calculated controls.
Syntax is: DCount (expr, domain[, criteria))
The parameters are as follows:
The expr expression that identifies the field whose records are to be counted. can be a string expression that identifies a field in a table or query. Expr can be the name of a field or "*", unless it is "*", the DCount function does not count the records that contain blank fields, and the DCount function calculates the total number of records, if "*". A domain string expression that represents the recordset that makes up a field, either the name of the table or the name of the query. The criteria optional string expression used to restrict the range of data that the DCount function executes. The criteria for limiting criteria can be replaced by a variable, such as the number of people whose field "language" score in "G1CJB" is greater than or equal to 80 points, which can be completed using the following formula:
DCount ("*", "G1CJB", "[Class]>=80")
If you replace "80" with the numeric variable "n", the following is:
DCount ("*", "G1CJB", "[Class]>=" & N)
If you replace "80" with the character type variable "n", you would do the following:
DCount ("*", "G1CJB", "[Class]>= '" & N &] ")
Be aware that the alternative forms are different. The symbol "&" above is the concatenation operator.

2. How to Generate program run progress box

Access can achieve this requirement if you can generate a program to run a progress box while the program is running. I have found a simple way of doing this by building two text boxes in a form, a larger, rectangular text box (the fill color is white) as a frame, a narrow, rectangular text box (filled with other colors, The length is 0.011 cm, and as the production process runs pipelining, their initial state is not visible. You can then control the progress of the program by controlling its properties as visible and modifying the width of the Progress pipeline text box. Note that in order for the progress box to be displayed in time when the program is running, you must use the SetFocus method to move the focus to the progress box, and adjust the initial position of the pipelined text box and the Outer box text box. Notice that when the Width property is used to change the control, its unit is "Twip", 1twip=1/1440 inches, and about 1/567 centimeters.