unbound subform updating problem

[Phil]

I have a form with an unbound subform. In the subform, I would like
to display all the records from a particular table, sorted on a
particular field. So far, quite a simple matter. The issue becomes a
little more complicated, however, because the table in question gets
altered, based on user input on the main form (the form containing the
unbound subform). Not only do the records in the table change, but
the table may have columns added to it or deleted from it, and I would
like those changes to be reflected in the subform. I can get the
updated records to show up in the subform, but whenever columns are
added or deleted from the table, the subform continues to display the
table with its original columns.

I have been working on this issue for over a week, searching forums,
experimenting with various methods, etc. I feel that I have a good
grasp of how to use subforms, the difference between a subform control
and the subform itself, sourceobject and recordsource properties, etc,
but I cannot seem to hit on the right solution. I feel like this
should be a fairly simple thing to do with Access (I'm using A2003,
btw)...

Any help or suggestions greatly appreciated. I can post my code if
desired, but i'm more interested in knowing generally how an
experienced programmer would tackle this problem rather than exact
syntax.

Thanks
Phil

 

[John W. Vinson]

i'm more interested in knowing generally how an
experienced programmer would tackle this problem rather than exact
syntax.

Unless there were NO other way to do it, I would certainly *not* by
dynamically adding, deleting, reordering and redefining fields in a table!

What real-life Entity type does this child table represent? Why are fields
being added and changed? What are some representative fields? What you're
trying to do is very unusual and is the source of your difficulties!

John W. Vinson [MVP]

 

[Phil]

I understand that what I'm doing is not very elegant from a database-
design perspective. Here's a bit of an explanation:

I have a collection of real-world entities (call them widgets). Each
widget has a unique identifier, a collection of values, and zero or
more optional characteristics, each of which can have a value.

I maintain a table that has optional characteristics and the values
for those characteristics (along with widget identifiers, of course).
This table is separate from the table that contains the collection of
values for each widget, which allows me keep the optional
characteristics of the widgets dynamic, rather than hard-coding fields
in a table for characteristics whose names can change and which exist
in unknown quantities for each widget.

At run-time, my user selects a widget and would like to see the
collection of values, along with any and all optional characteristics
(and the values of those optional characteristics) for the selected
widget and zero or more related widgets (the relationships between
widgets are defined based on user inputs and vary from one run to the
next).

I have written code to dynamically create a table at run-time which
contains a record for the selected widget and a record for each of the
related widgets, including fields for each and every optional
characteristic that exists for each of the widgets in this dynamically-
created table. Essentially, I have exactly what I want to display,
but its a table, and I would prefer to display it in a form so I don't
have to expose my tables to the user and to generally produce a more
streamlined UI. I would also like to be able to sort the records in
my table.

I hope that makes sense. If there is a more elegant way to do what
I'm trying to accomplish, I'd be happy to scrap all my code for this
problem and start with a clean slate

-Phil

 

[John W. Vinson]

I understand that what I'm doing is not very elegant from a database-
design perspective. Here's a bit of an explanation:

I have a collection of real-world entities (call them widgets). Each
widget has a unique identifier, a collection of values, and zero or
more optional characteristics, each of which can have a value.

So you have a Many to Many relationship between Widgets and Characteristics. A
normalized design would use three tables: Widgets; Characteristics; and
CharacteristicValues. The latter would have a foreign key to the Widgets
table, a foreign key to the Characteristics table, and a value that
combination of widgets and characteristics.

I maintain a table that has optional characteristics and the values
for those characteristics (along with widget identifiers, of course).
This table is separate from the table that contains the collection of
values for each widget, which allows me keep the optional
characteristics of the widgets dynamic, rather than hard-coding fields
in a table for characteristics whose names can change and which exist
in unknown quantities for each widget.

A many to many relationship - with one characteristic value PER RECORD, rather
than one per field - is much more dynamic and much easier to implement. No
fieldnames should identify specific widgets, *or* specific characteristics;
those are *data* which should be stored as data in fields, not as attributes
in fieldnames.

