Create Subform through Code? 1

[dcroe05]

I've created an access form to help co-workers clean and standardize data. By selectind a table and a field from listboxes a subform populates with the data selected and the user can perform various function with the data.

Here's the problem: I made it for users to be able to drop the form into whatever database they're using, but the users keep forgetting the import the form AND THE SUBFORM--which renders the form useless.

Here's the question: Is there anyway to create a subform through code so that I don't need a hard subform. I know I could do it using a listbox, but the the records wouldn't be directly editable.

 

[Remou]

Can you use the header of a continuous form your selections and the detail section for records?

 

[dcroe05]

Not really. The subform part of the form is a very small window, but there is quite a lot on the form in the way of listboxes and commandbuttons, and if I made the data part of a continuous form and put all the action items in the header and footer, I'd have a ridiculously wide form that only showed 10 or so records. So its usabilit would plummet.

 

[Remou]

It is possible to build the whole thing with code, but there are other options that might be easier, for example, an import from a central location, if the subform is missing.

 

[dcroe05]

Right now the main form checks for the existence of the subform and tells the user if it is missing. I can't automate the download because I don't have a consistent path the main database, plus generally when they discover they have the incomplete utility they are offline and can't get the subform.

 

[Remou]

What code and controls do you have on the subform? Would a query or table be suitable as the object loaded into the subform control, rather than a form?

 

[dcroe05]

There is no particular need for the subform to link to a from as opposed to a table of query.

Sourcing to a table would create the same problem, but if I source to a query that might work if I could build the query through code based on the table and field selected by the user.

But if that's possible I don't know how to do that.

 

[Remou]

This will not be much of a problem. You need an SQL string, which can be built from user information. You can use this to create a query, which can be used as an object in your form. For example:

'References: DAO 3.x Object Library
'Typed, not tested

'Select the fields from a list box
For Each varItem In Me.lstFields.ItemsSelected
   lst = lst & "," & Me.lstFields.Column(1, varItem)
Next varItem

StrSQL="Select " & Mid(lst,2) & " From " & Me.cboTable

If IsNull(DLookup("[Name]", "MSysObjects", "[Name]='qryQuery'")) Then
    Set qdf = CurrentDb.CreateQueryDef("qryQuery", strSQL)
Else
   Set qdf = CurrentDb.QueryDefs("qryQuery")
   qdf.SQL = strSQL
End If

Me.[Data Subform].SourceObject="Query.qryQuery"

 

[dcroe05]

Thanks. That's just what I needed.