Creating a Table using VBA code in a module

[JohnLouisWood]

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,

My Dim ... Field is quote/commented


How can I enter a record to a table is I have not declared any fields?

[JohnLouisWood]

I would love to. How do I do that?

[JohnLouisWood]

Here goes. I get a Access Forums message that download failed.

[JohnLouisWood]

I think you missed a lot of the 'point'. The present database includes game scores/results from the 49 seasons. I have already queried this to get season results - win and loss record for each season.
WinNum will be an integer (whole number) less than 30, I would expect, although at this time I am not 'error checking'. How else should I have declared WinNum.
I am only asking the user to select the number of wins/cutoff. When I ran/made my first module (the problems I'm having are in module #2), the table that I had made/designed was then populated with 8 records. Because I had pre-selected the cutoff to be 15 wins, I was able to easily title my 'first' table. When I run my second module, I do NOT know the cutoff, and consequently need make/design a/the new table.
I expect to 'eventually' create A report that will include sub-reports for 15 wins, and probably several other categories like 20+ wins and 25+wins, each having its own sub-report.

[madpiet]

I think you missed a lot of the 'point'. The present database includes game scores/results from the 49 seasons. I have already queried this to get season results - win and loss record for each season.
WinNum will be an integer (whole number) less than 30, I would expect, although at this time I am not 'error checking'. How else should I have declared WinNum.
I am only asking the user to select the number of wins/cutoff. When I ran/made my first module (the problems I'm having are in module #2), the table that I had made/designed was then populated with 8 records. Because I had pre-selected the cutoff to be 15 wins, I was able to easily title my 'first' table. When I run my second module, I do NOT know the cutoff, and consequently need make/design a/the new table.
I expect to 'eventually' create A report that will include sub-reports for 15 wins, and probably several other categories like 20+ wins and 25+wins, each having its own sub-report.

What if you just create an unbound form where the user can set the filters (number of wins or whatever), then you can pass those selections as filter parameters to your report. Would that solve your problem? I'm wondering if the whole "write this to a table" is a huge red herring. If your end goal is to open a filtered report, you're doing it the hard way. One of the arguments when you open a report is a filter (like a query's WHERE clause minus the "WHERE" keyword), so you could just build the filter in your unbound form using VBA and then just pass that filter to your DoCmd.OpenReport "MyReport".... strFilter and you're done.

[davegri]

If you need more instructions for db posting:

[Gicu]

How do you expect to set a fld variable to a Field object if you don't declare it? CJ_London gave you the answer in post #2 but you didn't attempt to do it

.Fields.Append .CreateField("FallY", dbText)

Cheers,

[JohnLouisWood]

You are making me think that I should make several copies of my first module/table. That uses 4/5 fields (4 fields plus the ID field) with 8 records. In order to make my sub-report I have to make some queries. (a) 'add' calculated fields that change FALLY from 1984 to SeasonY 1984-85, (b) determine the total of 15+ win seasons, the MAX of consecutive win seasons, and how many times that happened.
I've started a Sub-report for the 15 win seasons using labels and textboxes; The Varsity has * 19 * 15+win seasons. The most consecutive 15+ win seasons was * 4 * , and this happened * 2 * times. (I've used *'s around textbox info that I would get from my query(s)). My other question/concern is how do I 'complete the next line/sentence?
The varsity had *4* consecutive 15+ win seasons from *1984-85* to *1987-88* and from *1998-99* to *2001-02*. The question/concern is how do I write my last sentence if this only happened 1 time, or 2 times (like this case) or maybe even 3 or more times?

[JohnLouisWood]

Is your 'answer' to create fields as Text, and then after I have my new tables, change the data to numbers?

[Gicu]

Not sure who "you" is, but you create the fields as needed, the idea is you do not use Set fld1=tdf.CreatedField because you did not declare the fld1 variable (using Option Explicit would flag all these errors).
So if you need to create 15 fields you would loop 15 times. But as I said, a screenshot of the table would help clear the picture...
Cheers,

[JohnLouisWood]

I removed Option Explicit, and most of this runs. When I try Dim fld1 As Field, I get the error user-defined type not defined.
When I comment out all the field work, I am now looking at: (a) Set tbl = db.CreateTableDef(NameNewTable) and later (b) Set rst3 = db.OpenRecordset(NameNewTable) I get the following error message: Microsoft Access database engine cannot find the input table or query "Var_20_cws_info_mt". Make sure it exists and its name is spelled correctly. Just after part (a) I have a Debug.Print NameNewTable, and it prints: Var_20_cws_info_mt

[madpiet]

Because I had pre-selected the cutoff to be 15 wins, I was able to easily title my 'first' table. When I run my second module, I do NOT know the cutoff, and consequently need make/design a/the new table.
I expect to 'eventually' create A report that will include sub-reports for 15 wins, and probably several other categories like 20+ wins and 25+wins, each having its own sub-report.

While you can create tables and all that in VBA to do this, I think you're overcomplicating a really simple question. Say you just keep all the games in a table and then use a query to determine which team won/lost. Then you can just create a simple query based on that, and then use that as the source of your report. Finally, you can create an unbound form that the user can enter the filter values into and then click a button to run your report. Requires like two lines of code. I'd keep it simple - much easier to understand (six months from now, for example) and easier to modify. I'm not against writing VBA, it's just that I don't think doing this all in VBA is a great idea, nor necessary. But it's your database. Have at it.

[Gicu]

The whole idea with Option Explicit is to help you identify the missing variables; removing it doesn't make the code run any better, you will still bang your head if you run with your eyes closed .
Please try this (copy into a new module and compile it), it should create a table with the selected number of fields (which is what I think you want, but you never posted a screenshot). I did not know the names so I used FIeld1,Field2, etc.

Option Compare DatabaseOption Explicit


Sub VarWinCons()
Dim db As DAO.Database
Dim WinNum As Long
Dim NameNewTable As String
Dim tbl As DAO.TableDef
Dim iCount As Integer


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"


With tbl
    .Fields.Append .CreateField("cws_ID", dbInteger)
    Debug.Print "We are here #2.5"
    For iCount = 1 To WinNum
        .Fields.Append .CreateField("Field" & iCount, dbDouble) 'we don't have variables for field
    Next iCount
End With
Debug.Print "We are here #3"
'now append the new table to db
db.TableDefs.Append tbl
Set tbl = Nothing
Set db = Nothing
Application.RefreshDatabaseWindow
End Sub

Cheers,

[JohnLouisWood]

I got a compile error: User-defined type not defined.
This is the statement that created the error. Dim db As DAO.Database
This is the same error that I have consistently gotten when using Option Explicit.
I have been able to run my module IF I do NOT try to create the table in the module, but create the table before running the module. It works, but it limits my choice(s) of which user input can be used.