Issue with Collection in Access VBA (1 Viewer)

[mcomp72]

I am trying to use a collection in my code, and I'm seeing what appears to be some weird behavior which I can't explain. Essentially, I am adding values to a collection, and sometimes, the next line of code changes the first listing of the collection.

It's hard to explain in words, so I recorded a video (2 minutes) that shows what is happening.

I would be very curious if anyone has any explanation for what is going on. Right now, the rest of my code won't work because the first value in the collection is blank.

Here's the code seen in the video:

SQLstr = "SELECT * FROM qScores WHERE MemberID = " & MemberID & " ORDER BY TournamentDate DESC"

Set rstSCORES = db.OpenRecordset(SQLstr, dbOpenSnapshot)

If rstSCORES.RecordCount > 0 Then

    rstSCORES.MoveLast
    rstSCORES.MoveFirst
    
    'This will loop through the recordset and record the scores in the collection.
    Do While TheScores.Count < 10 And Not rstSCORES.EOF
    
        TheScores.Add rstSCORES!Points
        
        rstSCORES.MoveNext
    
    Loop
    
End If

 

[Edgar_]

How does your data look? You are traversing a recordset that appears to start at 9, then 8, 5, 5?, 10?

Check your Points field in your rstSCORES recordset, those numbers should be there. The sixth? record appears to be null. Is that the problem?

Do this and see your immediate window to know what the Points field is outputting.

    rstSCORES.MoveLast
    rstSCORES.MoveFirst
    
    'This will loop through the recordset and record the scores in the collection.
    Do While Not rstSCORES.EOF
    
        Debug.Print rstSCORES!Points
        
        rstSCORES.MoveNext
    
    Loop

 

[cheekybuddha]

Where is TheScores declared and set?

 

[Mike Krailo]

You should have clicked on the small plus symbol (to expand) in the watch list to see all of the collection.

The recordset is keying off of a MemberID and not the values in the displayed list from your sign-in-sheet form. That subform displayed at the start of the video doesn't make sense if it is a sign in sheet, that would presumably be a single user and your displaying multiple users in the subform. Just trying to understand the overall concept of what you are doing. Record Scores button would imply, the one MemberID is about to enter in theirs scores for the game/match. If that is the case, why are you opening up a recordset of values already there and then adding to a collection??? It always helps when we all know what your trying to achieve.

If the recordset is for one single memberID, why is the data on the form shown in the video showing three different names each with one single point score? It's all very confusing. Maybe show us the qScores table/query first.

I assume the player already entered their scores into qScores already (is this a query?) since this shows you opening up a recordset and then attempting to populate the collection. Unfortunately we do not see your data. Maybe if you upload the database after zipping it up, some of us can try and troubleshoot further.

 

[ebs17]

TheScores.Add rstSCORES!Points.Value

The collection can and will take references unless you explicitly use the Value property of the recordset field.

 

[Mike Krailo]

He does have a variable called Points dimensioned as a Long. It must be used further down in the code.

The form for entering the score data should verify the contents of the Points field using the forms Before_Update event. Ensure the value is a number and not null and also disallow negative values. This should prevent any null values from sneaking in there once you do get it all working. We're all just waiting for your feedback.

 

[MarkK]

TheScores.Add rstSCORES!Points

As ebs17 points out, the line of code above adds an ADODB.Field object to the collection. You could do...

    sql = "SELECT TOP 10 * FROM qScores WHERE MemberID = " & MemberID & " ORDER BY TournamentDate DESC"

    With db.OpenRecordset(sql, dbOpenSnapshot)
        Do While Not .EOF
            TheScores.Add !Points.Value
            .MoveNext
        Loop
        .Close
    End With

 

[mcomp72]

TheScores.Add rstSCORES!Points.Value

The collection can and will take references unless you explicitly use the Value property of the recordset field.

Thank you! That seemed to fix the problem!

 

[Mike Krailo]

Any chance you can share the reason for putting the scores into a collection instead of directly into a table of scores?

 

[Pat Hartman]

@mcomp72 I'm glad you found the problem but my question is, why are you loading a recordset into a collection in the first place? Surely, it would be more efficient to simply use the recordset directly rather than adding the middle step of saving the data in a different place where you can process it from later.


Maybe you are just trying to understand how to use collections and this is just a meaningless test. If so, just ignore me?

 

[mcomp72]

A little background on what I am building:

I know how to write VBA in Excel, so I created a spreadsheet system for my Dad, to track the golf scores for an amateur seniors group he plays with each week. For each game, he enters a couple of different things on what he calls the Sign-in sheet. First, he marks which people show up and pay their money to play. Then, after the game, he types in everyone's scores. The spreadsheet then has code that writes the scores to individual Excel sheets -- there is one for each player.

The Excel file works well, but he keeps asking me to add features, and it's to the point where I know it would be much better if it was a database system instead of a spreadsheet system. So I'm trying to recreate it, as much as I can, in Access. I want to make it look and feel as close to the Excel version as possible, because my Dad is 76 and learning a new system would be a little difficult.

In terms of why I am using a collection:

