#1 Arrays can be used with variables?
Of course they can. Control arrays are a deviant structure that simulates a normal array very badly. You could create User Defined type to store employee data, and then have an array of those types:
Option Explicit
Public Type EmployeeData
FirstName As String
LastName As String
HoursWorked As Long
End Type
Private Employees() As EmployeeData
Private EmployeeCount As Long
Private CurrentEmployee as Long
CurrentEmployee indicates the current index into the array.
Wether you have "Next" or "Previous" buttons to move through the data or wether you use a list or some other control to switch between them, the first thing you need is special handling in your various editing controls (in this case, three- first name, last name, and hours worked) that either change the "current" employeedata or break out if EmployeeCount is 0.
Adding a new Employee:
Private Sub CmdAdd_Click()
EmployeeCount=EmployeeCount+1
Redim Preserve Employees(1 to EmployeeCount)
CurrentEmployee=EmployeeCount
RefreshView()
End Sub
This adds a new item, and then refreshes the controls displaying state. It calls the following routine to do that:
Private Sub RefreshView()
If CurrentEmployee=0 then Exit Sub
txtFirstName.Text = Employees(CurrentEmployee).FirstName
txtLastName.Text = Employees(CurrentEmployee).LastName
txtHoursWorked.Text = Employees(CurrentEmployee).HoursWorked
if(currentemployee) = Ubound(Employees) then cmdNext.Enabled=False Else cmdNext.Enabled=True
if(currentemployee) = Lbound(Employees) then cmdPrev.Enabled=False Else cmdPrev.Enabled=True
End Sub
To move between the various employees, you merely change the "CurrentEmployee" variable and refresh the view again:
private sub CmdNext_Click()
CurrentEmployee=CurrentEmployee+1
RefreshView()
End Sub
Private Sub CmdPrev_Click()
CurrentEmployee=CurrentEmployee-1
RefreshView()
End Sub
And so on.
The various text boxes in question (txtFirstName,txtLastName, txtHoursWorked) can be used to update the "current" employee.
You can either take advantage of the Change event, or use a "Save" button, or save in the cmdNext and cmdPrev routines:
Private Sub SaveEmployee()
With Employees(CurrentEmployee)
.FirstName=txtFirstName.Text
.LastName = txtLastName.Text
.HoursWorked = Val(txtHoursWorked.Text)
End With
End Sub
Then, wherever you need to "save" the current value (either a Specified Save button, in the next or previous buttons, etc) you simply call "SaveEmployee".
Removing an Employee is a tad more tricky. We need to Remove an element, and this is done by moving all the higher elements down one index. You can do this manually:
Private Sub RemoveEmployee(RemoveIndex as Long)
Dim I as Long
For I = RemoveIndex to Ubound(Employees)-1
Employees(RemoveIndex) = Employees(RemoveIndex+1)
Next
EmployeeCount=EmployeeCount-1
Redim Preserve Employees(EmployeeCount)
#2 Collections?
The other option is, instead of using a User Defined Type, you could define an "Employee" Class Module and keep a collection of them. Slightly more "involved" in a few ways, but a lot more flexible as well.