CODEWITHSUNDEEP
sqlms-accessrandomvbams-access-2007
Muhnamana
Muhnamana

Reputation: 1044

Selecting Random Record and Marking Record As Being Used

So, the following selects a random team to be used. Once selected, I mark this team being as used as an X in the Used field with a update query later on. For the most it works, but after running this a handful of times, I start to get duplicated teams, even with them being marked as X.

What else am I missing to accomplish this?

Here is the SQL statement:

SELECT TOP 1 RandomTeams.[Team Name], RandomTeams.Used
FROM RandomTeams
WHERE (((RandomTeams.Used) Is Null))
ORDER BY Rnd(TeamID);

Here's how I'm handling the updates to mark a team as being used, which is working as expected, I have no issues here when marking with an X:

Text214.Text contains the team name that is being used

strTeam = (Chr(34) + Text214.Text + (Chr(34)))

strSQLUpdateTeams = "UPDATE RandomTeams SET Used = 'X' WHERE [Team Name] = " & strTeam

DoCmd.SetWarnings (WarningsOff)
DoCmd.RunSQL strSQLUpdateTeams

Upvotes: 0

Views: 88

Answers (1)

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

As a test, how about just throwing the following code into a module, then execute it and see what happens. BTW, how are you resetting [Used]? 

    Sub Test_Teams()
    Dim strSQL      As String
    Dim strTeam     As String
    Dim strSQLUpdateTeams   As String
    Dim dbs         As DAO.Database
    Dim rs          As DAO.recordSet

    Set dbs = CurrentDb
    If MsgBox("Do you want to reset all 'Used' flags?", vbYesNo, "Reset?") = vbYes Then
        strSQL = "update RandomTeams set [Used] = null;"
        dbs.Execute strSQL

    End If
MyLoop:
    strSQL = "SELECT TOP 1 RandomTeams.[Team Name], RandomTeams.Used " & _
            "FROM RandomTeams " & _
            "WHERE (((RandomTeams.Used) Is Null)) " & _
            "ORDER BY Rnd(TeamID);"

    Set rs = dbs.OpenRecordset(strSQL)
    If Not rs.EOF Then
        strTeam = rs![Team Name]
        Debug.Print "Found Team: " & strTeam
    Else
        MsgBox "EOF! No more teams."
        rs.Close
        Set rs = Nothing
        dbs.Close
        Set dbs = Nothing
        Exit Sub
    End If
    strTeam = (Chr(34) + rs![Team Name] + (Chr(34)))
    rs.Close
    Set rs = Nothing

    strSQLUpdateTeams = "UPDATE RandomTeams SET [Used] = 'X' WHERE [Team Name] = " & strTeam
    Debug.Print strSQLUpdateTeams
    'DoCmd.SetWarnings (WarningsOff)
    'DoCmd.RunSQL strSQLUpdateTeams
    dbs.Execute strSQLUpdateTeams
    If dbs.RecordsAffected <> 1 Then
        MsgBox "Whoa! Not good! Update failed!"
    End If
    GoTo MyLoop


End Sub

Upvotes: 2

Related Questions