At run-time, my user selects a widget and would like to see the
collection of values, along with any and all optional characteristics
(and the values of those optional characteristics) for the selected
widget and zero or more related widgets (the relationships between
widgets are defined based on user inputs and vary from one run to the
next).

Very easily done with a Subform, and with a self join query - perhaps with a
Widget to Widget relationship table.

I have written code to dynamically create a table at run-time which
contains a record for the selected widget and a record for each of the
related widgets, including fields for each and every optional
characteristic that exists for each of the widgets in this dynamically-
created table. Essentially, I have exactly what I want to display,
but its a table, and I would prefer to display it in a form so I don't
have to expose my tables to the user and to generally produce a more
streamlined UI. I would also like to be able to sort the records in
my table.

I hope that makes sense. If there is a more elegant way to do what
I'm trying to accomplish, I'd be happy to scrap all my code for this
problem and start with a clean slate

You might want to at least consider the normalized design above. Don't scrap
your code yet... but I think if you work with the relational paradigm rather
than "committing spreadsheet" as you are now doing, you'll be pleased!

John W. Vinson [MVP]

 

[Phil]

Thanks for the suggestions. Top-level DB structure and design is
definitely not my strongest suit. I'll work up a design with the
table structure as you suggested and see what I come up with.

Thanks again.

-phil

 

[Phil]

On a related, but more syntax-specific note, I've been having a hard
time successfully pointing my subform control at the correct result
set. Specifically, programmatically assigning a stored query to the
sourceobject attribute of my subform control creates undesirable and
inconsistent results. Here's the scenario:

I have a table that contains the result set I want to display in my
subform (With my new architecture, this table may in fact be the
result of a self-join query rather than a stored table). Let's call
my table tblWidgetResults. I also have a stored query (called
qryWidgetResults), which simply says 'SELECT * FROM [tblWidgetResults]
ORDER BY [widgetsize]' At run-time, I first disconnect the query from
my subform with this line of code:

Me.<subformcontrolname>.SourceObject = ""

(without this line, I am unable to modify the tblWidgetResults table
to reflect user input, since the subform's sourceobject is still tied
to that table via my query and won't allow it to be locked for
updates)

Then I have code that updates the records (and currently, the columns)
in the tblWidgetResults table.

Finally, I reset the subform control's sourceobject with the line:

Me.<subformcontrolname>.SourceObject = "query.qryWidgetResults"

This works perfectly and displays the correct results in my subform,
BUT (and this has got me tearing my hair out), it also results in the
destruction of the stored query qryWidgetResults. After running the
above code, if I open the query in SQL view, all that is left of it is
'SELECT ;' I have also tried setting the sourceobject to a SQL string
generated in code as a workaround:

stringsource = "SELECT * FROM [tblWidgetResults] ORDER BY
[WidgetSize]"
Me.<subformcontrolname>.SourceObject = stringsource

but this generates run-time error 2124: the form name you entered
doesn't follow object naming rules. Presumably something wrong with
my syntax, although the left-hand side of my assignment is identical
to the one that I've used successfully when assigning the stored query
as the sourceobject.

Any ideas?

Thanks,

Phil

 

[John W. Vinson]

Finally, I reset the subform control's sourceobject with the line:

Me.<subformcontrolname>.SourceObject = "query.qryWidgetResults"

This works perfectly and displays the correct results in my subform,
BUT (and this has got me tearing my hair out), it also results in the
destruction of the stored query qryWidgetResults.

I'm astonished that it works that WELL.

A Subform Control's source object should be a Form, not a Query. My guess is
that - somehow - the form wizard is getting into the act, creating a Form
based on your Query, and saving it as qryWidgetResults (thereby destroying the
Query object).

Normalize! Normalize! Normalize!

And use a Form based on a (permanently stored, needing no modification) Query
on a (permanently stored, needing no modification) Table!

<g>

John W. Vinson [MVP]