none
Access 2007: Is there a technique to bypass a continuous form's single control instance issue

    Question

  • As you know, if I use a continuous form for data entry, this poses a million and one problems because Access 2007 creates only one instance of a control for all records on the form. So if I have 10 records all lined up, but one of them needs to call an event, the programming behind that event will affect every single control for every record. This is extremely limiting. It is so limiting it makes using continuous forms for data entry or editing almost useless.

    Is there a way to fundamentally bypass this limiting factor?

    I am not asking for a trick or work around. I am asking if anyone has found a way to customize Access Forms so that each record has it's own control instance? 



    • Edited by crushbrain Saturday, September 17, 2011 5:02 PM
    Saturday, September 17, 2011 4:59 PM

Answers

  • The form's underlying table would in a real world context be one similar to Nothwind's OrderDetails table (I'm deliberately omitting spaces so as not to repeat Northwind's encouragement of bad practice).  If you join this to Products, and Products to Categories (if Northwind had one rather than - I find this hard to believe - a value list!), you could not only include the Product value in the text box overlaying the product combo box, but also the Category value in a text box overlaying the category combo box. 

    So the point I'm making is that to achieve normalization to 3NF both must be 'hybrid controls' when using a continuous form, not just the product combo box.  With a normalized table the category combo box must be unbound, which requires a 'hybrid control' to be used to keep the category showing correctly (in the overlying text box) for each row.  It can only be a single control in the context of a table not normalized to 3NF as it would have to be a bound control in this case.

    Checking my demo file just now I realized that I'd completely forgotten that I'd added a form, frmLocations_Cont_MCB_Simple, to the file which does exactly this, with District equating to Category and Parish equating to Product.  The form is based on the following query:

    SELECT Locations.*, Parishes.Parish, Districts.District, Districts.DistrictID
    FROM (Districts INNER JOIN Parishes ON Districts.DistrictID = Parishes.DistrictID)
    INNER JOIN Locations ON Parishes.ParishID = Locations.ParishID;

    The form isn't one of those selected from the opening form, having been added as an afterthought to demonstrate correlation in a simple 2-level hierarchy rather than the more complex 3-level hierarchy of the original forms.  It's in my public databases folder as ComboDemo2K.zip on SkyDrive at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    You might have to copy and paste the text of the above (not copy the link location) into your browser; for some reason it sometimes doesn't seem to work as a hyperlink.

    PS: As an Irishman I'll tactfully avoid any mention of rugby<G>
    Ken Sheridan, Stafford, England
    • Marked as answer by Bruce Song Monday, September 26, 2011 7:35 AM
    Monday, September 19, 2011 12:53 AM

