Form flickering with background image

[arnelgp]

it is better if you explain the end-goal of this draw.
will each draw be saved to a table and eliminate it
from future draws?

 

[Mike Krailo]

Arnel, I'm not sure about babycat, but that's exactly how I think it should work. That makes it easier to track the winners and leave them out of the data that is displayed using SQL string.

To get only names that are not recorded in the Win table for example.

SELECT PickupNames
FROM TBLNAMELIST
WHERE PickupNames Not In (SELECT Winner FROM Win);

The only problem I have run into so far is after several winners have been removed from the list in this way, the code to display the names gets more complicated because there will be gaps in the ID's due to being removed. I guess you have to store each one that gets removed in a tempvar and check for each of those ID's in the main button select code.

Here is the function I was working on and thought this was going to be fairly easy until I ran into what I'm describing above. I left my comments on it in there so you can see what I'm thinking.

Public Function GetRandomIDs(TotalNames As Long, StrSQL As String)
   ' Prior to running this funtion, other code will need to determine the TotalNames (not including previous winners) via an SQL recordset inquery.
   ' TotalNames as argument is all that is needed to calculate all ID's
   ' There is no direct return value but five new tempvars will be set.
   ' They will be used to populate an SQL query of the five display names for each unbound box use in LuckyDraw main form.
  
   ' The Win table is the key to showing the winners list and eliminating duplicate names being selected or displayed on the main form.
  
   ' The assumption with TBLNAMELIST is that the ID's are starting at 1 and there are no missing ID's anywhere. They are sequential.
   ' That is easy to do since we are in control of the import of the table of names.
  
   ' Darn! I just realized that it's not that simple. If winners are exclued from the query later, then this code will fail since it assumes a complete
   ' chain of unbroken ID's in sequential order. That is no longer the case after some winners are culled out of the record source.
  
   ' Hmmmmmm. Can we account for the gaps somehow by also tracking the ID's that were assigned as winners? Yes, that would work. So the answer is to
   ' reference the NLID FK that is stored in the winners table already inside this code and make the appropriate changes.
  
   ' So we need and SQL statement after all as an argument to the function, but it will be to scan for all ID's in the Win table.
  
   Dim Key1 As Long
   Key1 = rand Mod TotalNames + 1
  
   TempVars!Box1ID = Key1
  
   ' Here we do the special select case code to check for end of list wrap around and handle accordingly.
   If Key1 > (TotalNames - 4) Then
      Select Case Key1
         Case (TotalNames - 3)
            TempVars!Box1ID = Key1
            TempVars!Box2ID = (TotalNames - 2)
            TempVars!Box3ID = (TotalNames - 1)
            TempVars!Box4ID = TotalNames
            TempVars!Box5ID = 1
         Case (TotalNames - 2)
            TempVars!Box1ID = Key1
            TempVars!Box2ID = (TotalNames - 1)
            TempVars!Box3ID = TotalNames
            TempVars!Box4ID = 1
            TempVars!Box5ID = 2
         Case (TotalNames - 1)
            TempVars!Box1ID = Key1
            TempVars!Box2ID = TotalNames
            TempVars!Box3ID = 1
            TempVars!Box4ID = 2
            TempVars!Box5ID = 3
         Case TotalNames
            TempVars!Box1ID = Key1
            TempVars!Box2ID = 1
            TempVars!Box3ID = 2
            TempVars!Box4ID = 3
            TempVars!Box5ID = 4
         Case Else
            MsgBox "Unexpected Craziness Just Happened!"
      End Select
   Else
      ' No special case here, all other boxid's assigned in ascending order
      TempVars!Box1ID = Key1
      TempVars!Box2ID = Key1 + 1
      TempVars!Box3ID = Key1 + 2
      TempVars!Box4ID = Key1 + 3
      TempVars!Box5ID = Key1 + 4
   End If

End Function

 

[Babycat]

Hi Mike

Currrently, there is no need to remove the winner ID. I actually didn't think about that, but it would be great if we can consider it as an option.
let split the work into 3 parts:

1. Generate 5 uniqued ID (ID1 to ID5). They could be 5 random IDs, or one random ID and 4 ID are "nearby" in the list (like your idea in previous post). If previous winner needs to be removed, i will check in this steps and remove it if necessary

2. Map to TBLNAMELIST and extract strings of name
I think about using SQL and recordset:

SQL= "Select * from TBLNAMELIST where ID = ID1 or ID = ID2 or ID = ID3 or ID = ID4 or ID = ID5"
Set rs = dbs.OpenRecordset(SQL, dbOpenSnapshot)

3 . then using recordset method to fillup the boxes

i = 1
DO WHILE NOT rs.EOF
          box(i) =   rs!ID &  rs!StringName
          i = i +1
rs.MoveNext
LOOP

Do you think it works?

I dont have computer with Access right now, So this is just my idea, I have not tried on real.

 

[Mike Krailo]

That's an idea that will work. I'm injecting another requirement that you don't need. The version I'm working on does not modify the original table. There is an additional Win table that gets populated every time a winner is selected. I like that approach, just ran into the ID gaps problem described earlier.

My power has been out all morning so I cannot look at it now. I'll keep hacking away at it and post the result when I can.

 

[Mike Krailo]

I have a pretty decent solution using arrays to do all of the visual mixing on screen. I tried lot's of ways that didn't work very well. The one using the subquery took to long to calculate every time the select button was pressed using long lists. Using the arrays was the closest one to working the way I wanted but there was still something not quite right.

Then I came up with the idea of creating four more fields in the table L1, L2, L3, L4 (for Loser1, Loser2, etc..). Yes they are repeating fields and in this case it is a good thing. These fields get filled out by running the FillHat() subroutine after import with self join keys that point back to a different name (See the Hat table pic).

Here is the query HatQ and the source Hat table.

SELECT Hat.ID, Hat.PickkupNames, Loser1.PickkupNames, Loser2.PickkupNames, Loser3.PickkupNames, Loser4.PickkupNames
FROM (((Hat INNER JOIN Hat AS Loser1 ON Hat.L1 = Loser1.ID) INNER JOIN Hat AS Loser2 ON Hat.L2 = Loser2.ID) INNER JOIN Hat AS Loser3 ON Hat.L3 = Loser3.ID) INNER JOIN Hat AS Loser4 ON Hat.L4 = Loser4.ID
ORDER BY Hat.ID;

And the final data used to load into the main NameList array.

 

[Babycat]

Bravo.
I saw alot of your hard work. Much appreciated your efforts
Let me study your code.
(I really like your picture of angel )