Collection classes and Continuous Forms

[puckman11]

I'm inexperienced with VBA Class Collections. I have crudley coded some objects which reflect my data requirements. I have experimented successfully and miserably with code up to the point of establishing a collection class which arranges these into a hierarchy. I think a learning curve remains ahead of me yet. So my question is derived more from my intuitive recognition of CC's.

Before I go headlong trying to experiment some more, is it possible to have a continuous form with a collection class as its Recordsource? I gave up on trying to find a working SQL string that produced a record source which could work with a form I would like to use. This is why I turned to CC's. It looks promising.

[MMcCarthy]

The simple answer is yes as you can populate an unbound form with anything you like really. However, I don't think this is the answer you are looking for.

Can I suggest you post the query you are having problems with and we can try to get it to work for you.

If you do want to pursue the collection class option I am not a fan of using unbound forms and so am probably not the best person to help you but I can direct someone else to fill in.

[puckman11]

Hi "Mac",
Thanks for the quick reply. I think I know toward where the answer to this thread could go. The SQL string is unoffensive. I have returned exactly what I expected in an SQL-check app provided by Ken Getz.

SELECT [Student Basic].[ID] as ID,[Student Basic].[LastName] as LastName, [Student Basic].[FirstName]as FirstName,[Student Basic].[Gender] as Gender, [ModuleMark].[CourseID],[ModuleMark].[Mark] as Mark, [ModuleMark].[EvaluationType]FROM [Student Basic] INNER JOIN ModuleMark ON [Student Basic].[ID]=[ModuleMark].[StudentID] Where CourseID=1

- I arbitrarily set a criteria in "WHERE" just to see if I could drill down to one Course. I need to poll the entire evaluation table.

The problem was marshalling them into a continuous form as to retain the one-to-many relationship with unbound controls, as you have deduced. {Love how programmers are so quick with this line of thinking}

Where I think you're heading and where I might have follow, is to refine the solution to continue normalizing the data model so that the foreign keys always contain the StudentID so as to point any info that pertains to them.

The above SQL sample is a Recordsource for the home (opening) form that provides a quick view of students, both currently active and inactive, for one of twenty four different evaluations they must endure. I am almost certain I can quickly filter with ComboBoxes the students into various categories from the header of the Form.

I have CommandButtons for each record in the contiuous form which can open/jump to a pertinent form. Unfortunately, the Form thus far show a record of each Student's evaluation on each record in a continuous form. Not what I wanted. I have three options, Normalize further, create a different "Home Screen", or swim further into the murky waters of marrying objects to Access DAO.

Your answer is not a defintive "No" to steer me away from Object Oriented Modeling. However, your response has a verve about "Bound Controls" I like. After all, that's how I cut my teeth on Access. What has me giddy is your wonderful provacation toward me to re-think my approach.

Eddie

[NeoPa]

My understanding is that the answer to the question is actually "No".

A Recordsource can be the name of a table, or of a query, or alternatively a SQL string. If the form is unbound then it has no Recordsource setting.

[puckman11]

Agreed. I should have been more careful with my denotation of the word "RecordSour ce" - should have read "Record Source." I meant it in a more generic sense rather than the specific property of a form.

My intention was for the data object to be contained in a Collection Class and then use methods/properties to pass and accept data to and from unbound controls on a continuous form. I have never coded using this approach; never mind basing a whole working database application upon it. I wanted simple assurance this was do-able from someone more experienced.
Since my initial post, I shelved my Collection Class approach and went back to Bound Forms and Controls. This has placed other challenges before me. I am sure I will be back posting another question.

[NeoPa]

Puckman11:
Since my initial post, I shelved my Collection Class approach and went back to Bound Forms and Controls. This has placed other challenges before me. I am sure I will be back posting another question.

That sounds eminently sensible. The idea of using unbound forms in Access to do the work that Access does for you isn't something I'd recommend, but you sound like you have a good understanding of classes and OO, which compares well with my more limited understanding of those subjects, so it's hard to be certain you haven't seen something I've missed.

From the Access perspective though, your new approach sounds eminently more sensible. As someone who seems able to express their problems reasonably clearly, more questions from you would be welcome.