unbound subform updating problem

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

0
Phil
5/28/2007 3:00:23 AM
access.forms 6864 articles. 1 followers. Follow

6 Replies
1349 Views

Similar Articles

[PageSpeed] 17

On 27 May 2007 20:00:23 -0700, Phil <perphectnumber@gmail.com> wrote:

>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]
0
John
5/28/2007 3:14:51 AM
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


On May 27, 9:14 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On 27 May 2007 20:00:23 -0700, Phil <perphectnum...@gmail.com> wrote:
>
> >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]


0
Phil
5/28/2007 5:20:28 AM
On 27 May 2007 22:20:28 -0700, Phil <perphectnumber@gmail.com> wrote:

>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]
0
John
5/28/2007 5:55:39 AM
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

0
Phil
5/28/2007 6:17:52 PM
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

0
Phil
5/28/2007 7:31:25 PM
On 28 May 2007 12:31:25 -0700, Phil <perphectnumber@gmail.com> wrote:

>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]
0
John
5/28/2007 11:38:01 PM
Reply:

Similar Artilces:

MS029 (KB912442) problem... need a script
Hi all, We have Exchg. 2003 SP2 and have installed patch MS06-029. On addiction to this, we can't use Mailbox Store >- Security for adding more users on whole Store. Basiclly, we have to change OU. Question: I can export all of my key account people to .txt or csv... There are a lot of OU on whole A.D. Could I try to start script on A.D. root who will change (Add) Send As right for these people (not in the same OU). If yes, I need a script :-) Thnx a lot! Here's a good place to start. http://support.microsoft.com/default.aspx?scid=kb;en-us;237677 "Dan" <d...

Problem typing emails
I can open a new email or reply to one and have the same problem: I can type the first few words without any problems and then I start to have trouble typing the words. I will hit the keys but the corresponding letter or number does not appear on my document. I need to hit the key numerous times before I see it. I don't have a problem typing a WORD document or even typing this question. Please help!!! Thanks, Sandy Your symptom is indicative of interference from a third party security program, particularly the anti-spyware ones that add thousands of = entries=20 to the R...

spelling problems
i have a spelling issue with XP using outlook that comes with XP i keep getting the correction on my emails when i use the spelling check eg .....if i type whats ...running spelling checker..i get it corrected to What,s how do i rectifty this? only happens in m/outlook no other program thanks for any advice What's is short for What Is and is properly What's And for future reference, this is actually the Dynamics GP (Great Plains) forum. You might be better served in an outlook forum -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get ...

New Win7 computer problems
Also posted in Lsoft group I bot a new HP p6240f computer with win 7. Loaded excel 95, then xl97 then xl2003. Then tried it. There must be something wrong in Windows or the video card cuz I get narrow columns and wide rows and misshapen shapes and small fonts in the formula bar. I can send a screen shot to anyone who may be able to assist. Normally, when I migrate from one computer to another all I have to do is adjust the zoom. All programs run fine on my Vista computers. I have tried the new LG2486L monitor on my notebook and it worked just fine and I tried an old dell 17"...

Data update
Is there a way to use an existing org chart (in Vision 2007) and use an updated excel file to update that chart? I am tired of having to redo these org charts every month. ...

Problem with Exchange System Manager
The other day I deleted a mailbox using the Exchange Tasks in the Exchange System Manager (ESM), and today when I fired up ESM the entire heirachy for our mail server has disappeared. So instead of being able to view the mailbox stores, queues, logons, etc etc underneath the server root, the only to branches are Recipients and Tools. Does anybody know what has happened? Our mail services are still functioning, I just can't see anything in the EMS. This can occur only if you havn't permissions to Exchange organization. Check you permissions. -- Best Regards. "Naresh" ...

unbound forms
I have a form that has a recordsource based on a query I typed in and I have all the controls on the form unbound. I believe with a bound control it keeps a link to the database so there is a lot of communication between form and database right? With an unbound form does the recordsource "download" a recordset that the form then walks through? So if I add a next button to the form, when next is clicked does the form requery the database or does it get the data from the recordset? I am working in access 2007 and right now the database is split with the backend stil...

