Database Query ...

[plenderj]

Howdy.

Im reading data in from a database using this kinda crap : Set DB = OpenDatabase(App.Path & "\database.mdb") etc.

Aaaanyway.
Normally I'd get info like so :

    Set RS = DB.OpenRecordset("SELECT * FROM tblCompany WHERE RecNum = '" & CurrRecNum & "'", dbOpenDynaset)
    MsgBox "" & RS!CompanyName

But now I have a form with a control array with 31 text boxes on it. The database also contains 31 fields (just 1 table) that correspond to the form.
I dont want to have to keep manually write out that :

txtDisplay(0) <--> NameOfOperation
txtDisplay(1) <--> Date Started
txtDisplay(2) <--> Name
... and so on.


So I came up with an idea that I could do something like this :

    Set RS = DB.OpenRecordset("SELECT * FROM tblCompany WHERE RecNum = '" & CurrRecNum & "'", dbOpenDynaset)
    With RS
        For i = 0 To 30
            txtDisplay(i).Text = "" & RS!recDisplay(i)
        Next i

'''  elsewhere then

Public Function recDisplay(Index As Long) As String
    Select Case Index
        Case 0:
            recDisplay = "NameOfOperation"

Now the above code doesnt work.
It would work if I was simply grabbing RS!NameOfOperation
But I wanna use the above approach.

So any ideas, or any better ways of doing it ?

- jamie

[nullus]

in ADO you could do this:

RS.Open = "SELECT * FROM tblCompany WHERE RecNum = '" & CurrRecNum & "'", conn, adOpenStatic, adLockOptimistic
        
For i = 0 To RS.Fields.Count
  txtDisplay(i).Text = RS.Fields(i).Value
Next i

/me spits on DAO

[plenderj]

        For i = 0 To 0
            txtDisplay(i).Text = "" & RS.Fields(recDisplay(i)).Value
        Next i

works

Thanks.