VBA Loop records (1 Viewer)

Hek

Registered User.
Local time
Today, 09:53
Joined
Oct 30, 2018
Messages
73
Hi all,

Could really use some help with my VBA code, I'm trying to add all the records of one table (tasks) to another (employee_tasks) using a while loop. The main field that needs to be added it the task ID but at the moment it keeps crashing when I try and run it which I'm pretty sure is because its creating too many new records. below is the code and any help will be greatly appreciated. Thanks in advance.

Dim dbs As Database
Dim rsTasks As DAO.Recordset
Dim rsemployee_tasks As DAO.Recordset

Set dbs = CurrentDb()
Set rsemployee_tasks = dbs.OpenRecordset("employee_tasks")
Set rsTasks = dbs.OpenRecordset("Tasks")

rsTasks.MoveFirst
Do Until rsTasks.EOF
If Not IsNull(rsTasks![task_ID]) = False Then
rsemployee_tasks.AddNew
rsemployee_tasks.Update
End If
Loop

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:53
Joined
May 7, 2009
Messages
19,169
you just run a Simple Append/Insert Query.
Make sure that you edit employee_tasks table first adding
index to the combination task_id + employee_id (No duplicate index).

insert into employee_tasks (employee_id, task_id)
select employee_table.employee_id, tasks.task_id from employee_table, tasks;
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:53
Joined
Sep 21, 2011
Messages
14,044
Regardless, you are not moving the record pointer for rstasks, so always adding the first record.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:53
Joined
Feb 28, 2001
Messages
26,999
OK, nobody else seems to have mentioned this so I will.

In Access, with proper table design, you never have to do this. You are duplicating data, which generally goes against normalization guidelines. Instead, you would simply add a flag of some sort to the tasks table and set it when it changes status from "ordinary task" to "employee task." Then when looking at tasks, you just check for the flag to choose what kind of task you wanted to see.

Which isn't to say you CAN'T do what you are doing. But perhaps you are working harder than necessary. If you want to pursue this idea, post again and ask about it.
 

Eugene-LS

Registered User.
Local time
Today, 19:53
Joined
Dec 7, 2018
Messages
481
below is the code and any help will be greatly appreciated. Thanks in advance.
It's not the best method you've chosen, but you can do it this way:
Code:
Dim rsTasks As DAO.Recordset
Dim rsemployee_tasks As DAO.Recordset

    Set rsTasks = CurrentDb.OpenRecordset("Tasks", dbOpenSnapshot)
    Set rsemployee_tasks = CurrentDb.OpenRecordset("employee_tasks", dbOpenDynaset)
 
    Do Until rsTasks.EOF
        If Not IsNull(rsTasks!task_ID) = False Then
            With rsemployee_tasks
                .AddNew
                !task_ID = rsTasks!task_ID
                ' ... other fields ...
                .Update
            End With
        End If
       .MoveNext
    Loop

    rsemployee_tasks.Close
    Set rsemployee_tasks = Nothing
    rsTasks.Close
    Set rsTasks = Nothing
 

Hek

Registered User.
Local time
Today, 09:53
Joined
Oct 30, 2018
Messages
73
Thanks for the input everyone, the reason I want to do it this way is because all the records in tasks are the same for every new order that comes in and there are 28 individual tasks that need to be marked as completed, so I was trying to use VBA to add all the tasks in the tasks table to the employee_tasks whenever a new order is submitted. I was also thinking that this would make it easier to change if the tasks themselves changed in the future. If anyone believes there is a better way to do this I'd love to hear your thoughts
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:53
Joined
Feb 19, 2002
Messages
42,971
Are you really adding all tasks to all employees for each new order? It seems like the tasks should be associated with the order. Or do certain employees do certain tasks?
 

Hek

Registered User.
Local time
Today, 09:53
Joined
Oct 30, 2018
Messages
73
Are you really adding all tasks to all employees for each new order? It seems like the tasks should be associated with the order. Or do certain employees do certain tasks?
All orders will be the same so the tasks will be too. The tasks will be associated with the order and certain employees will do certain tasks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:53
Joined
Feb 19, 2002
Messages
42,971
This should be done with an append query rather than a VBA loop.
 

Users who are viewing this thread

Top Bottom