Help with RecordSet

[Richard]

I have created a database that is working rather well but would like to
cleanup my VB code some.

In a table I have 50 fields that contain a department number and 50 fields
that have a task number. Below is the code I have had to use. I wanted to
setup a loop but that code gives me a <Item not found in this Collection.>
error.

What I have
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs1 = db.OpenRecordset("Job", DB_OPEN_SNAPSHOT)

Found = "No"
Do Until rs1.EOF
If rs1!JOB_ID = JOBNUM Then
Found = "Yes"
deptnum(1) = rs1!DEPT_NO_1
tasknums(1) = rs1!Task_No_1
deptnum(2) = rs1!DEPT_NO_2
tasknums(2) = rs1!Task_No_2
deptnum(3) = rs1!DEPT_NO_3
tasknums(3) = rs1!Task_No_3
deptnum(4) = rs1!DEPT_NO_4
Rest of code. . . . . . .


What I am trying but not working!

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs1 = db.OpenRecordset("Job", DB_OPEN_SNAPSHOT)

Found = "No"
Do Until rs1.EOF
If rs1!JOB_ID = JOBNUM Then
Found = "Yes"
For k = 1 To 50
deptnum(k) = rs1!DEPT_NO_(k)
tasknums(k) = rs1!Task_No_(k)
Next k
Forms![Select Tracking].Status = rs1!Status
End If
rs1.MoveNext
Loop

If any of you can lead me in the right direction, I would greatly appreciate
it.

RC
(e-mail address removed)

 

[Allen Browne]

Richard, it is possible to create a string as the field name, and use in
like this:
Dim strDept As String
strDept = "Dept_No_ & k
deptnum(k) = rs(strDept)

However, can I suggest that you are wasting your time with this approach?
Firstly, you very rarely need to assign a recordset to an array: you are
usually better off working directly with the recordset, since it has very
useful methods and properties associated with it.

More importantly, though, the data structure is not well normalized. Would
you consider using a related table that gets a new record each week? The
fields would be something like this:
JobID Foreign key to your existing table.
DeptNum Foreign key to a table that has a list of departments.
TaskNum Whatever that is.
TaskDate The Monday of the week that this relates to?

Obviously I don't know what your data actually is, but I'd wager that this
kind of approach will be much more efficient. It will do away with most of
your code, and make it very easy to query comparisions across different
quarters, etc.

 

[Van T. Dinh]

Your Table is NOT normalised. You have 50 repeating groups
(each group has a dept number and a task number) which is
a no-no in Relational Database Design Theory. Generally,
normalised Tables in RDMS are narrow (small number of
Fields) and long (large number of Records) and your Table
is very wide. Generally, if your Table has more than 40
Fields, your Table may not be normalised and you need to
check.

You need to normalise your Table and everything will be a
lot simpler.

HTH
Van T. Dinh
MVP (Access)