Once my Dad switches to this db, the scores will be recorded on the SCORES table. However, I will need to import some of the previous scores from the Excel file into a table called OLDSCORES. The reason is, they take the last 10 games a player has played and calculates their average score to generate what they call the person's "Points Needed". (I don't play golf so I can't fully explain it, but my understanding is, this is some kind of handicap system so the really good golfers don't always "win".) So I decided to read in a player's scores from the SCORES table into the collection, and then if there isn't 10 scores in it, it will read in more from the OLDSCORES table. (One the collection has 10 scores in it, it will stop.) The code will then add up the scores in the collection and divide by the number of scores to get the average.

I have no idea if using a collection is the "best" way to do it, but it was the first idea what came to mind, and so I figured I'd go with it.

 

[ebs17]

@mcomp72: Your task can certainly be solved better and more compactly with a query - although I haven't fully understood the task yet.

Advantage of a collection (alternatively also dictionary):
Both objects are very slim and are already in the main memory. Access is direct and super fast when you need to look up a value (item) associated with a value (key).

So if you have to look up individual values in a larger amount of data in a loop, you will achieve performance advantages compared to designs such as DLookup, individual query/recordset with individual filter, FindFirst in the recordset, etc.
This means that the additional generation of a collection can be worthwhile.
So the keyword would be: direct access instead of searching and filtering.

 

[cheekybuddha]

SELECT TOP 10
  MemberID,
  Points
FROM (
  SELECT
    MemberID,
    Points
  FROM qScores
  WHERE MemberID = [Enter MemberID]
  ORDER BY
    TournamentDate DESC
  UNION
  SELECT
    MemberID,
    Points
  FROM OldScores
  WHERE MemberID = [Enter MemberID]
  ORDER BY
    TheDate DESC 
)

 

[cheekybuddha]

Why have a separate table for OldScores?

Really you just need some sort of flag in the Scores table to indicate that they're old.

Perhaps one already exists like tournament date?

 

[ebs17]

adds an ADODB.Field object to the collection

Just for completeness: A DAO recordset is used here.

//Edit:
OldScores query, qSores is a query?
In fact, I would look at the definitions of these two queries and see what they do.

I will need to import some of the previous scores from the Excel file into a table called OLDSCORES.

It would be logical to import the data completely and transfer it to the database.
It will probably be easy and direct if you get the 10 most recent values directly from a table of scores. I would only add filtering to member if it is explicitly needed (queries are good for bulk data processing).

Approximately ...

SELECT
   S.MemberID,
   AVG(S.Score) AS ScoreAvg
FROM
   tblScores AS S
WHERE
   S.ID IN
      (
         SELECT TOP 10
            X.ID
         FROM
            tblScores AS X
         WHERE
            X.MemberID = S.MemberID
         ORDER BY
            X.TournamentDate DESC
      )
GROUP BY
   S.MemberID

 

[Pat Hartman]

I didn't mean to reopen a closed thread. I was just pointing out that there might be a way that uses queries directly.

I agree that Old scores should probably not be a separate table. But, if for some reason it needs to be, then @cheekybuddha 's suggestion of a Union solves the problem.

I don't know how to calculate handicaps either but if they need precisely 10 scores, the easiest solution would be to do a totals query (of the union query, if necessary) that counts the number of scores for each player and then selects only players with 10 or more scores for the handicap calculation query/code

Also, keep in mind that the Top predicate can return more rows than you selected if there are duplicates.

 

[mcomp72]

I am still pretty new to databases, so I have no doubt the way I came up with to handle this issue is probably not the most efficient.

The problem I foresaw with importing the existing scores into the SCORES table is that it has a couple of foreign keys. And one of the tables it relates to also has a foreign key to yet another table. So I was trying to forgo having to deal with potentially creating new records in those other tables. So I thought a table of old scores that didn't relate to other tables (except the MEMBERS table) would be easiest. At least easiest for me to wrap my head around.

I am still learning about SQL, so I didn't know about the UNION thing. That seems like a good idea. I will see if I can adapt the code that @cheekybuddha posted to work for me.

Thanks everyone!

 

[Pat Hartman]

When you import data from an old database, you can keep the PK's and therefore the FK's won't break. Just include the PK in the append query. However, if you already have data in the table, the old PK's can't conflict with any of the new records. So, at this point, you might need to start with the referenced tables and get new PKs for them and then update the FK's in the old scores so you can append the oldscores and allow Access to generate new PK's.

Keep in mind - this is the ONLY method that allows you to set an autonumber PK yourself. In all other conditions, you cannot change or add a PK. So on a form for example, you cannot type in a value for the PK - EVER but the append query works and it works specifically to facilitate conversions.

 

[Minty]

It sounds like they are using a system similar to the World Handicap System, which has been adopted worldwide by most of the golfing world.

In that system, in simple terms, your handicap is calculated by taking an average of the best 8 Net Scores from the last 20 recorded scores in your scoring history.
It's complicated by different stroke allowances on different courses, depending on the course difficulty, but you probably don't need to worry about that.

If that's how it works then a single table and suitable queries should suffice.