Creating a dynamic architecture driven by a query or SQL string

[SlimJimPoisson]

I have an Access application with primarily one table. I am always creating new queries to look at my data. Not only do these queries return different records they also present different columns, in different orders. All of these queries will include the unique ID field so that common operations can be performed on them, but the formats and filters are completely different.

I envision an architecture where there is really one main form that acts as a control wrapper around a continuous subform. The wrapper has the buttons and functions (such as right-click menu) that all me to filter and massage the records being currently displayed. I have already completed most of that.

One of the wrapper's features would be to allow the user to choose the datasheet being presented in the subform. When choosing a new view it will either use an existing query or its equivalent SQL to generate the dataset being displayed.

I have this working more or less, but it is not dynamic enough. It's easy to filter and sort the data, but I cannot determine how to rearrange the columns since that order seems to be dictated by the subform that uses the query.

So, I want to do this and avoid having to create an associated subform every time I create a new query. I already have the code to find the queries and pull their SQL, I just need to know how hard it will be to have the one main form and one subform perform the magic needed to satisfy my need which includes rearranging the columns.

I have some ideas on how I would approach this. I was pretty successful foregoing the form and subform altogether and just hijacking the RightClick popup CommandBar so that you can perform operations directly on a query's datasheet, but I ran into some bizarre problems (another question for another thread).

However, before I went any further I wanted to get a sanity check on my understanding and vision. Outside of my introduction this is my first post. I hope I don't come off as the addled old professor you find wandering the chemistry building late at night looking around and muttering to himself. It is very possible that I missed something gigantic in my confusion. Like I'm in the wrong building. Or it's Sunday.

 

[jdraw]

Welcome.

You may get some ideas from this material from Martin Green. I realize he is working with dynamic reports, but its the approach and examples that may provide some insight for you.

Good luck.

 

[SlimJimPoisson]

Thanks for the response and ideas.

There are some great ideas there, but it appears to be focused on the UI for customizing the report. That part is unnecessary for me, it's the process of effecting the customizations on the target subform (i.e. which fields are included and what order they are in). In the referenced page those appeared to be limited to adding filters and sort orders which is not an issue for me.

And you are correct that for the moment I am fixated on using query/datasheet. I will admit that reports are one area of Access that I have not explored, but I doubt that they would work for me because I failed to mention that the ability to select multiple rows of the datasheet and take action using a rightclick, button, or hotkey on those selections (based on the SelHeight and SelTop properties) is essential to my vision (and something I already have working).

 

[jdraw]

It sounds like you may be dealing with a form and subform control(s) and depending on what you want to happen, you might use some event to extensively adjust the subform control- its size, the subform involved, the subform recordsource etc etc.
I haven't done this at the level/complexity I'm seeing in your post, but there may be others with detailed knowledge/examples.

I did a little googling and found a few links that may be useful (or not, but should give some info):
https://stackoverflow.com/questions/37676795/possible-to-change-ms-access-subform-fields-through-vba
http://www.fmsinc.com/microsoftaccess/forms/subform/master-link-fields.asp
https://bytes.com/topic/access/answers/842235-dynamically-change-record-source-control-source-form
https://btabdevelopment.com/easy-way-to-remember-how-to-refer-to-subforms/
https://answers.microsoft.com/en-us...ceobject/3aefc06d-b0ca-4082-88e6-420d24ece134

Interesting project, good luck.

 

[Pat Hartman]

It is poor practice to expose users to queries or tables directly. Forms should be used so you can control data actions and prevent the data from being modified incorrectly.

Rather than trying to build Access forms on the fly, I would create queries based on the selection criteria and export them to Excel where the user can rearrange the columns and do further drill down.

If you can live with a consistantly formatted subform, you can provide a pretty extensive search facility and open a form to display the data and manipulate it as necessary, even update it if you care to put validation code in the form's beforeUpdate event. Or, give the option of exporting the data to Excel.

 

[SlimJimPoisson]

If anyone is interested I was extremely successful with my "ubiquitous subform" approach and there is no question that I will start using this as my default design for any situation in which there is a need to present a datasheet view in a subform.

