Access VBA Grid Setup

Posted on 2007-10-01
Medium Priority
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
  • 5
  • 4
  • 4
LVL 19

Expert Comment

ID: 19989422
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
ID: 19989438
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
ID: 19989450
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 19989597
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

ID: 19989663
have you tried my suggestion?

Author Comment

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

Expert Comment

ID: 19989690
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

ID: 19989724
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
ID: 19989740
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

ID: 19989759
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
ID: 19989798
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

frankytee earned 2000 total points
ID: 19989952
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

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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

569 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