Display subform total
I have an Access 2007 single form with subform. The subform has a hidden textbox that sums a record in the subform and displays in a textbox on the form. The form textbox is blank, but becomes visable if I click on the taskbar or open another app. If I update the subform or move to another record, I loose it again. I've tried requery and recalc on practually every event of the form, subform and both textboxes to no avail. What will make this textbox stay visible? -- Lafayette, LA Never mind; I got it. I changed the field from text to double number and added me.reca...

Printing problem #9
I have created a 30 page booklet in publisher 2003. When I try to print it, it duplictates page #7. why is it doing this and how can I correct it? Publisher asks you to insert pages till the number is divisable by 4. because any booklet must be a multiple of 4 to print front and back of each page. Fix this and your problem should go away. John G. <michael.russo@atlanticbb.net> wrote in message news:e5c91681-ef0b-4588-8936-ad2c4cc9a7ef@h16g2000yqj.googlegroups.com... >I have created a 30 page booklet in publisher 2003. When I try to > print it, it duplictates page #7. why is it...

CRM-EMAIL problems
When I try to send CRM Email, it fails and asks me to contact the administrator. Everything else works fine. Can someone tell me what I need to check. CRM/SQL/Exchange are all on separate servers. Exchange is a domain controller. Thanks On the crm server you need to check the registry settings such as hkey_localmachine/softare/microsoft/mscrm/ you will see an entry for mailserverurl this should be something like http://crmservername/mscrmconnector/crmemaildispatch.srf "Said Kay" <said@trident.net> wrote in message news:022201c35276$febb6b60$7d02280a@phx.gbl... >...

Sync Problem Please Help error. 0x8004108d
When I Open Windows Live Mail I get a message saying No Internet connection is currently avaible. To view Internet content saved on your computer, click Work Offline. Click Try Again to attempt to connect. At this stage my wireless broadband is connected but Windows Live Mail will nto recognise it. I can only fix this problem by disconnecting and connecting my interent while WLM is open which is annoying. when I click try again WLM attempts to synchronise using a connection called "local area connection" instead of "virgin broadband connection and it says "...

CRecordset Update Exception error. Can't update
Can somebody provide some sample code on how to update BLOBs in Oracle using CRecordset? Thanks Rafael ...

How to bulk update for quantity discount pricing
Hi all. I have RMS Store Operations V2.0 I hope anyone can help me on solution / procedural work to do bulk updates on quantiy discount table. I am familiar with SQL I have a group of items, where the retail prices are set based on a formula based on item dimensions (thickness and width) The discounted prices are also based on a similar formula The number of items to update when prices changes is around 300 items Here's what I notice from RMS 1. When choose to use quantity discount pricing table from an item properties and entered accordingly, one entry is inserted into the database, tab...

Label Visible Problem
I have a listbox on my form called [listNoClient] and a label [lableNoClient], I am trying to make the lable Invisible when the list box is Null, and Visible when the listNoClient Not Null.....................Thanks for any help................Bob What is the value of the Multiselect property of the listbox? -- Ken Snell <MS ACCESS MVP> "Bob" <xxx@xx.xx> wrote in message news:f2888t$21c$1@lust.ihug.co.nz... > > > I have a listbox on my form called [listNoClient] and a label > [lableNoClient], I am trying to make the lable Invisible when ...