Once created, the Ubiquitous Subform eliminates the need for any other datasheet subforms and it can be copied and pasted unchanged into any other application and used as needed by all the forms. This eliminates the need to create or change any associated subform whenever the underlying query or data requirements are changed as the query itself drives everything. The updates are dynamic so the source query can be changed without closing the main form allowing a design where the user selects the source query from a drop-down menu or any other methods as desired.

The expectation is that the main form would act as a wrapper and would contain controls to allow the user to select the source query, search or filter the records displayed in the subform, and provide buttons to take action on the currently displayed dataset or on selected records. I have added hotkeys though the keyboard events in the subform which perform actions on selected records. I also have a key that increases or decreases the font size of the datasheet while also automatically resizing the columns to fit. Finally, I added a key to move to the next or previous query allow people to switch views quickly which has really helped with productivity.

In the end I was surprised that it turned out to so simple. I will describe the approach here pared down to the simplest steps required to make it work.

First, create a blank form. This will become the Ubiquitous Subform. Change the default view to "Datasheet". Create a new text box field and leave it unbound, but delete the label. Then select the field and copy it to the clipboard. Next paste it into the form as many times as you need to cover the maximum number of columns in your biggest query. I created 100 unbound fields. You can resize or reformat the form but it makes no difference. Save and close the form and give it whatever name you like.

Next, create another blank form. This is your main "wrapper" form. Add your subform and format as desired. You might turn off the navigation buttons. Give the subform object a name, in my code below I use fSubform. Now in the Form_Load() event code add a call to RefreshForm passing the name of the query that should be used as a source. Of course it's not necessary to use a function you could strip the code and put it directly in the Load event code. You can also call the RefreshForm function from anywhere at any time and the view will be updated. Save the wrapper form and give it a good name ("Harold" is a good name).

Here is the RefreshForm code. I apologize that it is not elegant so feel free to improve it.

Public Sub RefreshForm(QueryName As String)

    Dim Subform As Form
    Dim QueryDesign As QueryDef
    Dim CurrentItem As Control
    Dim I As Integer
    
    'Get the Query Design
    Call CurrentDb().QueryDefs.Refresh
    Set QueryDesign = CurrentDb().QueryDefs(QueryName)
    
    'Load the Query Design into the Subform
    Set Subform = Me.fSubform.Form
    Subform.RecordSource = QueryName
    
    'OPTIONAL: pull the font information from the query design (I omitted italics and underline)
    Subform.DatasheetFontName = QueryDesign.Properties!DatasheetFontName
    Subform.DatasheetFontHeight = QueryDesign.Properties!DatasheetFontHeight
    Subform.DatasheetFontWeight = QueryDesign.Properties!DatasheetFontWeight
    
    'Load the Queries fields into the subform's controls
    For I = 0 To QueryDesign.Fields.Count - 1
        Set CurrentItem = Subform.Controls.Item(I)
        CurrentItem.ControlSource = QueryDesign.Fields(I).Name
        CurrentItem.Properties!DatasheetCaption.Value = QueryDesign.Fields(I).Name
        Subform.Controls.Item(I).ColumnOrder = I + 1        'Save order as in the query
        Subform.Controls.Item(I).ColumnHidden = False        'Necessary for dynamically changing queries
        Subform.Controls.Item(I).ColumnWidth = -2            'OPTIONAL: Autofit the column width
        
        'OPTIONAL: Set the column alignment as desired
        Select Case QueryDesign.Fields(I).Type
            Case dbBoolean, dbByte, dbChar, dbDate, dbGUID, dbMemo, dbText, dbTime, dbTimeStamp
                CurrentItem.TextAlign = 1
            Case Else
                CurrentItem.TextAlign = 3
        End Select
    Next I

    'Hide the rest of the fields which are not needed
    For I = QueryDesign.Fields.Count To Subform.Controls.Count - 1
        Subform.Controls.Item(I).ColumnHidden = True
    Next I
    
End Sub

That's it, you're done. I hope it's useful to you.

 

[Uncle Gizmo]

That's it, you're done. I hope it's useful to you.

Due to a bug in the forum software this message was "unapproved" (hidden) for some considerable time. I have just approved it. I hope no one has been inconvenience too much! The new forum software no longer has this bug, so this problem should not reoccur.