Can I bind a form to a collection.

[adolph]

I wrote an access2000 database for a POS system. In it is a sales
form with a a subform showing the items being purchased. The main
form is unbound. It uses a class to hold the main form data and to
read/write to the sales table.
What I'm having trouble with is what to use as the 'Recordsource' for
the subform (Items being sold). I do not want to bind the subform
directly to the itemsSold table. What I would like to do is create a
collection of objects that are of a items class and bind the subform
to this.
Another option would be to create a recordset completely in memory (No
underlying table) and bind the subform to it. Here the issue is when
the complete sale button is pressed on the main form, how do I
reference the recordset of the subform. IE where should I create the
virtual recordset (subform, main form, module) and depending where I
create it, what is hte scope of the recordset.
Any ideas would be appreciated.
Adolph

[Rich P]

If you dont want to bind your textboxes you could use a recordset object
and cycle through the recordset object

Dim DB As DAO.Database, RS As DAO.RecordSet

Sub Form_Load()
Set DB = CurrentDB
Set RS = DB.OpenRecordset("Select * from tbl1 Where ID = x")

txt1 = RS!fName
txt2 = RS!lName
End Sub

Sub Current()
RS.MoveNext
txt1 = RS!fName
txt2 = RS!Lame
End Sub

Hopefully, you get the idea. But this will become a bunch of spaghetti
code. Something a little easier that does not bind your form to a table
(but sort of binds the textboxes to fields in your recordsource) would
be this:

Sub Form_Load()
Me.RecordSource = "Select * From tbl1 Where ID = x"
End Sub

In the properties of each textbox you set the controlsource just to the
name of the field in your recordSource for each field. You could also
do this with VBA, but again it starts becoming spaghetti code -
especially if you want to change the number of fields in the
recordsource.

VB.Net has greatly improved this type of functionality with the
Datagridview control (which would be a subform in Access).

Datagridview1.DataSource = dataset1.tbl1

this gives all the fields in tbl1. Then

Datagridview1.DataSource = dataset1.tbl2

this would give/show all the fields in tbl2 which is different than tbl1
but you don't have to write any spaghetti code when you change the
datasource.
Rich

*** Sent via Developersdex http://www.developersdex.com ***