Send Results of SQL Statement Into an Array of Unknown Size?

[Richard Hollenbeck]

Inside a private sub, I want to query another table for list of valid record
numbers. I want the results in an array. But I'll never know from one time
to the next how big to make the array. It's too bad I couldn't just make it
open ended, or can I? I currently have only about 50 records. So if I make
it something like:

Dim recordNum(1 to 1000)

it will work for a few months before I need to change it to a bigger number.
Isn't there a way to dynamically resize the array? Or set the size of the
array based on how many records I will find?

Do I first need to query the table to count the records?

dim numOfRecords as integer
numOfRecords=1

'then insert the results of a count query into
'numOfRecords and use that variable as the
'size of the array.

Dim recordNum(1 to numOfRecords)

Then I could insert the record numbers into this array to use later. Would
something like that work or is there a much simpler approach? Ideas? Many
thanks.

Rich Hollenbeck

 

[Rick Brandt]

Inside a private sub, I want to query another table for list of valid record
numbers. I want the results in an array. But I'll never know from one time
to the next how big to make the array. It's too bad I couldn't just make it
open ended, or can I? I currently have only about 50 records. So if I make
it something like:

Dim recordNum(1 to 1000)

it will work for a few months before I need to change it to a bigger number.
Isn't there a way to dynamically resize the array? Or set the size of the
array based on how many records I will find?

Do I first need to query the table to count the records?

dim numOfRecords as integer
numOfRecords=1

'then insert the results of a count query into
'numOfRecords and use that variable as the
'size of the array.

Dim recordNum(1 to numOfRecords)

Then I could insert the record numbers into this array to use later. Would
something like that work or is there a much simpler approach? Ideas? Many
thanks.

Are you sure you need an array? A RecordSet is easily based on a SQL
Statement and has many of the features you would have in an array along
with many other that an array wouldn't give you.

 

[Richard Hollenbeck]

Thank you, Rick, for your reply. Actually all I really need to do is
filter a subform based on the values in another non-related table. I tried
putting some information in the data filter box in the form properties but
it didn't work. I typed:
=StudentsInCourses.courseCode=[parent]![courseCode]

That didn't do anything. I can still pull up the wrong records. Here's the
bigger picture:

As I'm entering grades for students in a particular activity, I only want
the students that are actually in that course to appear in the list. I want
to populate the subform with the names of the students, but all the students
are available from the roster, even students not in that class.

The relationships are set up like this:
StudentsInCourses
studentID (key)
courseCode (key)
(it's a composite key)

Students
studentID (key)
other fields

Courses
courseCode (key)
other fields

Students 1:M StudentsInCourses
Courses 1:M StudentsInCourses

Groups (groups of activities)
groupID (key)
courseID
other fields

Courses 1:M Groups

Activities
activityID (key)
groupID

Groups 1:M Activities

StudentScores
studentID (key)
activityID (key)
score
(studentID and activityID form a composite key)

Activities 1:M StudentScores
Students 1:M StudentScores

So I have a problem. The main form is based on the Activities table and the
sub form is based on the StudentScores table (actually it's based on a query
including StudentScores, Activities, Groups, Courses, and
StudentsInCourses). I think the program ought to be filtering these extra
students out already, but maybe not. The query pulls from the Students
table, not from the StudentsInCourses table. I don't think I can get the
students directly from the StudentsInCourses table as it would create a
many-to-many relationship.

My subform won't display a roster in that sub form until the composite key
in StudentScores is satisfied and a record actually exists. So I wanted to
use some kind of INSERT INTO query once I find the students from the
StudentsInCourses table who are actually supposed to be in that particular
course. The way it works now it that once I put a valid studentID in to the
subform it will display the student's last and first name and a 0.00% score,
which is fine, I guess. But it will also allow me to enter a valid
studentID from a student who doesn't belong in that course, but in another
course. I need to eliminate those students that don't belong there before
running the INSERT INTO query to populate the grades sub form. I hope this
is clear.

Thank you very much.

Rich Hollenbeck

 

[John Nurick]

Hi Richard,

The first question is, do you really need to get the numbers into an
array? For many purposes you can pull them straight out of the
recordset. For instance, if the values you're interested in are in the
first field of the recordset, you can use something like this

Dim rsR as Recordset
Dim lngIndex As Long, lngValue As Long

... 'populate recordset

With rsR
.Move lngIndex - 1
lngValue = .Fields(0).Value
End With

instead of

Dim rsR as Recordset
Dim arValues(999) As Long
Dim lngIndex As Long, lngValue As Long

...populate recordset
...populate array

lngValue = arValues(lngIndex - 1)

If it absolutely has to be an array, you need to do something like:

Dim arValues(10) As Long
Dim lngNumberOfRecords As Long

... 'populate recordset
lngNumberOfRecords = record count
ReDim arValues(lngNumberOfRecords)
... 'populate array
...