For background, I am working with a high school team's basketball stats - 49 seasons. I have worked with this for about 10 years, and I have gotten my reports onto a/my webpage. I recently thought it would be nice to talk about consecutive seasons with __ wins. I started working with 15+ win seasons. After 30+ queries (and making a couple of tables along the way), I thought there had to be a better way. I was able to create a module that 'easily' populates a table. I manually created/designed the table, and have to empty the table anytime before running the module again. Well, if this so successful, I should be able to have a parameter (user-input) for a different number of victories/wins. Great idea, but then I would need the module to create the table, so I can then populate the table. Here is my code: The problem is that a get a compiler error the line before the Debug Print #2.5
A later/second question is how do I delete the table the next time I run this module with the same 20+ wins (or empty the table and do not create).
Sub VarWinCons()
‘Dim db As Database
'Dim myTable As TableDef
Dim WinNum As Long
Dim NameNewTable As String
'Dim tbl As DAO.TableDef
'Dim fld1, fld2, fld3, fld4 As Field
WinNum = InputBox("How many wins are we working with?", "Consecutive Seasons of Wins")
Debug.Print "We are checking on seasons with " & WinNum & " wins"
NameNewTable = "Var_" & WinNum & "cws_info_mt"
Debug.Print "The name of the table that we are creating is: " & NameNewTable
Set db = CurrentDb
Set tbl = db.CreateTableDef(NameNewTable)
Debug.Print "We are here #2
tbl.Fields.Append
tbl.CreateField("cws_ID", dbInteger)
Debug.Print "We are here #2.5"
With tbl
Set fld1 = .CreateField("FallY", dbDouble)
Debug.Print "We are here #3"
.Fields.Append fld1