Display result of dynamically generated SQL SELECT query in datasheet subform

Question

I am developing a form that contains a subform. Let's call them frmMain and frmSub for now. I want to display a list of records in frmSub in datasheet view that is generated by an SQL SELECT on one or more of my database tables.

Depending on the SQL statement used, I may have different columns that I will want to display in the frmSub.

I don't even know where to begin with this.

As I see it, I need to declare a DAO.Recordset that contains the records that I will display in frmSub, and set this Recordset as the RecordSource for frmSub. Somewhere in there I also need to tell frmSub which columns I need to display (define form controls in VBA?)

I want to do this when the form is loaded, so I will be putting the code into the Form_Open() subroutine, using the OpenArgs to determine the SQL and everything that follows.

I realize that the above is somewhat vague; this is because I will have a few forms like this in my front end, so I'm looking for a general approach that I will be able to reuse. Help with this would be greatly appreciated.

Answer 1

Create multiple subforms with different sets of controls. Then use code in the On Load event of frmMain to dictate the Source property of the subform control.