none
Default form/report control names cause circular reference errors RRS feed

  • Question

  • When placing controls on Forms/Reports (e.g. from the Design/ Add Existing Fields pane), the default Control name is identical to its bound field name.

    Thereafter, when using the field name in calculating the value to be displayed in the similarly named control (where "Control Source" starts with "="), Access gives "Circular Reference" errors because it thinks you're asking it to calculate the value of the control, based on the value of the control.

    This behaviour is very confusing for beginners (who are, after all, generating their forms and reports in a "default" or "approved" manner, and subsequently performing very minor modifications to those forms/reports in accordance with the product's own help files), and perennially irritating for veteran Access developers who are forced to rename large numbers of controls (thus reducing one of the key advantages of using Access for rapid application prototyping in the first place).

    This could very easily be resolved, permanently and without any impact on backwards compatibility, if only Microsoft would change the default settings of MS Access so that new form/report controls would be generated with a name corresponding to the bound field name PLUS SOME MORE TEXT APPENDED ON THE END OF IT, such as "_ctl" or "_Ctrl". This would be very simple to do... And would greatly improve MS Access. I've been hoping for Microsoft to do this ever since Access '97... (I thought it was obvious and therefore not worth the mention, but perhaps not obvious since it's not been done by now.)

    Friday, June 10, 2011 1:18 PM

Answers

  • When placing controls on Forms/Reports (e.g. from the Design/ Add Existing Fields pane), the default Control name is identical to its bound field name.

    Thereafter, when using the field name in calculating the value to be displayed in the similarly named control (where "Control Source" starts with "="), Access gives "Circular Reference" errors because it thinks you're asking it to calculate the value of the control, based on the value of the control.

    This behaviour is very confusing for beginners (who are, after all, generating their forms and reports in a "default" or "approved" manner, and subsequently performing very minor modifications to those forms/reports in accordance with the product's own help files), and perennially irritating for veteran Access developers who are forced to rename large numbers of controls (thus reducing one of the key advantages of using Access for rapid application prototyping in the first place).

    This could very easily be resolved, permanently and without any impact on backwards compatibility, if only Microsoft would change the default settings of MS Access so that new form/report controls would be generated with a name corresponding to the bound field name PLUS SOME MORE TEXT APPENDED ON THE END OF IT, such as "_ctl" or "_Ctrl". This would be very simple to do... And would greatly improve MS Access. I've been hoping for Microsoft to do this ever since Access '97... (I thought it was obvious and therefore not worth the mention, but perhaps not obvious since it's not been done by now.)


    I don't speak for Microsoft, but I believe they set it up the way they did so that novice users don't (usually) need to understand the difference between a control and a field.  They can treat them interchangeably, and refer to the properties of a "field" on a form according to the name that they assigned to it when they designed the table.  If Access did it your way by default, then the users would lose that equivalence, and the results might be more troublesome than the difficulty posed by circular references when they create calculated controls.

    I would like to see an option that allowed the user to choose a control-naming convention to follow -- no prefixes or suffixes, standard prefixes (or suffixes, but prefixes are more common in the developer community), custom prefixes/suffixes.  I do think that it would be better for novice users to start with no prefixes or suffixes, the way it is now, but I understand the problem with turning a bound control into a calculated control.  One solution to that might be for Access to recognize the problem and offer to make an automatic adjustment.

    Personally, I find it convenient to let Access name all my bound controls for the fields to which they are bound.  Any unbound or calculated controls, I name with control-type prefixes.  Essentially, any control where I need to distinguish between the control and the field, I name with a prefix.  This system works for me, but may not work for everyone. 


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Proposed as answer by Bruce Song Friday, June 24, 2011 1:01 PM
    • Marked as answer by Bruce Song Monday, July 11, 2011 11:18 AM
    Friday, June 17, 2011 3:35 PM

All replies

  • p.s. Will you also please set up Access so that the default name for new ASSOCIATED LABELS is also comprised of the bound field name, with the text "_Label" appended to the end of it?

    If you could set up these default control name suffixes via the "Options/Object Designers" dialog, this would be even better.

    Friday, June 10, 2011 1:47 PM
  • You might find this Renaming Wizard interesting to use.

    http://www.acc-technology.com/rnwiz.htm

     

    HTH,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, June 10, 2011 5:56 PM
  • $34.95 USD is a lot of money for what I'm asking for (and in any case, the tool you are recommending does a different job to what I require). You are recommending an Access object refactoring (renaming) tool. Instead, I require the system in MS Access for generating default form/report controls (such as TextBoxes) to be amended slightly so that the default setting does not generate errors. It would be nice also to have some options to control the process.

    Access is ergonomically broken, since if you use the default configuration and follow the instructions for doing something relatively basic, you get errors that are not obvious for novices to analyse and resolve. Fixing this once and for all, for everyone, would be trivially simple and would cost Microsoft a few hundred dollars (spent ONCE for EVERYONE), and would also introduce no new issues re: backwards compatibility etc.

    @Microsoft, please?

    Friday, June 10, 2011 7:26 PM
  • You could write some code to loop through all of the controls on your form and rename them. kinda like this -

    Public Sub ControlRename(strFormName As String)
    Dim frm As Form, ctl As control, strSuffix As String
      
      Set frm = Forms(strFormName)
      
      For Each ctl In frm
        Select Case ctl.ControlType
          Case acTextBox
            strSuffix = "_txt"
          Case acComboBox
            strSuffix = "_cbo"
          Case acListBox
            strSuffix = "_lst"
          Case Else
            GoTo Skip_Control
        End Select
        
        If Right(ctl.Name, Len(strSuffix)) <> strSuffix Then
          ctl.Name = ctl.Name & strSuffix
        End If
    Skip_Control:
      Next
    
    End Sub
    



    Kent
    Saturday, June 11, 2011 3:32 AM
  • Naming Of Controls On Forms / Reports
    ============================
     
        This topic was examined in one of the forums, leading to emergence of collectively evolved guidelines for recommended practice. For general information, these guidelines have been placed at Steve Schapel's site. Link:
     
        For ready reference, a copy is also placed at the end of this post.
     
        As brought out in the guidelines, so long as required discipline is followed, either of the following alternatives should work smoothly:
     
        (a) Names of pure bound controls are kept identical to those of fields serving as their control sources. At the same time, it has to be ensured that names of other controls (unbound and calculated controls) do not tally with the name of any field in the record source.
     
        Or
     
        (b) Suitable prefixes are used universally so that in no case, the name of any control happens to tally with the name of any field in the record source.
     
        It is quite understandable that over the time, different individuals tend to develop their own preferences for the naming style to be followed.
     
        Certain interesting factors having a bearing on this subject are brought out below (as tested on Access 2003):
     
        1 - Forms - VBA module:
        --------------------------
        1.1 - No control with name matching that of a field is present on the form:
        --------------------------------------------------------------------------
        If a field name in record source is referred in VBA code, whether directly or with Me qualifier followed by dot, bang or parenthesis, and no control having this name is physically present on the form, the code refers directly to the field and value is the only property exposed by intellisense. Examples:
        (a) Me.MyFieldName
        (b) Me!MyFieldName
        (c) Me("MyFieldName")
        (d) MyFieldName
     
        1.2 - Bound control with name matching that of a field is present on the form:
        -----------------------------------------------------------------------------
        If a bound control having its name identical to the control source is actually present on the form and this name is referred in VBA code, whether directly or with Me qualifier (see (a) to (b) in previous para), it automatically refers to the control. Intellisense exposes all the properties for the control (e.g. Top, Left etc). Value (which was the only available property when pure field was being referred - as stated in previous para) becomes the default property in this case. In other words, a bound control having its name identical to control source acts somewhat like a super-incarnation of the field itself, with additional properties over and above the original Value property.
     
        1.3 - Unbound control with name matching that of a field is present on the form:
        ---------------------------------------------------------------------------------
        If an unbound control having its name identical to a field in record source, is present on the form and this name is referred in VBA code whether directly or with Me qualifier, it automatically refers to the control (not the field in record source).
     
        Note:
        ------
        As seen from 1.1 to 1.3 above, form control, if physically present, prevails over identically named field, if referred in VBA code using conventional syntax (see (a) to (b) in para 1.1 above)
     
        1.4 - Syntax for consistent foolproof reference to field in record source
        (Irrespective of the presence or otherwise of same name control on the form):
        ------------------------------------------------------------------------------
        Even if a bound control having its name identical to the control source is present on the form and it is desired that VBA code should refer only to the source field (not the control itself), it can be done by using the syntax:
     
        Me.Recordset("FieldName")
     
        Note:
        (a) Full form of above statement is:  Me.Recordset.Fields("FieldName")
        (b) The value returned by using Me.Recordset("FieldName") is always in synch with the current record (Absolute position of form's recordset matches current record). On the other hand, if RecordsetClone were used, that is not the case and additional code is needed to navigate to a particular record.
     
        2 - Forms and Reports - Calculated controls
        (having expressions using names of controls / fields):
        ----------------------------------------------------
        2.1 - In plain expression, if a control having the referred name exists physically, the control prevails even if there is a similarly named field in record source. If no such control exists on the form, the field in record source gets picked up.
     
        2.2 - If aggregate expression is involved (e.g. Sum() etc.), the field prevails even if a control with identical name exists.
     
        3 - Reports - VBA module:
        ----------------------------
        3.1 - Fields in record source are not amenable to direct reference via VBA code, using the conventional style (see (a) to (b) in 1.1 above). Only controls actually existing on the report are recognized in VBA. In such cases, it is the control that prevails.
     
        3.2 - For dependable reference to a field in the record source, the syntax suggested for forms in 1.4 above,  i.e. Me.Recordset("FieldName") does not work in reports (Error 2593). As a result, it is found necessary to use OpenRecordset method of CurrentDb (or DbEngine).
     
        3.3 - Certain peculiarities associated with reports VBA:
        (a) Me.RecordsetClone is not available in report module.
        (b) Although Me.Recordset is shown in intellisense, and it does compile, its use in VBA gives error 2593 (This feature is not available in mdb).
        (c) When a recordset is opened via CurrentDb's OpenRecordset method, it opens at first record. The benefit of automatic synchronization with current record (typical of form's recordset) is not available.
        (d) When Me qualifier is used in report's module, intellisense displays all fields featuring in record source. However these fields can not be accessed by any of the alternative styles (Me followed by dot, bang etc) mentioned from (a) to (d) in para 1.1 above, unless a control by that name is actually present on the report. In such case, it is the control that prevails over field.
        (e) Unlike forms, typical properties for controls (Top, Left etc) are not shown by intellisense in report's module. However, if used, there is no problem in getting the desired results.
     
    A.D. Tejpal
    -------------
     
    General Guidelines (Recommended Practice):
    Naming Of Controls With respect To Field Names
    ==================================

        1 - For controls referenced in code or expressions, the names should not be the default names such as "text13". It is best to modify the default name to something meaningful that eases maintenance of application.

        2 - An unbound or calculated control should not have its name identical to any of the fields contained in the record source. As a standing precaution, suitable prefix like Txt (or txt), Cbo (or cbo) etc should be used while naming such controls.

        3 - It is perfectly Ok if the name of a pure bound control is the same as its control source (unless, for some reason, you need a different name for the bound control - see para 4 below). It can also prove convenient in readily distinguishing the bound controls from others. However, in such an arrangement, care has to be exercised on following lines:
            (a) If the role of an erstwhile bound control bearing a name identical to its control source is subsequently changed to that of an unbound or calculated control, its name should be modified simultaneously (including suitable prefix as per 2 above) so as to differentiate it from field names.
            (b) If the control source of a bound control is subsequently changed, its name should be changed simultaneously, matching the new control source.

        4 - There is no objection however, to rename even bound controls, say by prefixing with Txt (or txt), Cbo (or cbo) etc. if the developer is keen to do so. In such a case, care should be taken to use the actual field name (and not the control name) while using the content in aggregate expressions in calculated controls e.g. =Sum([FieldName]). It would also be desirable to give meaningful names to such calculated controls e.g. txtTotal, or txtAvg
    etc.

        5 - For a control bearing the name of a particular field in record source, NEVER bind it to some other field.

        Note - In allowing the names of bound controls to be identical with the control source, if the developer is not confident enough that the discipline envisaged in para 3 (a) & (b) above would be strictly observed, it might prove safer to rename all such controls in the beginning itself, as per para 4 above. In order to make such a task less taxing, suitable tool like Arvin Meyer's FixNames utility (link given below) can be used if desired.
    http://www.datastrat.com/Download/FixNames2K.zip

    ============================================
     
    ----- Original Message -----
    Newsgroups: Msdn.en-US.accessdev
    Sent: Friday, June 10, 2011 18:48
    Subject: Default form/report control names cause circular reference errors

    When placing controls on Forms/Reports (e.g. from the Design/ Add Existing Fields pane), the default Control name is identical to its bound field name.

    Thereafter, when using the field name in calculating the value to be displayed in the similarly named control (where "Control Source" starts with "="), Access gives "Circular Reference" errors because it thinks you're asking it to calculate the value of the control, based on the value of the control.

    This behaviour is very confusing for beginners (who are, after all, generating their forms and reports in a "default" or "approved" manner, and subsequently performing very minor modifications to those forms/reports in accordance with the product's own help files), and perennially irritating for veteran Access developers who are forced to rename large numbers of controls (thus reducing one of the key advantages of using Access for rapid application prototyping in the first place).

    This could very easily be resolved, permanently and without any impact on backwards compatibility, if only Microsoft would change the default settings of MS Access so that new form/report controls would be generated with a name corresponding to the bound field name PLUS SOME MORE TEXT APPENDED ON THE END OF IT, such as "_ctl" or "_Ctrl". This would be very simple to do... And would greatly improve MS Access. I've been hoping for Microsoft to do this ever since Access '97... (I thought it was obvious and therefore not worth the mention, but perhaps not obvious since it's not been done by now.)


    A.D. Tejpal
    Saturday, June 11, 2011 4:57 AM
  • @KentGorrell: This is helpful to me (I just need to add a ribbon button to my development machine, with some macro code behind it), but not particularly helpful for novices who will remain confused.

    @A.D.Tejpal: Your instructions are relevant, comprehensive and of excellent quality, but the fact that your instructions needed to be so long only serves to reinforce my point. Which novice Access developer, trying out Access for the first or even second time, will know about your instructions, or where to find them? There will continue to be hundreds or thousands of people scratching their heads in confusion, until Microsoft fixes this by changing the defaults. Your opening remarks say everything that needs to be said: "so long as required discipline is followed"—i.e., everything is fine so long as I perform various additional (and entirely superfluous) administrative exercises, in order to manually fix something that is broken in Access, every time I create a new Form or Report. I appreciate your time and trouble, and admire your detailed explanation (a useful reference); but the only people who can fix this problem are those who work at Microsoft.

    This is a major problem for novices negotiating spurious error messages and making sense of the ambiguous object structure, a perennial irritation to veterans since Intellisense/ code completion takes some time (or prompting via a software restart) to recognise that Field/ Control names have been changed, and a minor problem for MS engineers to fix permanently. MS: Please fix this.

    Saturday, June 11, 2011 6:40 AM
  • Hi Matthew,

    Your suggestion is really a nice feedback and I can help you to submit to the Access product team. I am not very sure whether the product team will change this. Because if changed, it may impact a series of Access products from Access97 to Access2010 and a lot of users who are currently using it.

    I also think KentGorrell and A.D.Tejpal's suggestions are very helpful to this problem. As for novices, I think we can write some technical articles such as FAQ or educate them to let them know this scenario and avoid this problem.

    Hope this can give you the hint.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Tuesday, June 14, 2011 9:35 AM
  • Bruce,

    > "I can help you to submit to the Access product team"
    Excellent - please do this.

    > "if changed, it may impact a series of Access products from Access97 to Access2010 and a lot of users who are currently using it"
    There need not be any impact whatsoever on Access 97, 2000, XP, 2003, 2007 or 2010 if you change this. All I'm asking for, is for the default naming conventions for new controls on new forms to be changed so that following MS' own instructions doesn't generate confusing errors and force users to trawl through dozens of knowledge-base/FAQ articles just to get started with Access. I'm not asking for existing forms/ controls to be retroactively renamed (it would as you suggest break massive amounts of existing VBA code if MS did that). I'm asking for something very simple, very easy to do - with no impact whatsoever on existing products.
    Regarding your assertion that this change would affect "a lot of users who are currently using it".  I'm pretty sure that >95% of serious developers would be extremely happy with this change (and novices would be much more likely to make a successful start on MS Access, and not get frustrated and choose another product instead). If Microsoft wishes to take a more conservative approach to introducing this new feature, I would suggest for you to leave the defaults the same, but give developers the opportunity (in the OPTIONS/DESIGNERS dialogue) to change the default naming conventions themselves, so as to prefix/suffix db-field-names with specified text strings, in order to generate the default names for new controls being created for the first time (use feature usage analysis to prove my point, if you wish, before rolling out the new setting to everyone else). Implement it this way, and I'm absolutely sure that >99% of Access developers who actually notice the difference or know about the new feature, would consider this an improvement on the existing state of affairs.

    This problem is so serious as it stands, that it probably set me back months when I first started using MS Access in the mid 1990's. It was just too confusing. I wanted to use Access to solve my software problem, but there were so many idiosyncrasies like this one that I gave up and used something else instead; only returning to the Access ecosystem months later.

    SUMMARY:

    • EASY TO IMPLEMENT
    • NO IMPACT WHATSOEVER ON EXISTING ACCESS APPLICATIONS
    • POSITIVE IMPACT ON EXISTING ACCESS DEVELOPERS (with very little cost in terms of understanding the rationale behind the change)
    • VAST IMPROVEMENT FOR NOVICE DEVELOPERS
    If you value your new customers (and don't want them going over to FileMaker Pro or something else like that instead), I suggest for you to sit up and take notice.

    Matthew

    Friday, June 17, 2011 11:27 AM
  • When placing controls on Forms/Reports (e.g. from the Design/ Add Existing Fields pane), the default Control name is identical to its bound field name.

    Thereafter, when using the field name in calculating the value to be displayed in the similarly named control (where "Control Source" starts with "="), Access gives "Circular Reference" errors because it thinks you're asking it to calculate the value of the control, based on the value of the control.

    This behaviour is very confusing for beginners (who are, after all, generating their forms and reports in a "default" or "approved" manner, and subsequently performing very minor modifications to those forms/reports in accordance with the product's own help files), and perennially irritating for veteran Access developers who are forced to rename large numbers of controls (thus reducing one of the key advantages of using Access for rapid application prototyping in the first place).

    This could very easily be resolved, permanently and without any impact on backwards compatibility, if only Microsoft would change the default settings of MS Access so that new form/report controls would be generated with a name corresponding to the bound field name PLUS SOME MORE TEXT APPENDED ON THE END OF IT, such as "_ctl" or "_Ctrl". This would be very simple to do... And would greatly improve MS Access. I've been hoping for Microsoft to do this ever since Access '97... (I thought it was obvious and therefore not worth the mention, but perhaps not obvious since it's not been done by now.)


    I don't speak for Microsoft, but I believe they set it up the way they did so that novice users don't (usually) need to understand the difference between a control and a field.  They can treat them interchangeably, and refer to the properties of a "field" on a form according to the name that they assigned to it when they designed the table.  If Access did it your way by default, then the users would lose that equivalence, and the results might be more troublesome than the difficulty posed by circular references when they create calculated controls.

    I would like to see an option that allowed the user to choose a control-naming convention to follow -- no prefixes or suffixes, standard prefixes (or suffixes, but prefixes are more common in the developer community), custom prefixes/suffixes.  I do think that it would be better for novice users to start with no prefixes or suffixes, the way it is now, but I understand the problem with turning a bound control into a calculated control.  One solution to that might be for Access to recognize the problem and offer to make an automatic adjustment.

    Personally, I find it convenient to let Access name all my bound controls for the fields to which they are bound.  Any unbound or calculated controls, I name with control-type prefixes.  Essentially, any control where I need to distinguish between the control and the field, I name with a prefix.  This system works for me, but may not work for everyone. 


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Proposed as answer by Bruce Song Friday, June 24, 2011 1:01 PM
    • Marked as answer by Bruce Song Monday, July 11, 2011 11:18 AM
    Friday, June 17, 2011 3:35 PM
  • Dirk,
    Your comments are all excellent and if your viewpoint on bound controls is accurate, then I'd certainly be happy to settle for the compromise solution you've suggested. If you're happy with the current state of affairs, I'd suspect we are using different methods of referencing fields and controls from VBA (I usually reference controls using "Me.ControlName.ControlPropertyOrMethod" (no square brackets or apostrophes). Does this make a difference? [Might do some testing and remark on this.]
    Your system might also not work for me since I use a lot of Form Control Events on bound Forms (necessary for various technical reasons), and it's sometimes very important to me to distinguish between the Control, and the Field it is representing. I vaguely recall that there are subtle differences between Form.Control.Value, and Form.Field (value); in terms of:
    • Recordset locking / synchronization, Save Actions / transactions
    • Record "Dirty"-ness
    • Requirements for subsequent "Requery" Actions
    Different things happen if you assign a value to the Control.Value than if you assign a value to the Field. For many developers, that distinction can be quite subtle and important. For me, the devil really is in the detail - I've got to be able to distinguish between the two, or my software simply won't work (and will start throwing obscure Recordset error messages at my end-users). It would also be nice if code auto-complete would work with me, without me having to reboot my computer to prompt Access that I've renamed some Form Controls.
    I'm tempted to Mark As Answer since I think we're approaching a common viewpoint, but I shan't do that yet since if I did, Microsoft might ignore this thread. They need to take action on what we are both now recommending.

    Matthew Slyman M.A. (Camb.)
    Friday, June 17, 2011 9:57 PM
  • I am with Dirk in that I prefer the current behavior over a fixed prefix or suffix.  When I drag a Field from the field list, I want my controls named the same as the field.  If I want a control that will utilize an expression, I certainly do not begin its creation process by dragging a field on the form, I simply create the control of choice, then rename it a name that is appropriate with a prefix (in the VBA development world prefixes seem to rule the day, but in .Net it seems suffixes are prevalent).

    If anything were to change, I would like to see a property named "Control Prefix" and "Control Suffix" as properties on the Default <Control Type> property dialog.  So the properties would be along side the "Auto Label" and "Add Colon" properties.  As an addition, it would be nice to have the default control properties be global, and not form specific, but I understand why the collection is only at Form level and will likely not go beyond that.

    On a side note I think it is important to note that in bound forms, the fields are NOT referenced directly, unless you go through the Recordset property (or a variant like RecordsetClone or create a .Clone of either one).  MS Access creates a set of hidden controls (with a control type of "AccessField") that match the name, and map to, the fields of the bound data.  This is a type of voo doo magic employed by MS Access and bound forms.


    Brent Spaulding | Access MVP
    Saturday, June 18, 2011 3:59 AM
  • To be able to choose a prefix. Eg. Cmd for Buttons, would be nice.

     

    But I still wonder what code is causing the circular reference? I have no problem working with them as is so far.


    hrubesh
    Saturday, June 18, 2011 7:04 AM
  • > "If I want a control that will utilize an expression, I certainly do not begin its creation process by dragging a field on the form, I simply create the control of choice, then rename it"

    I understand how to do this (I also often create unbound controls from the toolbox palette) but sometimes I need a control that has all of the same Type, RowSource and formatting options that belong to a particular field, only, I want it to be an unbound Control or calculated Control instead, and I need some VBA code to manipulate it. In this case, it is easiest to generate the Control from the Existing Fields list. Or at least, it ought to be easiest that way, if not for the idiosyncrasies I'm trying to persuade MS to fix...

    > "in the VBA development world prefixes seem to rule the day"

    Perhaps true in general - but I write a lot of VBA code and find it easier (in terms of understanding big db tables and corresponding controls, and in terms of using code auto-complete) to have my Controls named with a suffix. There's got to be a reason the .NET world broke with Office/VBA tradition...

    > "Default <Control Type> property dialog..."

    This particular dialog or property sheet is more to do with formatting and labelling, than it is to do with the field/control name-space.

    It would be trivially easy for MS to add these new settings to the "File/Options/Object Designers/Form design view" dialog, so that they would apply globally, as most developers would hope.

    > "in bound forms, the fields are NOT referenced directly, unless you go through the Recordset property. MS Access creates a set of hidden controls... that match the name, and map to, the fields of the bound data..."

    Right. And it's the ambiguity caused by this "voodoo magic" that's causing my trouble with code auto-complete, and with circular references.


    Matthew Slyman M.A. (Camb.)
    Saturday, June 18, 2011 9:14 AM
  • @hrubesh,

    To reproduce the problem, create a table (table1) with two fields (id_field = Number PrimaryKey, string_field = Text(32) or something like that).

    Produce a Form (Form1) based on table1. Create Controls for id_field and string_field if Access has not already done so.

    Now select string_field. Visit the Property Sheet/ Data tab. Change the "Control Source" field so that it reads as follows:

     

    =string_field & ": helloworld"
    

     


    Access will helpfully enclose "string_field" in square brackets, with no reference to Form or Controls or anything; which implies (from my perspective, and possibly from the perspective of anyone else who is used to working with other RDBMS systems) that the Field is being referenced rather than the Control.

     

    =[string_field] & ": helloworld"
    

    Now ensure that the "string_field" Control is selected, and observe the little green triangle in the top-left of the control, and the diamond-shaped warning/ information sign to the left of the control. Click on the diamond-shaped warning, and Access will tell you (somewhat helpfully, although somewhat confusingly for Access beginners) that you have a circular reference leading to an "Invalid...Control Source".

     

    In fact, the Control Source is not (in and of itself) "Invalid". The true problem is caused by the Control having the same name as the db Field from the underlying Recordset (see datAdrenaline's explanation about Recordsets and "voodoo magic" for more information about that). Both Control and dbField are named, "string_field", with no prefix or suffix to distinguish them from each other. There's an ambiguity in the name-space that can easily be resolved by renaming the Control to something like,
    "string_field_Ctl"
    So in other words, Access thinks your formula (as above) for the contents of the "string_field" Control, is referencing the "string_field" Control in an infinite loop. Only, you weren't trying to reference the Control, you were trying to reference the Field.

    So you sometimes really need to be able to distinguish between Field and Control. This can also be important in VBA code, for reasons I've explained previously. Only, renaming Controls after generating the Form's VBA module can lead to issues with code auto-complete (as previously explained).

    So you need to rename the Controls, and reboot Access (or perhaps even Windows). Only, renaming dozens or hundreds of Controls is a big pain.

    Still confused? You're not the only one. There will be others out there.

    @datAdrenaline, it might be considered "voodoo magic" or "indirect referencing", for MS Access to reference Recordset/Form-bound db Fields in this way; but have you tried explicitly referencing the Field via the Recordset or Recordsetclone object, within the "Control Source" box on the Control Property Sheet? If you can do it, please show me a way... If you cannot do it, then clearly, the existing state of affairs is ergonomically wrong - and the best way to fix it is to make it more explicitly obvious whether you're dealing with the Field or the Control (and not leave novice Access developers battling with "voodoo magic".)


    Matthew Slyman M.A. (Camb.)
    Saturday, June 18, 2011 9:42 AM
  • There is one other request I wish to make - someone in this thread (@datAdrenaline, I think) has mentioned associated Labels.

    Besides changing the default naming system for new Controls, I would like new associated Labels to be named in a way that reflects their association with the Control they are (by default) attached to. (This would help, for instance, with VBA code that manipulates Label Properties as well as Control Properties).

    So for example (using my own naming conventions), if I create a new Control based on a db Field called "string_field", I would expect the Control to be named:

    string_field_Ctrl

    ...(don't forget, you can find out the Control Type using VBA methods) and I would expect its associated Label to be called:

    string_field_Label


    Matthew Slyman M.A. (Camb.)
    Saturday, June 18, 2011 9:47 AM
  • Thanks,

    I have seen what you mean and in fact, I had this problem few weeks ago, while I was doing a rapid prototype in front of my manager.

    I could not know what problem was happening, we stopped looking at the screen and continued the explanation verbally.

    Personally, I take it as a lack of experience from my part.

     

    In the long run, once I start concrete versions of my projects, I do write proper names for important controls. I do have few that are left as-is, but in the long run, as the versionning of my software is updated, I catch up with all these 'mistakes'.

    I have Txt prefix before textboxes and Cmd before buttons for example.

     

    I am sure there is an explanation for this implementation from MS, there are pros and cons from different parties, yet these evolve when one specialises and sticks to programming principles.

     

    My opinions

    1.Learning can be easy when controls have the same name as table fields when simple forms are done.

    2.Learning can be hard when controls have the same name as table fields when more complex calculations are done.

    a. By the time people have started doing complex calculations, they should be applying good naming conventions.

    b. In rapid development scenarios, (like what happened to me) this can cause time lag.

    3. It would be nice to have the options to add prefix or suffix to the autogeneration module. We could see it in a near future in another version :)

    4. I have sometimes realised that when starting to use a program gradually, well structured from beginning to end, all bits and pieces are known in time. When trying to achieve an end result directly, I usually suffered what I call information blindness. The answer was in the lines, but trying to put it all together at one time, I'm blinded. To this, I am thankful for what is available, knowing each and everyone is doing their best to provide the best, I may sometimes think to be ahead of time, yet I realise they've also been there, and the time they catch up with it, it will all be fine :)

    5. I see myself in this situation where I see people ask many things that I have planned long time, yet the time is not now. In the current program I am working with, there are 50 improvements I can bring. Yet it is not the time, because I have to change the core of the system. I have seen that the core could be improved to increase productivity. Once this is done, then I would think of the other improvements. In the meantime, the other people have no idea of what I am doing!:) But I appreciate they ask for the things that I know I have to do. It keeps me motivated that: They are waiting for me!:)

     

     


    hrubesh
    Saturday, June 18, 2011 1:36 PM
  • >>There's got to be a reason the .NET world broke with Office/VBA tradition...<<

    Well, no ... there really does not have to be a reason, other than to distinquish from something else.  I do know that the object oriented languages lend themselves to not have prefixes simple because there as a developer, you are not dealing with the standard types all that much.  Plus, there is a whole new set of "norms" in .Net and oo programming.

    >> This particular dialog or property sheet is more to do with formatting and labelling.... <<

    It is my opinion that your above statement is precisely the reason a default control prefix or suffix should be a part of the collection of properties offered by this dialog.

    >> It would be trivially easy for MS to add these new settings to the "File/Options/Object Designers/Form design view" dialog <<

    I would not consider your suggestion to be trivial since at lease two new settings would have be added for each control type offered for forms and reports (ie: Text Box Prefix, Text Box Suffix), then you have the complication of the SubForm/Report control, if you are like me, you have a different prefix (suffix) depending on the parent (ie: subfrmMySubFormControlName vs subrptMySubReportControlName).  With that in mind, now MS would have to add two more settings for that one control type.  Then code would have to be added to analyze whether the control's parent will be a Form or Report object in order to decide which setting to use.  So, I would not consider your suggestion trivial at all.

    The advantage of using the Default Control settings is that the Access programmers already have a base class (and sub-class structures) to work with and Access UI objects that are already in place.  Adding two properties to the base class and dealing with those properties upon control creation on a form would be far less work and yeild the same result we are after, with the exception of the setting being global.  But, to work around that, you can create a blank form and/or report object that all others should be based upon.  Then, indicate those object names in the respective Form and Report template Object Designer settings of the database file.

    With the control default method of implementation I am supporting, you could even import those template objects with each new project in order to prevent having to duplicate your efforts of setting the control defaults.


    Brent Spaulding | Access MVP
    Sunday, June 19, 2011 4:54 AM
  • > "...at lease two new settings would have be added for each control type offered for forms and reports..."

    Not everyone uses different prefixes/ suffixes for different types of controls. Personally I find that naming convention to be a bit redundant and pedantic, considering that one might change one's mind about what sort of control to use, and considering that there are simple ways within VBA to query the type of control one is looking at within a Form.Controls Collection. As described in my original posting, I use the suffix, "_Ctrl", with all my controls—sometimes including subforms.

    I'm happy though as long as Microsoft's solution to this problem:

    • Is simple for all developers to understand and implement;
    • Removes the existing barriers for novice developers who are learning MS Access for the first time.

    Matthew Slyman M.A. (Camb.)
    Tuesday, June 21, 2011 4:39 AM
  • A potentially related discussion (about a different issue, where aspects of this issue are discussed):

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/4e624497-0bd6-4ab9-8d32-9ca63df3435f


    Matthew Slyman M.A. (Camb.)
    Wednesday, June 29, 2011 5:57 PM
  • @Bruce Song, this is only the "Answer" if Microsoft intends to take up Dirk's recommendations:

    I would like to see an option that allowed the user to choose a control-naming convention to follow -- no prefixes or suffixes, standard prefixes (or suffixes, but prefixes are more common in the developer community), custom prefixes/suffixes. I do think that it would be better for novice users to start with no prefixes or suffixes, the way it is now, but I understand the problem with turning a bound control into a calculated control. One solution to that might be for Access to recognize the problem and offer to make an automatic adjustment.

    Do you plan to do this?

    As regards Dirk's other comments:

    Personally, I find it convenient to let Access name all my bound controls for the fields to which they are bound. Any unbound or calculated controls, I name with control-type prefixes. Essentially, any control where I need to distinguish between the control and the field, I name with a prefix. This system works for me, but may not work for everyone.

    Dirk is right. This doesn't work for everyone. One reason this doesn't work is because the Access development environment is often not very clever when you start renaming fields and controls. If you start with a simple bound control, write some VBA code and then decide to set a ControlSource to a calculated value; you sometimes need to refactor (carefully find & replace) names within your VBA code. And if you rename db fields, you can run into this problem:

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/4e624497-0bd6-4ab9-8d32-9ca63df3435f

    So you're better off starting on the right foot. You're better off having some more sensible defaults. Perhaps these two issues are both part of the same big discussion...

    @Bruce Song?


    Matthew Slyman M.A. (Camb.)
    Wednesday, June 29, 2011 6:03 PM
  • Hi Matthew Slyman,

    I will surely help you submit your feedback to the product team and take up Dirk's recommendations. Each feedback will be highly evaluated by the product team and then decide whether to fix it. I also hope that they can adopt your suggestion, but I can't absolutely guarantee that it will be fixed or fixed in a short time. As long as they begin to fix it, I will let you know at the first time.

    Thank you for your valuable feedback again!

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Thursday, June 30, 2011 8:07 AM