Creating a Table using VBA code in a module

[JohnLouisWood]

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

[CJ_London]

looks like you are not using Option Explicit otherwise you would be getting more errors

See this link for how to add fields to tables
https://learn.microsoft.com/en-us/of...def-method-dao

[JohnLouisWood]

I added Option Explicit, and then I get an error that db is a variable not defined. So, when I take the quote/comment off Dim db as Database I get the error user-defined type not defined

[Welshgasman]

Qualify whether DAO or ADO

[JohnLouisWood]

Sorry, I do not know what you are asking. What is DAO and what is ADO?

[JohnLouisWood]

I am working 'locally'. I am the only one using the database, and it is saved on my laptop. I tried putting Dim db As ADO.Database and I tried Dim db As DAO.Database. Same result as before - I get the error user-defined type not defined

[Welshgasman]

Look to see if you have any.missing references.

[CJ_London]

Did you read the link I provided in post #2

[Gicu]

Sorry, but I am not clear why do you think you need to create a table in code every single time? Why not just create it once and empty\fill it as needed?

To empty it you would simply run a stored delete query or CurrentDb.Execute "DELETE * FROM tblYourTable;", dbFailOnError.

Cheers,

[JohnLouisWood]

I looked at it. And, there were 2 different codes that 'start' with Dim db As Database. But when I try that I get an error.

[JohnLouisWood]

Each table would have the selected info for that number of consecutive winning seasons, and consequently the queries and (sub)reports. I imagine that I will end up with a report with several sub-reports included - 25+ win seasons, 20+ win seasons, 15+ win seasons, 10+ win seasons, and probably a 5- win seasons. Later I will create similar for the Reserve/JV and for the Freshmen.

[JohnLouisWood]

What references? Where would I look? This only my second module, and the 1st is very similar - 15+ wins instead of parameter/user-selected.

[Gicu]

I think you're getting the error because of your fields declaration:
Dim fld1, fld2, fld3, fld4 As Field 'only the last one is a Field, the rest are Variant

You should have them individidually declared and fully qualified
Dim fld1 as DAO.Field,fld2 as DAO.Field,......

But I think your design is wrong, you shouldn't add fields to the table depending on user input, you should add records....

Cheers,

[davegri]

Can you upload your DB? If we have a look at your main table that holds the 49 year win/loss data we can more easily focus on what needs to be done.

[madpiet]

There are so many things wrong with this question that I hardly know where to start. Why are you dropping the table at all? Why not just delete the contents? So there's not need to get a new table name from the user. You're just going to clutter up your database with what amount to temporary tables.
WinNum... an Integer has a maximum value of something like 32,000 Do you really need a larger number than that?
I'd just add a season or year to the table and then make that required.

All that code to populate a table? Can't you just create a form and let the user enter the data? Or prompt for a filename and import it using transfertext or something?
what are you entering? Something like (Opponent, Home/Away, OurPoints, OpponentPoints)?

Sounds to me like you're making this way more complicated than is necessary. By a LOT. You could do something as simple as ...

dim intReply as integer
intReply = Msgbox("Do you want to delete the contents of this table?", vbyesno + vbinformation)

if intReply = vbyes then
currentdb.execute "DELETE * FROM Mytable", dbfailonerror
msgbox "table truncated"
end if

then just do something like create a form to enter whatever data you want and maybe open it in Add mode, so you can't accidentally overwrite existing data. Problem solved. Nothing doing. And almost no code at all.