Problem with initialising a loop

[dickohead]

Morning all,

I am trying to create a function that will populate one table with items of another, based on the tables ID number.

For example:

There are 4 classes, each one has 10 subjects, there are only 30 subjects in total though, so some of these subjects are shared.

I'm trying to create a selection list with each of the classes in it, that will then allow the user to select that class and populate a table with the appropiate subjects in it.

SQL Example:

SELECT CompID FROM ClassesCompetencies WHERE ClassID = MyDropDownList

Hopefully that makes sense?

Anyway, I have the following code in my function

Function addComps()

DoCmd.SetWarnings False
    
    Dim strCompID As String
    Dim strPicNo As String
    Dim strSQL As String

    ' assign two variables to our CompID and PicNo
    strClassID = Me.ClassListCombo.Value
    strPicNo = Me.PicNo.Value

    ' count how many rows in the table ClassesCompetencies where ClassID is Me.ClassListCombo.Value
    Dim xRows As Long
    ' select CompID FROM Classes Competencies where ClassID = strClassID
    xRows = DCount("[CompID]", "ClassesCompetencies", "[ClassID]=" & strClassID & "")
    ' define the array based on how many rows in the table as above
     Dim myArray(xRows) As String
         
    ' insert data into the array
    strSQL = "INSERT INTO myArray(xRows) ([CompID]) SELECT [CompID] FROM ClassesCompetencies WHERE [ClassID] =" & strClassID
    Dim xArray As Variant
    xArray = 0
    
    For Each xArray In myArray()
        MsgBox xArray
    Next xArray
    
    'myArray = add all CompID records from ClassesCompetencies where ClassID equals the value from the combo box.

     Me.Refresh

DoCmd.SetWarnings True

End Function

But when I try and run the loop to make sure it's getting data from my array, I get this:

Run-time error '92':

For loop not initialised

Now I'm fairly certain that I have created both my Array and my Loop in a really bad way! No matter what I have tried, I've not been able to get it working correctly.

So... any ideas?

 

[Guus2005]

If you post a sample database i can give you a better answer.

I will try:
I believe that the solution can be found in joining tables with a one to many relationship or even select two tables without a relationship to provide a cartesian product.

A cartesian product will give you all possible combinations of the two (or more) tables.

But to answer your question you can't fill an array using a SQL statement without using a recordset. And you have to use

For lngCounter = LBOUND(Array) to UBOUND(Array)
    msgbox Array(lngCounter)
Next lngCounter

The SQL solution is probably faster.

HTH

 

[dickohead]

Thanks for the reply mate.

I've since discovered that Array's may not be my answer and I should perhaps be using a collection?

I understand why the loop was giving me that error, I was using a "for each next" loop on an array that didn't support it... hence the error!

So now...

How do I select items from a table and put them into my collection?

My thinking was this, but it's not working (I also changed my function to a sub procedure as it's not returning anything now, it's inserting data)

Sub addComps()

DoCmd.SetWarnings False
    
    Dim strClassID As String
    Dim strPicNo As String
    Dim totalComps As Integer
    Dim myClassCompetencies As Collection
    Dim intCounter As Integer
    
    ' assign two variables to our CompID and PicNo
    strClassID = Me.ClassListCombo.Value
    strPicNo = Me.PicNo.Value
    
    ' count the total number of competencies for the chosen class
    totalComps = DoCmd.RunSQL("SELECT COUNT [CompID] FROM ClassCompetencies WHERE [ClassID] =" & strClassID & "")
    
    ' add items from the table to the collection
    For intCounter = 0 To totalComps
    myClassCompetencies.Add (DoCmd.RunSQL("[b]?????[/b]"))
    Next

    ' add items from my collection to the table
    For intCounter = 0 to myClassCompetencies.Count
    'some code that will porbably be based on the above loop
    Next
    Me.Refresh

DoCmd.SetWarnings True

End Sub

So this is much cleaner, but still not functional!

I have bolded some ?????'s becuase I have no idea how to select each item from the table and then add them to my collection (and the same in reverse)

 

[Guus2005]

First loose the

DoCmd.SetWarnings xxx

they do nothing in this case.
Second, why do you want to fill ans array or collection with the contents of a table?
Why not use a recordset?

dim rst as recordset

set rst = currentdb.querydefs("qrySomeQueryOrTable")

'Loop through the recordset
do while not rst.eof
   debug.print rst.fields(0)
loop

You could stop here and use the recordset or use it to fill the collection you mentioned.

HTH