I'm using Access 2007, and the only VBA that I know is what I've picked up along the way..
Ok, on to the database. I have a fairly small database for entering/viewing employee training data, with a main form for actually entering the data (no editing or deleting - only entering). All of our staff members can be grouped into 3 categories and often attend training sessions in bulk. Instead of having to enter the same class for 20+ people, I want to be able to click a checkbox (i.e., "All Analysts"), enter all the training information once, then click Save. I want it to then save individual training records for everyone who falls under the selected category.
Using a Do Until loop inside an If Statement, I'm able to specifically find the ones that fall under the selected category. The problem is that I can't get it to save a separate training record for each of those employees - it keeps updating the same record with the next applicable Employee ID.
Any help would be greatly appreciated!
Private Sub cmdSave_Click()
Dim Counter As Integer
Dim AnalystCount As Integer
'Set the counter to blank
Counter = 0
'Start with the first employee
Me.EmpID = 1
'Count the number of analysts, so the loop knows when to stop
AnalystCount = DCount("EmpID", "tblEmployees", "[Type]='Analyst'")
'Check to see if "All Analysts" is selected
If Me.chkAllAnalysts.Value = -1 Then
'Loop through all this until we reach the last analyst
Do Until Counter = AnalystCount
'Check to see if they're an analyst
If Me.Type.Value = "Analyst" Then
'Add one to the counter
Counter = Counter + 1
'Save the record
Me.Dirty = False
'If we haven't reached the last analyst...
If Counter <> AnalystCount Then
'...move to the next employee
Me.EmpID = EmpID + 1
'Let the user know that everything saved
MsgBox "Saved!", vbOKOnly