UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Creating A Recordset From A User Defined List, Access 2016    
 
   
Ub78Nx
post Oct 9 2017, 01:20 PM
Post#1



Posts: 2
Joined: 9-October 17



Hi, I have a database that includes employee ID#s, employee names, and supervisor names among other information. I am trying to come up with a way to update the supervisor names in groups for times when a new supervisor takes over for an old supervisor. This could affect dozens or hundreds of employees and finding each one and manually changing the supervisor name is just taking too long.

My idea was to create a record set using the employee ID numbers and then looping through the recordset to change all of the supervisor names. I have used recordsets before, but only when I have one criteria (ex. employee ID = 999999). What I would like to do is allow the user to enter a list of employee numbers that I can then put into the SQL statement to get just the numbers I need. I can not figure out how to go about this however. I have been able to create a recordset using a sample list of numbers, but have not had any luck allowing user entry and making that the list. Any help would be appreciated.

If there is a better way to go about this I am open to other ideas as well. I try to make the knowledge I have work in new situations but I realize there is a lot of stuff I still don't know.
Go to the top of the page
 
doctor9
post Oct 9 2017, 01:31 PM
Post#2


UtterAccess Editor
Posts: 17,726
Joined: 29-March 05
From: Wisconsin


Ub78Nx,

A supervisor is also an employee, so they should not have their name stored in a text "supervisor's name" field. Instead, you should have a foreign key field that links to the primary key of the Employee table. (This assumes that a person has only one supervisor.)

tblEmployees
EmployeeID [Autonumber, Primary Key]
strFirstName
strLastName
lngSupervisorID [Foreign Key to tblEmployees.EmployeeID]

So, let's say Amy is the supervisor over Bob and Carol. The records would look like this:

CODE
EmployeeID - strFirstName - strLastName - lngSupervisorID
    17           Amy          Anderson        [Null]
    23           Bob          Baker             17
    31           Carol        Carson            17


If the lngSupervisorID is Null, that means the employee has no supervisor.

Now, if Amy left the company and you hired a new guy named Dave to be the new supervisor, you could use a simple Update query to change the 17's to Dave's EmployeeID value. No need for a Recordset.

If you want to pick and choose a group of random employees (i.e. employees who have nothing else in common, like an old supervisor), you could create a form, and add a Listbox that lists all of the employees. The user could select an employee in a separate combobox, and the list of employees that he/she will supervise in the listbox. You could use a command button to commit the list of people to that supervisor using VBA. Just loop through the selected items in the listbox and set that employee's lngSupervisorID to the EmployeeID selected in the combobox.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
cheekybuddha
post Oct 9 2017, 06:58 PM
Post#3


UtterAccess VIP
Posts: 9,173
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

Dennis has given you a useful suggestion wrt your table setup, but to answer your question more directly you can do something like:
CODE
  Dim rs As DAO.Recordset, strSQL As String

' Use an 'IN' clause to select multiple results
  strSQL = "SELECT EmployeeID, EmployeeName, SupervisorName FROM tblEmployees WHERE EmployeeID IN (111, 222, 333);"
  Set rs = CurrentDB.OpenRecordset(strSQL)
  With rs
    If Not (.BOF And .EOF) Then
        .MoveFirst
        .Fields("SupervisorName") = "New Supervisor"
        .Update
        .MoveNext
        .Fields("SupervisorName") = "New Supervisor"
        .Update
        .MoveNext
        .Fields("SupervisorName") = "New Supervisor"
        .Update
    End if
    .Close
  End With
  Set rs = Nothing


Obviously the above example is still pretty manual, but shows you the format of the 'IN' clause.

You can use arrays of EmployeeID's and new Supervisor names, and loop through them to build the IN clause in code, and loop through the recordset to update the names.

I would actually find it easier just to use those arrays to build SQL UPDATE statements and execute them rather than using a recordset at all.

Something like:
CODE
  Dim strSQL As String, arrEmployeeSupervisors As Variant, i As Integer

  arrEmployeeSupervisors = Array( _
    Array(111, "New Supervisor 1"), _
    Array(222, "New Supervisor 2"), _
    Array(333, "New Supervisor 3") _
  )

  For i = LBound(arrEmployeeSupervisors) To UBound(arrEmployeeSupervisors)
    strSQL = "UPDATE tblEmployees SET SupervisorName = '" & Replace(arrEmployeeSupervisors(i)(1), "'", "''") & "' WHERE EmployeeID = " & arrEmployeeSupervisors(i)(0) & ";"
    CurrentDb.Execute strSQL, dbFailOnError
  Next i


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Ub78Nx
post Oct 11 2017, 01:01 PM
Post#4



Posts: 2
Joined: 9-October 17



Thank you both for the responses. I don't have much experience with arrays but I do like that approach; it is something I will look into for the future. What I ended up doing is just storing the employee IDs in a listbox as they're entered, which allows for some verification, then cycling through the listbox and updating the supervisor name with a loop command. It took me a while but it works. Thankfully now I have some extra time to try and refine it Thanks again.
Go to the top of the page
 
cheekybuddha
post Oct 11 2017, 05:23 PM
Post#5


UtterAccess VIP
Posts: 9,173
Joined: 6-December 03
From: Telegraph Hill


Cycling through a listbox is essentially the same as cycling through an array.

Glad you got it working. Dennis and I are pleased to assist.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th October 2017 - 06:17 AM