SQL Statement into an Array based on Combo box select

[gal4y]

I would like to make a two dimensional array based on a combo box select.

I have a table (XY Pairs) with three fields: capability, X Value and Y Value. I need to do some computations but it must be in an array first. Capability is a number (1 to ??), X and Y values are also numbers).

If Capability =11 then the sql looks like this:

Project1 0 0
Project1 1 5
Project1 2 15
Project1 3 30
Project1 4 60
Project1 5 100

I would like to capture the last two columns into an array.
The SQLString doesn't seem to work correctly. I have a listbox on the form and a button to put the values into the array. The record set for the form is based on another table and the list box is unbounded.

Here is what I have so far:

Dim SQLString As String
Dim ListboxValue As Double

ListboxValue = Me.List13.Column(0) 'Captures Value from ListBox on Form


SQLString = "SELECT [XY Pairs].Capability, [XY Pairs].[X Value], [XY Pairs].[Y Value]" _
& "FROM [XY Pairs]" _
& "WHERE ((([XY Pairs].Capability) = '" & ListboxValue & "'" _
& "ORDER BY [XY Pairs].[X Value]"

Dim dbs As database, rst As Recordset
Dim recnum1, e, f As Integer
Dim myarray(6, 6) As Variant
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(SQLString)
recnum1 = rst.RecordCount

For e = 0 To recnum1
myarray(e, 1) = ??????

On Error Resume Next
recnum1.MoveNext Not does
rst.MoveNext
Next e


rst.Close

db.Close

Thanks for any help in advance
Greg

 

[KenReay]

Hi

so what happens?

to begin the recordcount is not accurately populated until you move to that last record in the set, so you need:

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(SQLString)
If rst.RecordCount > 0 Then
rst.MoveLast
rst.MoveFirst
recnum1 = rst.RecordCount

For e = 0 To recnum1
...etc

or better still

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(SQLString)
e = 0
Do Until rst.EOF
e = e + 1
myarray(e, 1) = ??????

On Error Resume Next
recnum1.MoveNext Not does
rst.MoveNext
Loop

Note I have not examined you code to see if it does what you intend, just picked out a couple of obvious problems


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK