Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    Quote Originally Posted by Gicu View Post
    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?

  2. #17
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    I would love to. How do I do that?

  3. #18
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    Here goes. I get a Access Forums message that download failed.

  4. #19
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    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.

  5. #20
    madpiet is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    122
    Quote Originally Posted by JohnLouisWood View Post
    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.

  6. #21
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you need more instructions for db posting:

    Click image for larger version. 

Name:	attach.png 
Views:	23 
Size:	45.4 KB 
ID:	49937

  7. #22
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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

    Code:
    .Fields.Append .CreateField("FallY", dbText)
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #23
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    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?

  9. #24
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    Is your 'answer' to create fields as Text, and then after I have my new tables, change the data to numbers?

  10. #25
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #26
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    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

  12. #27
    madpiet is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    122
    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.

  13. #28
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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.
    Code:
    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,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #29
    JohnLouisWood is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    52
    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.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-17-2018, 08:07 AM
  2. Replies: 2
    Last Post: 10-29-2015, 03:31 PM
  3. Issue creating a table in code
    By Newby in forum Access
    Replies: 5
    Last Post: 12-27-2012, 03:00 PM
  4. Replies: 3
    Last Post: 08-16-2012, 11:16 AM
  5. Creating Macro from Module
    By Harley Guy in forum Modules
    Replies: 1
    Last Post: 11-08-2010, 07:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums