Access VBA Grid Setup

Posted on 2007-10-01
Last Modified: 2013-11-28

I need to be able to allow a user to enter in a number of columns and rows in a database form, and then have VBA create a grid, or table or array of text boxes to match these numbers? Does anyone know how to do this?

Question by:andyb7901
    LVL 19

    Expert Comment

    what do you intend to do with the data the user enters? is it to be saved?
    one way to do this is to have a "dummy" table, ie data you delete after time the user enters ("defines") this grid, and then redefine an existing query (specifically for this grid) with the required columns. have a subform based on this query and refresh your subform on whatever parent form.
    LVL 44

    Expert Comment

    by:Leigh Purvis
    Can you describe your overall goal?
    (i.e. not only the requirements - but the purpose?)

    You want the user to be able to input two values - one for a number of columns - one for a number of rows, and then have that spontaneously as a grid of textbox controls to match those dimensions?
    Unlike VB (where you've also included this question - so we need to make sure on this) Access can't necessarily create controls at runtime (design view is required - and an MDE would negate that possibility).
    Plus it's not a good solution anyway.
    Personally - I wouldn't attempt that with a grid - unless you can specify an absolute maximum number of "cells" at design time.
    (For simple example suppose the maximum was 100 and the most the user could enter was 10x10).

    A continuous form with a single row of controls would be easier to generate, however bear in mind that you'd lose total distinct formatting control on a "per cell" basis.  (CF can help you to an extent with that - but it's not as flexible or fast as it might be).

    So - we turn to your requirements again.
    What's the ultimate goal?  To store values entered in this grid?  Where?  Why?
    LVL 44

    Expert Comment

    by:Leigh Purvis
    FWIW the query method mentioned by frankytee while I was typing is possible (if you're OK with the raw query binding - many aren't -  though it's likely less critical in a situation such as this).
    You'd lose pretty much *all* formatting options then of course - but if that's not an issue then that's OK.

    However (depending on the version of Access) you should be able to pull the "grid" off without creating *any* temporary data objects in your application - and doing everything in memory.

    Author Comment

    I basically want to create a tool, with a set of questions along the top, and then down the side a list of possible outcomes. The user will then place a yes or No, or a tick or something ion the fields, where they feel the outcome is suitable to the question. I want the user to be able to set up this form on their own. So eneter in the number of questions and outcomes. Using VBA or something I would then like the system to create a grid with the questions and outcomes listed.
    LVL 19

    Expert Comment

    have you tried my suggestion?

    Author Comment

    So how would I base a grid on a table? Im not too sure which grid even to use?
    LVL 19

    Expert Comment

    no grid involved. use a subform set to "datasheet" view. that way it gives the appearance of a grid. since its bound to a table the data is persistent and you can then do whatever (save to a "permanent" table etc)

    Author Comment

    I have tried to use the following VBA to create a table on the fly but its not doing anything? It doesnt cause any errors, but doesnt create the table either?

    Private Sub TableSetup()

    On Error GoTo TableErrCatcher
    Dim cat1 As ADOX.Catalog
    Dim tbl1 As ADOX.Table
    Dim str1

    'Reference objects for table
    Set cat1 = New Catalog
    cat1.ActiveConnection = CurrentProject.Connection
    Set tbl1 = New Table

    'Name table and append columns
    With tbl1

        .Name = "tbl_Audit"
        .Columns.Append "1", adInteger
        .Columns.Append "2", adDate, 10
        .Columns.Append "3", adLongVarWChar, 4
        .Columns.Append "4", adVarWChar, 30
        .Columns.Append "5", adDouble
        .Columns.Append "6", adVarChar

    End With

    'Append new table to Tables collection
    'and free catalog resource
    cat1.Tables.Append tbl1
    Set cat1 = Nothing

        If Err.Number = -2147217857 Then
            MsgBox "Error", vbInformation, "Cash Flow"
        End If
    Debug.Print Err.Number, Err.Description
    End Sub
    LVL 44

    Expert Comment

    by:Leigh Purvis
    To my mind though - the grid/table is just the intermediatory.
    A means of collection a user's choices through the UI.

    If you create a temporary table - then it should be just that, temporary - which you then iterate through and assign the selections into your real table structure data.

    The temp table is likely the simplest method - if you don't need to respond to any events or provide formatting.
    Equally the in memory solution needn't take much implementing at all.
    But either way - your work is only partially done.  You need to load and persist that data in a normalized fashion.

    Author Comment

    Do you know how I would create a table through VBA? I have tried using my above code but it does not do anything? No errors either?
    LVL 44

    Expert Comment

    by:Leigh Purvis
    I'd imagine your error handling is causing the lack of reported errors.
    While I wouldn't personally rely on ADOX to add the table - it'll do it.
    Just make sure your field definitions are appropriate to a Jet db.
    i.e. use adVarWChar and not adVarChar as you do in your final column.
    LVL 19

    Accepted Solution

    in an Acces env use DAO instead of ADO, eg below to create a table
    some of the data types you gave were not valid.
    if you have a limit on the number of columns (and their dataype is fixed) you dont need to dynamically create a table. just delete * from the table and repopuate. redefine a query to the user input. but since you are creating the table then you dont need that query.

    Function fnCreateTable(iRows As Integer, iCols As Integer) As Boolean

        Dim d As DAO.Database, t As DAO.TableDef, f As DAO.Field
        Dim sTable As String
        Dim i As Integer, s As String
        sTable = "tbl_Audit"
        Set d = CurrentDb
        On Error Resume Next
        DoCmd.DeleteObject acTable, sTable
        On Error GoTo errH
        Set t = d.CreateTableDef(sTable)
        i = 1
        Do Until i > iCols
            s = i
            Select Case i
                Case 1
                    Set f = t.CreateField(s, adInteger)
                Case 2
                    Set f = t.CreateField(s, adDate, 10)
                Case 3
                    Set f = t.CreateField(s, dbText, 4)
                Case 4
                    Set f = t.CreateField(s, dbText, 30)
                Case 5
                    Set f = t.CreateField(s, adDouble)
                Case 6
                    Set f = t.CreateField(s, dbText, 255)
                Case Else
                    Set f = t.CreateField(s, dbText, 50) 'default to text field, 50 characters
            End Select
            t.Fields.Append f
            i = i + 1
        d.TableDefs.Append t
        'now insert dummy rows to "build up the grid"
        For i = 1 To iRows
            s = "insert into " & sTable & " (1) select NULL "
            d.Execute s
        MsgBox "table created", vbExclamation
        Set d = Nothing

        Exit Function
        MsgBox Err & " " & Err.Description
        Resume comExit
    End Function
    LVL 19

    Expert Comment

    i'm off now so good luck. let us know how it goes.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now