Writing Array Values to a Table

[JDU]

This is what I am trying to accomplish:

I have 2 arrays; one holds employee IDs and the other holds the number of depts that they work in (ie. recordcounts)

I have been able to code for the values to be stored in the arrays but don't know how to write these values to a table.

Another twist is this: I want to assign priorities based on the recordcounts (follow example below)

Example: Employee A works 2 depts. Employee B works 3 depts.
Array Employee ={A,B}
Array NoOfDepts={2,3}

Based on this, I want to write the following to a table.
Employee Priority
A 1
B 2

Can someone help me with the latter. I am including the code that shows how the array values are being created. Thanks

rsSpecificDeptTable.MoveLast
rsSpecificDeptTable.MoveFirst

NoOfEmployees = rsSpecificDeptTable.RecordCount

ReDim strEmployeeID(NoOfEmployees)
ReDim strNoOfDepts(NoOfEmployees)

rsSpecificDeptTable.MoveFirst
i = 0

Do While Not rsSpecificDeptTable.EOF

strRetrieveDeptsForEmployeeSelected = "Select * from tblNormalSchedule where txtEmployeeID=" & Chr(34) & rsSpecificDeptTable & Chr(34)
Set rsRetrieveDeptsForEmployeeSelected = db.OpenRecordset(strRetrieveDeptsForEmployeeSelected)

If rsRetrieveDeptsForEmployeeSelected.RecordCount <> 0 Then
strEmployeeID(i) = rsSpecificDeptTable!txtEmployeeID
strNoOfDepts(i) = rsRetrieveDeptsForEmployeeSelected.RecordCount
End If

i = i + 1

rsSpecificDeptTable.MoveNext
Loop

 

[scking]

You go to a lot of work to create and manage recodsets and they create two separate arrays the would also have to be sychronized just to get counts you could get with domain aggregate functions like DCount.

1 ---------------------------------------------
If you want to use an array start like this
Type EmpInfo
Emp As String
NumOfDepts As Integer
End Type
Dim myArray() As EmpInfo

Now myArray(i).Emp and myArray(i).NumOfDepts can both be addressed using a single subscript value.
---------------------------------------------
2 -------------------------------------------
You are extracting information from a recordset and putting
it in an array. You could easily just use the recordset or use the aggregate functions and avoid even using the arrays.

' Using an aggregate function to get the number of employees
Dim lngCountOfEmployees As Long
Dim iEmp As Integer
lngCountOfEmployees = DCount(&quot;[txtEmployeeID]&quot;, &quot;tblNormalSchedule&quot


3 -------------------------------------------
You could link the two different tables together using a query or write the SQL to join the two tables using txtEmployeeID. Then you would not need to use two different recordsets that have to be synchronized.






-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------