Change a subform's fields programmatically

[Nicholas Scarpinato]

I have a form with a list box that calls up a query in a subform based on the
results of another query that finds all the completed batches in the database
and lists them in the list box. Once you double-click the batch you want, it
displays in the subform in datasheet format. However, our batches are set up
differently for specific vendors, and some vendors need different information
than others. What I want to do is use the vendor name to determine the
appropriate fields to display in the subform, and then modify the subform
accordingly. Am I making this harder than it has to be? Can a subform display
a query's results directly (i.e., can the "subform" be a query, and not
really a form at all)?

 

[Golfinray]

I might start with Martin Green's website - he explains this pretty well.
www.fontstuff.com

 

[Nicholas Scarpinato]

Well, I read through as much of that site as I thought could pertain to what
I'm trying to do and I didn't find anything that helped much. (Found some
help to other problems I had though, so thank you for the link.) I'm still
trying to figure out if the best approach to this is to use a subform or if I
need to do something else. I've tried programmatically creating a form, but
the amount of code required is enormous, not to mention the fact that I'm not
real sure how I'm going to get it to work afterwards as a subform, so I think
I'm just going to call it a day and try this again in the morning. Maybe in
the morning I'll get a fresh idea that will solve this.

 

[Nicholas Scarpinato]

I got it working another way. I built a few SQL queries and set the code to
change the SourceObject of the form to whatever query I need for each
specific vendor based on a global vendor ID passed from another function that
runs before this process. Works like a charm, too. The code, in case anybody
needs it, is:

Me.subForm.SourceObject = "Query.qryName"

A few If statements for the vendors that require special information and I'm
set.