All replies

  • As you know, if I use a continuous form for data entry, this poses a million and one problems because Access 2007 creates only one instance of a control for all records on the form. So if I have 10 records all lined up, but one of them needs to call an event, the programming behind that event will affect every single control for every record. This is extremely limiting. It is so limiting it makes using continuous forms for data entry or editing almost useless.

    Is there a way to fundamentally bypass this limiting factor?

    I am not asking for a trick or work around. I am asking if anyone has found a way to customize Access Forms so that each record has it's own control instance? 




    Hi crushbrain,

    Perhaps you mark it as a trick or work around, but for new records I never use a continuous form. For that purpose I use a single record unbound form, that can be used for every table.

    Also most of the editing of the records I do through single record for that one record.

    For overviews I use the continuous forms that are - in nost cases - not editable.


    However, your grief I do not share:

    So if I have 10 records all lined up, but one of them needs to call an event, the programming behind that event will affect every single control for every record. This is extremely limiting. It is so limiting it makes using continuous forms for data entry or editing almost useless.

    After editing a control on a continuous record, you can make use of the different events. For each event you know exactly which field it was in which record. With a little smarter code in the event it is possible to nuanciate per record.

     

     

    Saturday, September 17, 2011 6:31 PM
  • Hi crushbrain,

    I now realize that you probably want the change some general control properties after editing a control, such as backgroundcolor.
    Unless you conditional formatting, a change of the control's backgroundcolor (or some other property) is for all the records.

    Personally I do not work with conditional formatting. Nor do I highlight some fields or controls. When you use these techniques you have to filter "manually" (optically) which records are important and which are less important.
    I prefer to make a new recordsource, so that I only see the relevant records, nothing more and nothing less, so that I can take my actions on the basis of those records.

     

    Imb.

    Saturday, September 17, 2011 7:35 PM
  • If you're talking about some sort of conditional formatting, Detail Paint Event will do the trick for a continious form.
    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Saturday, September 17, 2011 8:54 PM
  • One rather special situation where forms in continuous forms view do pose a small problem is in the use of correlated combo boxes where the referencing combo box's value is a hidden surrogate key rather than the visible value.  This can be overcome by the use of a natural key, however, or where that is ruled out by legitimate duplication of the value for different entities, e.g. city names, by the use of a hybrid control or by using a single multi-column bound control for data selection, with unbound text box controls referencing it's Column property to show the other values following a selection.  

    Otherwise what you say is only the case as regards formatting which cannot be achieved selectively by means of the conditional formatting mechanism, or values in an unbound control whose value is assigned by means of the code rather than computed by means of an expression or function as its ControlSource property.  Code which assigns a value to a bound control will only affect the current record.

    As regards your statement 'It is so limiting it makes using continuous forms for data entry or editing almost useless', all I can say is that there must be countless applications in daily use which refute this.  Forms in continuous forms view are very commonly used as subforms in particular, to enable data to be entered into rows in a table which references that on which the parent form is based.

    Perhaps you could give some indication of the type of operations you find inhibited by the use of a form in continuous forms view.


    Ken Sheridan, Stafford, England
    Sunday, September 18, 2011 6:44 PM
  • Ken: Take a look at my February, 2006 Access Answers column Tricks With Combo Boxes for a way to get around the cascading combo box issue you mention.
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Sunday, September 18, 2011 8:38 PM
  • Doug:

    I like the idea of returning the non-key column from the referenced table in the form's underlying recordset.  That should be a lot more efficient than calling a function as my online demo does.  I'll have to try and rework it along those lines.  I feel hoisted by my own petard on this one, as it's usually me who proselytises relational in preference to code solutions, and here I am caught doing the reverse!

    Am I right in thinking that in your example the form's underlying table is not normalized to 3NF, i.e. it includes columns for both category and product IDs?  My file addresses this; in fact that was its primary purpose, not to demonstrate correlation per se, which is well covered by solutions like yours, but to do so specifically in the context of a normalized table.

    A lot of people seem quite happy to accept a table not normalized to 3NF of course, or maybe are not aware that it isn't normalized.  It wouldn't be possible in my former line of work, however, which, although of a technical nature, was carried out in a quasi-judicial context, and the possibility of consequences arising from update anomalies would be unacceptable.

    I do know of cases within our area, not of our making I'm glad to say, where individuals have found themselves subject to legally enforceable constraints on their freedom of action as a result of legal instruments being issued incorrectly as a result of sloppy data.  In one case I made representations to the central government department who had issued the incorrect instrument, but the result was that they flatly refused to admit an error, and issued another instrument constraining the person to whom the first one should have been issued, without revoking the first one.  The result was that two people were now constrained rather than one!  So I do tend to be a little paranoid regarding data integrity.


    Ken Sheridan, Stafford, England
    Sunday, September 18, 2011 11:41 PM
  • Am I right in thinking that in your example the form's underlying table is not normalized to 3NF, i.e. it includes columns for both category and product IDs?  My file addresses this; in fact that was its primary purpose, not to demonstrate correlation per se, which is well covered by solutions like yours, but to do so specifically in the context of a normalized table.

    Well, the example is just supposed to demonstrate the approach, so it may be somewhat artificial. However, I don't see anything that prevents it from being 3NF. The category ID needn't be present in the table to which the form is bound: I think you can derive it through a query that joins Product to Category (sorry, it's over five years since I wrote the article!) lThat form could represent a look-up in a product table (where the rest of the row would show you information about the selected product), or it could represent an invoicing system (where the intent is to help the order taker find a specific product from a smaller list).
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Sunday, September 18, 2011 11:55 PM
  • The form's underlying table would in a real world context be one similar to Nothwind's OrderDetails table (I'm deliberately omitting spaces so as not to repeat Northwind's encouragement of bad practice).  If you join this to Products, and Products to Categories (if Northwind had one rather than - I find this hard to believe - a value list!), you could not only include the Product value in the text box overlaying the product combo box, but also the Category value in a text box overlaying the category combo box. 

    So the point I'm making is that to achieve normalization to 3NF both must be 'hybrid controls' when using a continuous form, not just the product combo box.  With a normalized table the category combo box must be unbound, which requires a 'hybrid control' to be used to keep the category showing correctly (in the overlying text box) for each row.  It can only be a single control in the context of a table not normalized to 3NF as it would have to be a bound control in this case.

    Checking my demo file just now I realized that I'd completely forgotten that I'd added a form, frmLocations_Cont_MCB_Simple, to the file which does exactly this, with District equating to Category and Parish equating to Product.  The form is based on the following query:

    SELECT Locations.*, Parishes.Parish, Districts.District, Districts.DistrictID
    FROM (Districts INNER JOIN Parishes ON Districts.DistrictID = Parishes.DistrictID)
    INNER JOIN Locations ON Parishes.ParishID = Locations.ParishID;

    The form isn't one of those selected from the opening form, having been added as an afterthought to demonstrate correlation in a simple 2-level hierarchy rather than the more complex 3-level hierarchy of the original forms.  It's in my public databases folder as ComboDemo2K.zip on SkyDrive at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    You might have to copy and paste the text of the above (not copy the link location) into your browser; for some reason it sometimes doesn't seem to work as a hyperlink.

    PS: As an Irishman I'll tactfully avoid any mention of rugby<G>
    Ken Sheridan, Stafford, England
    • Marked as answer by Bruce Song Monday, September 26, 2011 7:35 AM
    Monday, September 19, 2011 12:53 AM
  • What particular event you talking about?

    For the most part when working with the typical form events you still only working with one record and there is no confusing as to what record the event will apply to.

    The before change, after update etc. ONLY apply to the current record you are on and does not effect the other rows here. Since you only working with one record, then all events for that form apply only to the one record. I not at this point seeing the problem here.

    You might want to give an example of what you attempting to accomplish here.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, September 21, 2011 12:38 AM
  • Crushbrain,

    No offence nor any prejudice intended. Is it really that you have a crush brain? :-)

    We cannot guess what you intend to do with the recordsets from your post. Why edit one record and change the other records? Is your databse un-normalized by designed?

    Why not look into the use of Continuous Forms or Datasheet for their purpose instead of trying to re-invent the wheel? I suggest that you study the use of Forms in continuous or datasheet view first to gain the understanding of how it would work in a real world scenario before you run out to attack it with your shotgun.

    Maybe you don't even need a continuous form to do this? Just a Form with the record you want to edit and a Listbox of records you intend to modify and with an event to fire-up a sub/function to change the recordsets in the Listbox?

    Wednesday, September 21, 2011 1:37 AM
  • I realize I usesd pretty strong language. I hope I didn't offend anyone. I love Access 2007. I thought it might stimulate some interesting discussion.

    Anyhoo, my situation is this. I have a continuous form which acts as a subform. In order to fit on my parent form according to specifications, the textbox controls must be tightly packed and double stacked (in other words, I have textboxes positioned on their own borders horizontally and vertically). This means I can't have any labels in the subform. So I figured I could create one label, highlight it in gray, italicize the text, and when the textbox has the focus, the label updates to show the user what field they are using. This works nicely and looks beautiful.

    The problem is that the gotfocus event fires for every single line of the continuous form. Which means every line's label updates. Which no longer looks beautiful and is functionally in-elegant and could be somewhat confusing.

     

    To try and illustrate I used this txt to illustrate a text box:

    [-]

    and label boxes like this

    (-)

    My continuous form looks kind of like this

    [---][-------------------][--][---]

    [--------------][--------------](-)

    So when that gets repeated you might imagine it becomes somewhat visually overwhelming. It is also frustrating that every single (-) label box updates no matter which record is selected.

    Wednesday, September 28, 2011 6:05 PM
  • My continuous form looks kind of like this

    [---][-------------------][--][---]

    [--------------][--------------](-)

    So when that gets repeated you might imagine it becomes somewhat visually overwhelming. It is also frustrating that every single (-) label box updates no matter which record is selected.

    Hi crushbrain,

    Would it help if you place your label in the Form Header or the Form Footer?
    In that case you see only one label instead of a label per record.

     

    Imb.

    • Proposed as answer by KCDW Wednesday, September 28, 2011 6:41 PM
    Wednesday, September 28, 2011 6:22 PM
  • One possibility might be to use conditional formatting to make the labels visible only in the currently selected row:

    1.  Firstly you'd have to change the labels to text boxes with the label text as the ControlSource property, e.g.  ="My label".  Format the text box to look like your labels, with no visible border for instance.

    2.  Add a hidden text box control to the subform, txtMyIDHidden say, and in the subform's (i.e., its source form object's) Current event procedure assign the value of whatever is the key to the control with something like this:

    Me.txtMyIDHidden = Me.MyID

    3.  For each 'label', now a text box, use conditional formatting to set both its ForeColor and BackColor properties to whatever is the background colour behind the 'label' on the basis of the expression:

    [MyID] <> [txtMyIDHidden]

    The multiple instances of text box 'label' are still present in every row, of course, but by virtue of the ForeColor and BackColor properties matching that of the background colour cannot be seen pther than in the currently selected row.


    Ken Sheridan, Stafford, England
    Wednesday, September 28, 2011 9:37 PM
  • I realize I usesd pretty strong language. I hope I didn't offend anyone. I love Access 2007. I thought it might stimulate some interesting discussion.

    Anyhoo, my situation is this. I have a continuous form which acts as a subform. In order to fit on my parent form according to specifications, the textbox controls must be tightly packed and double stacked (in other words, I have textboxes positioned on their own borders horizontally and vertically). This means I can't have any labels in the subform. So I figured I could create one label, highlight it in gray, italicize the text, and when the textbox has the focus, the label updates to show the user what field they are using. This works nicely and looks beautiful.

    The problem is that the gotfocus event fires for every single line of the continuous form. Which means every line's label updates. Which no longer looks beautiful and is functionally in-elegant and could be somewhat confusing.

     

    To try and illustrate I used this txt to illustrate a text box:

    [-]

    and label boxes like this

    (-)

    My continuous form looks kind of like this

    [---][-------------------][--][---]

    [--------------][--------------](-)

    So when that gets repeated you might imagine it becomes somewhat visually overwhelming. It is also frustrating that every single (-) label box updates no matter which record is selected.

    To make things clearer so that others might have solution for you.

    Did you fire the event "gotfocus" from the subform or are you firing from each control's event?
    From what you describe, you should using each control's event.

    This sound's like the balloon help in Access. You might not need to re-invent the wheel if that is the case. This is avialable in each control. Use the "ControlTip Text" property to display the name of a control or to show the user what is the Textbox is use for. All the user needs to do is to move the mouse cursor over the control, the balloon help with appear as what you have written in the "ControlTip Text" property.

    If you prefer to use the label, you can use each control event to display what is in the "ControlTip Text". Something like this...

    me.mylabel.caption = me.myTextbox.ControlTipText

    Does that do what you want?

    PS. To display the name of a control.....
    me.mylable.caption = me.myTextbox.Name

    • Edited by AccessVandal Thursday, September 29, 2011 2:02 AM
    Thursday, September 29, 2011 1:56 AM