Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using VBA to loop/save form records?

Posted on 2008-10-07
8
Medium Priority
?
4,536 Views
Last Modified: 2013-11-28
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
        End If
        
        'If we haven't reached the last analyst...
        If Counter <> AnalystCount Then
            '...move to the next employee
            Me.EmpID = EmpID + 1
        End If
    Loop
End If
 
'Let the user know that everything saved
MsgBox "Saved!", vbOKOnly
 
End Sub

Open in new window

0
Comment
Question by:shadowfox2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22675406
I can't see anything in your code that is actually creating a record to save?
0
 

Author Comment

by:shadowfox2010
ID: 22675478
Oops! The information that it's saving is entered into bound text/combo boxes on the form - there's a query running behind it.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22675751
shadowfox2010,

OK, But still, somewhere you would have to be creating this new record for each employee.
How is this query creating the new records?

The way I see it you would have to be setting some sort of flag, to say that they are all selected for a particular class.
Or even more complex, creating a child record (Training session) for each member.
(presuming a One to many relationship between Employees and classes.)

The logic would be something like this:

Is this employee an Analyst?
If Yes, then create a record that puts them in a training session
If no, then move to the next employee.
Repeat until the end of the employee file.

But you would really need a recordset to do this.

JeffCoachman.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:shadowfox2010
ID: 22679462
Hmm, I'm not sure what you mean.

There's a many-to-many relationship between employees and classes, with a middle table connecting the two (tblEmpTrng). tblClasses only holds the list of available classes - tblEmpTrng holds all of the specifics like hours, associated costs, dates, etc. So yes, I am trying to create a training session record for each employee.

That's the logic that I tried to use with the Do Until loop. I'm able to successfully have it count all the analysts, so I know that it's performing the checks correctly. My problem is that it doesn't create a unique record for every analyst when I tell it to save (Me.Dirty = False) - it keeps updating the same record in the table.

How would I use the recordset?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1400 total points
ID: 22680888
shadowfox2010,

"Hmm, I'm not sure what you mean"
That's the point,

You are telling us what you need, but we do not know anything about your database.
;-)

For example:
 "There's a many-to-many relationship between employees and classes, with a middle table connecting the two"
...This is new information not mentioned in your original post.

The bottom line is you must loop each member of a certain Type AND create a Class "Record" for each of them.

" it keeps updating the same record in the table."
Then for some reason your code is not moving to the next record.


The sticking point here seems to be that you have designed your interface first.
Instead, ...it might have been better if you had asked:
"I need to assign multiple employees in the same category, to one class, how might I accomplish this?"
;-)

Here is an basic example of how this is typically done.
In this sample you would have to:
- Create your forms with the appropriate lookup comboboxes.
- Decide on a Primary Key strategdy for tblEmptTrng to prevent/allow duplicates in any combination of Employess, Classes and/or Dates.

(After you click the Assign button, you can view the Query: "qryEmployeesAndClasses", to verify that the entries were made.)


I am sure you can adapt this basic sample to work in your database.
;-)


JeffCoachman
Access-EEQ23795701-AssignEmploye.mdb
0
 

Author Closing Comment

by:shadowfox2010
ID: 31504051
Thanks much for the help! The example really helps me understand. :-)
0
 

Author Comment

by:shadowfox2010
ID: 22736530
Awesome, thanks for the sample!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22738146
;-)

Jeff
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question