Problem deleting the CSocket object in secondary thread
Environment : VC++ 6.0, Windows 2K Hi All, I have a CSocket derived object created in a secondary thread to listen. When i delete this object it throws an exception from CSocket::close(); When i create the same object in the Primary thread there is no problem. The secondary thread is a UI thread and my Socket implementation is also able to Recieve messages when i run it. The code below pin points the area where the first Assertion failure occures when I am deleting the CSocket object. void CSocket::Close() { if (m_hSocket != INVALID_SOCKET) { CancelBlockingCall(); VERIFY(Async...

S&T 2010 updated maps
I just bought a box copy of S&T 2010 and upgraded my 2009 S&T installation. My main motive was to get the map errors out of the way. S&T 2009 is full of map errors and this makes it about worthless for me. When I got S&T 2010 installed I checked some of the known map errors and they are still there. For example, in northwestern Oklahoma, along U.S. Highway 60, west of the town of Arnett, the rural road numbers on either side of the highway don't match. Also, the town of Harmon, 12 miles east of Arnett is half a mile west of its actual positron. Several other s...

Subform DataEntry property (Access 2007)
Greetings, I have a subform and based on a combo box I want to change the data entry property. Ive had a google to look for the property name, however the code below doesnt work as it cant find the data entry property. Me.qryStudentsResultsDeliverysubform1.DataEntry = False How do I access it? Thanks Rob Rob, Depends on where the combobox resides... is it on the mainform or on the subform. If it's on the mainform you should reference that form as well. If its on the subform which should be data-entry you could just set me.form.dataentry=false as you did. I'm assuming it&...

Update Calendar Dates, Keep information
I have a calendar that I used last year for basketball tryouts and practice schedule. Is there a way to keep the information in the same place and update the dates in the calendar? I tried to "Change Dates" but it erases all my information. -- Thanks As Always Rip ...

minus numbers causing a problem
I am trying to set up a 4 day "Scorecard" for a golf tournament using Excel 2002. Column A is players name. column B is handicap. Column C is Day1 Gross score, D is Day1 Net score and E is a daily running Total. Repeated for the four days with Total Gross and Net scores as the last two columns. I want Column D to show the result of C -B and Column E to show the running total Net scores. I was wondering if there was a formula I could use that wouldn't show and total the Minus figure generated at Column B. I have managed to hide it using Conditional Formatting but it is ...

problem when entering manually a record in the CRM tables
Why when I enter manually (via SQL Studio Management) a record in the tables (base, extension) that represent an entity in a CRM, I can see this record via the CRM Interface. Any idea about this issue? Microsoft recommends that you do not enter data directly into the SQL tables for CRM. This is unsupported. -- Matt Wittemann, CRM MVP http://icu-mscrm.blogspot.com "Ahmed Maaloul" wrote: > Why when I enter manually (via SQL Studio Management) a record in the tables > (base, extension) that represent an entity in a CRM, I can see this record > via the CRM Interface....

msaccess 2007 adp project indentity set off ? in subform
Hi.. I have a mainform and a subform, as soon a i try to add a record. it fails. (invalid input parameter values.check the status values for detail.) if i directly use the subform without the main form it works fine. i believe the identity is set off . thanks in advance On Thu, 15 Apr 2010 06:55:59 -0700 (PDT), skippyeddy <eddy@grienden.nl> wrote: > > >Hi.. > >I have a mainform and a subform, as soon a i try to add a record. it >fails. >(invalid input parameter values.check the status values for detail.) > > >if i di...

Trigger Subform "On Current" from Parent Form "On Activate"?
Is there a way to trigger the "On Current" event of a subform from the parent form's "OnActivate" event? -- Thanks, tbl tbl wrote: >Is there a way to trigger the "On Current" event of a >subform from the parent form's "OnActivate" event? Just call the subform's Current event (after making it Public): Me.subformcontrol.Form_Current Why do you need to use the Activate event? It is a rather unusual event to use for most any purpose. -- Marsh MVP [MS Access] On Mon, 27 Aug 2007 19:14:34 -0500, Marshall Barton <marshbarton@wowwa...

Fear and trembling- updates, restores and back ups etc
I have a fear- it goes back to when I was a QS user. When I made the switch over to QS with the MSDE engine, I had a heck of a time creating a new database and importing my old QS data- I had a QS support guy on the line all day and we could not get it to work. Finally we got it up and running with the new Quick sell/RMS. It was a real ugly process and I lost a couple of days operations and data. I have a mental block about doing any updates and restores- like trying to land an f14 on an aircraft carrier. Could someone out there do a step by step - ABC- 1, 2, 3, etc on how to do: 1) Upd...

Make a change to an Update Query
I need to make changes to an update query but when I open the desing view it only shows the field that being updated. How do I view the hidden fields? Answered in your prior post John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County JLUTHER wrote: > I need to make changes to an update query but when I open the desing view it > only shows the field that being updated. How do I view the hidden fields? ...

Index Match Problem
I'm using the following formula, but it's returning #N/A: =INDEX('File X'!$F$1:$F$2000,MATCH(1,($B5='File X'!$A$1:$A$2000)*(S$4='File X'!$G$1:$G$2000),0) However, if I replace the S$4 with "7710", it returns the correct value. The number in S4 is 7710. Why won't the formula recognize the cell reference, but if I type the contents of the cell manually, it works? I double-checked to make sure nothing was entered as text. Hi, is 7710 a number typed or imported, check if you don't have blank spaces in one of the two cells you ...