 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / January 2006Unbound Nested Subform Simulating Continuous Form
Need some help to stop me going around in circles on this one.... Have a nested subform (subform2) which simulates a continuous form for the record on the parent subform. Subform2 has rows of either an option button plus two text fields or a checkbox plus two text fields Am wanting to save the user entries into an underlying table. Tag property for each option button, check box or text field has the value of the key field for each record to be saved to the underlying table. Any help/suggestions on best way to do this appreciated. Thanks Bob Why use an unbound form, when you want to save the values into a table? Why go to the trouble of trying to simlate an unbound continuous form when a bound one is handed to you on a platter and requires next to effort to develop? The "best way" will be to use a bound continuous subform.  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Need some help to stop me going around in circles on this one.... > [quoted text clipped - 13 lines] > > Bob Allen, Started with a bound subform but switched to using an unbound subform for better error control, more control over saving records (single button on parent subform) and for more user friendly data capture (option buttons & check boxes rather than combo boxes on continuous subform). Parent subform has "Project" (one) details and nested subform has (many) "Products" that can be selected via option buttons or check boxes and layout of the form is not on a one record (Product) per row basis. > Why use an unbound form, when you want to save the values into a table? > [quoted text clipped - 21 lines] >> >> Bob If you do come up with a better design that the one Access gives you for free, please let us know. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Allen, > [quoted text clipped - 28 lines] >>> >>> Any help/suggestions on best way to do this appreciated. > If you do come up with a better design that the one Access gives > you for free, please let us know. I don't believe in using editable continuous forms, except in very specific situations. If I want to edit data presented in a continuous form, I use a detail form that can be bound or unbound. This makes the continuous form like a very elaborate listbox with lots of extra features, and with the detail form populated in the OnCurrent event of the list form. Going unbound gives control over certain things, but also takes away any number of extremely useful features (such as the .Dirty property). I've only implemented this kind of thing very, very seldom. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > Allen, > [quoted text clipped - 6 lines] > "Products" that can be selected via option buttons or check boxes and layout > of the form is not on a one record (Product) per row basis. I understand your issue and I have a different approach to solving it. I create a temp table (in a temp database - linked) that I then use in the bound subform. The Save button then does an Update query that moves the data from the temp table to the permanent table. This gives me the flexablility that a bound form gives with the ability to save all the changes or none of the changes (like a transaction commit or rollback but in a form rather than in code). -- Bri Bri, Thanks. Have included temp table and array in options for converting from the Optionbuttons & Checkboxes entries/combinations. Am now trying to determine best way to loop through optionbutton, checkbox and textbox controls on the nested subform to form the record (either directly or via a temp table or array) to add to the underlying tblProjectProducts using Insert Into... query >> Allen, >> [quoted text clipped - 17 lines] > -- > Bri What's your question? You want to save some values? And you don't want to do it the Access way? You, of course, are way better than those stiffs who designed Access on the one hand, but on the other hand, you don't know how to solve this simple problem? Fabulous! Congratulations! So save them. Use an INSERT INTO query if they're new. Use an UPDATE query if they're not. What's the problem? Lyle, Sorry new at access, am using a currentdb.execute "insert into...statement for new records being added. Have product number (unique identifier) in the tag property of each control which allows grouping controls into records. Just not sure on how to read through all the controls of the nested subform and group them into records using the tag property including for each record specifyig which control is to be saved to which feld before using the insert into query. > What's your question? > You want to save some values? [quoted text clipped - 7 lines] > query if they're not. > What's the problem? Probably how to read through would depend on how you created them in the first place. You say your nested subform simulates a continuous form? How? Is the initial data (that is displayed) part of a recordset? Where does it come from? How many rows are on this simulated continous form? How is this number decided? What happens when the user wants to create a new row? What happens when the user wants to delete a row? If you use recordset is it a DAO or ADO recordset? etc. etc etc. Lyle, Nested subform appears to user as as a single form containing a number of option buttons (4), a number of text boxes (12) and check boxes (11). Each pair of the option buttons (not option group) represents an either/or product choice Each check box represents a product choice. The text boxes are some additional fields used for additional info that relate to 6 of the product choices (option buttons/checkboxes) Subform is being used for initial data entry (same methodology will be used to edit existing records but via a separate view/edit form). During initial data entry user can include/exclude products by selecting/unselecting the optionbuttons/checkboxes. User enters details for a single project on the parent subform and selects one or more products (multiple) on the nested subform. Am using ADO to add record on the ubound parent subform and using CurrentDB.Execute "Insert Into... to add each product record in the unbound nested subform to the related (many) table using ProjectNr and ProductNr as key fields. The Tag property for each option button/check box has the ProductNr in it. The Textboxes also have the ProuctNr in the Tag property to allow these to be associated with the relevant Product (Optionbutton/Checkbox). Have been trying to write code to use the TAG info and convert the info loaded in the nested product subform by using the Insert Into query in a loop to add the (multiple) product records to the underlying tblProjectProducts. Not sure if using an array is necessary? > Probably how to read through would depend on how you created them in > the first place. You say your nested subform simulates a continuous [quoted text clipped - 3 lines] > create a new row? What happens when the user wants to delete a row? If > you use recordset is it a DAO or ADO recordset? etc. etc etc. I am guessing: SubForm (UnBound) -> Projects Sub-SubForm (Unbound) -> Products Products can exist only if a Project is specified. We have a many (Product) relationship to one (Project). So Project 1 may have Products 7, 8 , 12. Do this mean there is a Project-Product Table with rows 1 - 7 1 - 8 1 - 12 ? You are using ADO to add Projects and DAO to add Products? Is this some kind of egalitarianism? If not, why? And Tag? You are using the Tag Property of each Product to hold the Value of the Project to which it belongs? Quaint! I can't think of a thing wrong with it except prejudice because "We don't do things like that around here, Sonny!" Regardless, to me, its unusual. I would have put a ProjectID field on my sub-subform and in the OnCurrent event in my SubForm made the default value of that ProjectID Field (in the sub sub form) the value of theProject ID of the SubForm and requeried the SubSubForm . If subform was on new record I would save the new project record first. Bur I am totally guessing here AND I am wondering (1%) if you are using Unbound and Unlinked as the same thing? I am quite confused and can only hope you or someone else will clear things up. Lyle, Yes to ProjectProducts table. Parent subform and nested subform are both unbound but "linked" using code via ProjectId (have a single save button on the parent subform) The Tag property holds the ProductId (not the ProjectId) which is used together with the ProjectId (as key fields) to add products (and some additional Textbox fields associated with some of the products) for this project to ProjectProducts table. So rows in table are: ProjectId ProductNr ProductSites ProductComment 1 7 12 Anything 1 8 3 Something >I am guessing: > SubForm (UnBound) -> Projects [quoted text clipped - 30 lines] > I am quite confused and can only hope you or someone else will clear > things up. > Sorry new at access, am using a currentdb.execute "insert > into...statement for new records being added. Have product number [quoted text clipped - 4 lines] > specifyig which control is to be saved to which feld before using > the insert into query. I, for one, am not going to try to puzzle out an answer to your question, since you're quite obviously simply mis-using Access, and avoiding all the features it provides for accomplishing the tasks at hand. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ David, Am using unbound parent subform and unbound nested subform for data entry (i.e. adding new projects) in a one (Project) to many (Products) relationship. Instead of the nested subform being continous with one of the controls being a combobox having a list of products, the nested subform presents all of the products available as a option buttons and check boxes that the user can select/deselect. Am using unbound subforms to provide better error control, more control over saving records (single button on parent subform) and for more user friendly data capture (option buttons & check boxes rather than combo boxes on a continuous subform). Just trying to determine best way to loop through controls on the nested subform to form the record to add to the underlying tblProjectProducts using Insert Into... query I'm not meaning to misuse Access. >> Sorry new at access, am using a currentdb.execute "insert >> into...statement for new records being added. Have product number [quoted text clipped - 9 lines] > avoiding all the features it provides for accomplishing the tasks at > hand. I'm ecstatic that David has offered to help you as I still can't quite understand what you are doing. Now I can sleep without guilt! > I'm not meaning to misuse Access. You may not be meaning to do so, but you are. You've rejected all the tools Access gives you to make editing data easy. You're missing one important principle that I believe in: continuous forms should not be editable. If, instead, you use a linked single form to display and edit the data for a record selected in the bound display-only continuous form, you can get the UI you want and have all the control you need over when the detail record is created/saved (bound or unbound). SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ David, Probably a case of a picture worth a thousand words as I think from below there is some misunderstanding. Not editing existing records just adding new records. Parent subform is unbound and is single form (used to add a project) Nested subform is unbound and is single form (used to add many products for a single project). Nested subform is a grid of option buttons, check boxes (each one a product choice) which the user selects/deselects to to include/exclude products for the project Using nested subform as there are five sets of product groups i.e. five nested subforms, only one subform visible at a time depending on the project type selected on the parent subform Suggestions I was seeking originally was on best method (code) to loop through all the controls on the nested subform and convert these to (many) records to be saved to the ProjectProducts table relationally for the (one) project being entered on the parent subform. Primary key for the ProjectProducts table is (ProjectNr, ProductNr). Don't think any of the above is mis-use of Access but am certainly not an expert and if there is a better way of doing this the access way then am keen to learn so I posted the question. I probably confused the issue by using the term "simulating continuous form" but by that I meant that the multiple products were saved to the relational ProjectProducts table as if they were entered via a continous (date entry) form. >> I'm not meaning to misuse Access. > [quoted text clipped - 9 lines] > all the control you need over when the detail record is > created/saved (bound or unbound). Lyle Fairfield wrote: > Bri wrote: > [quoted text clipped - 5 lines] > > Self-immolation! That's just as useless a response as your original one. And this is from someone who regularly asks others to post example code to prove their points. Unless, of course, you meant to have a burning affair with a fiery red head? Oh, Wait! You said 'self'... Do you have/had red hair? -- Bri Oops, this got posted against the wrong thread. -- Bri > Probably a case of a picture worth a thousand words as I think > from below there is some misunderstanding. The point is that I didn't read your explanation because you're advocating creating a form that is unbound that replicates the look of a continuous form. THAT IS A WASTE OF TIME. I don't *care* how you think you're going to accomplish it -- the task is unnecessary and an attempt to work against all the tools that Access provides to make data entry and editing easy. Indeed, if you're only *adding* records, it makes my suggestion even *more* appropriate. You'd have a collection of unbound controls on your parent form where you'd input the data for the new record, then a SAVE button that would write those values to the child table and requery the BOUND subform. One of your objections was the problematic nature of certain controls on continuous forms. That is *onl* relevant if you're editing the records in the continuous form. For instance, if you use a filtered combo box to enter data into a particular field (where the combo box is filtered based on data in the record you're editing), that is irrelevant if the continuous form is not being used to edit -- you just display the data, with no need for the combo box. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.