Databases using DAO
An DAO Database control is a very powerful way to set up a database. This is the set up in Visual Basic within Access.
Note that the Database itself is defined as db, there are two Recordset definitions, one for srecset and one for hrecset. Ther are also two field definitions which are fldSalary and fldPayHr. These are all defined in the general area and are therefore available if there are multiple modules. Within the Form_Open event, I have used the SET statement to tell which datbase is being used. Note that I did this setting the entire path and as an alternative, I did it by using CurrentDb which means the current database. Then I defined srecset. This is done by doing an OpenRecordset on the database defined as db and bringing in the records that I want to appear using the SQL Select statement. Notice that the underscore allows you to continue on the next line and we are essentially concatenating together the parts of the SELECT. They could be written on one line, but then it would go off the screen. The SELECT statement is taking in all records where the paycode is S.
Next I moved to the last record in that record set using the MoveLast command attached to that Recordset. At that point, I can count the number of records in the record set using RecordCount and store the results in txtSalCnt.
I then move to the first record in the recordset using MoveFirst. I set the SalTot equal to 0 and then I go through the For loop accumulating the total salary until all the records have been processed or the RecordCount has been reached. Inside the loop, I set the field defined as fldSalary equal to the field called Salary which is on the recordset defined as srecset. Note that Salary is in quotes. I then add fldSalary to SalTot to accumulate the total. I then move to the next record. The For loop is repeated until I reach recordcount. When the for loop is complete, I move the accumulator called SalTot to a field on the form.
I then populate the next record set with all of the hourly employees using the SELECT statement except that this time I am looking for a paycode of H. Note that also I am only selecting the PayHr, one field.
Again, I move to the last record and get the record count. I then move back to the first record and set HrlyTot= 0. HrlyTot is the accumulator that I am using to accumulate the total hourly pay. The For loop is again entered and the processing will be done from 1 to the record count. Note that now I am accessing the field by index. Since I only selected one field, I can use an index of 0 to select the first and this case only field in the collection. I then add the pay per hour to the hourly total. Note that this is not really a very significant accumulation but it serves for demonstration. I then move on to the next field.
When the for loop is done, I move the hourly total to the total field on the form.
The form as you can see, shows the four fields that were placed there in this code.
Option Compare Database Dim db As Database Dim srecset As Recordset Dim hrecset As Recordset Dim fldSalary As Field Dim fldPayHr As Field Dim SalTot As Long Dim HrlyTot As Long Private Sub Form_Open(Cancel As Integer) ' Set db = OpenDatabase("c:\My Documents\drg\payroll.db\testreport.mdb") Set db = CurrentDb Set srecset = db.OpenRecordset( _ "SELECT * " _ & "FROM Payroll " _ & "WHERE PayCode ='S';") srecset.MoveLast txtSalCnt = srecset.RecordCount srecset.MoveFirst SalTot = 0 For i = 1 To srecset.RecordCount Set fldSalary = srecset.Fields("Salary") SalTot = SalTot + fldSalary srecset.MoveNext Next i txtSalTot = SalTot Set hrecset = db.OpenRecordset( _ "SELECT PayHr " _ & "FROM Payroll " _ & "WHERE PayCode ='H';") hrecset.MoveLast txtHrlyCnt = hrecset.RecordCount hrecset.MoveFirst HrlyTot = 0 For i = 1 To hrecset.RecordCount Set fldPayHr = hrecset.Fields(0) HrlyTot = HrlyTot + fldPayHr hrecset.MoveNext Next i txtHrlyTot = HrlyTot End Sub
Let me also show the settings that must be done prior to setting up this database. The developer needs to go into the Visual Basic side of Access and under Tools/Relationships, the developer must select Microsoft DAO 3.6 Object Library. I also found that you need to deselect any Microsoft Data Objects that are checked.