• Tabbing between subforms

    Author
    Topic
    #461303

    Hi,

    I have multiple subforms on a parent form. I want to be able to tab between the subforms, so that when the user tabs off the last control on a form they automatically go to the first control on the next subform. I’ve tried referring to the form in code but end up with an error stating “object required” when I use this code:

    sfrmStudentDemographics!Gender.SetFocus

    If I use forms!sfrmStudentDemographics!Gender.SetFocus
    I get an error stating that sfrmstudentDemographics can’t be found. I’ve checked in properties to be sure that the sfrm name is the same as what I’ve typed and it is. When I try starting the code with me.sf…………. to see if the name comes up in the list it doesn’t come up. I’m not sure what I’m missing.

    Thanks,
    Leesha

    Viewing 3 reply threads
    Author
    Replies
    • #1169747

      Hi,

      I have multiple subforms on a parent form. I want to be able to tab between the subforms, so that when the user tabs off the last control on a form they automatically go to the first control on the next subform. I’ve tried referring to the form in code but end up with an error stating “object required” when I use this code:

      sfrmStudentDemographics!Gender.SetFocus

      If I use forms!sfrmStudentDemographics!Gender.SetFocus
      I get an error stating that sfrmstudentDemographics can’t be found. I’ve checked in properties to be sure that the sfrm name is the same as what I’ve typed and it is. When I try starting the code with me.sf…………. to see if the name comes up in the list it doesn’t come up. I’m not sure what I’m missing.

      Thanks,
      Leesha

      This is a 2-step process. The first step is to move the focus to the other subform, then set the focus within the subform. So from your first subform, you need 2 lines of code:

      Parent.sfrmStudentDemographics.SetFocus
      Parent.sfrmStudentDemographics.form!Gender.setFocus

      Oh BTW, I’m assuming the sfrmStudentDemographics is the name of the control on the mainform that contains the 2nd subform; the actual name of the subform it contains is irrelevant here.

    • #1169753

      Mark has already provided the solution.

      The reason that forms!sfrmStudentDemographics fails is that a subform is not part of the Forms collection. Only “main” forms are members of the Forms collection. Parent refers to the main form of the subform; it has the advantage that you don’t need to specify the name of the main form explicitly.

      • #1169798

        Back again. It worked fine for the tabbing between the first subform to the second subform. Now when I go to tab between the second subform to the third (there are three on the sheet) using the following code:

        Parent.sfrmStudentReferral.SetFocus
        Parent.sfrmStudentReferral.Form![Referral Date].SetFocus

        I get an error stating the value that I entered is not correct for the field. All I’m doing is setting the focus on the field. It is a date field set to short date. There is data already in the field. I tried setting the focus to a different control on the subform but get the same error.

        Second question. When I load the form, I want the focus to go to a control on the first subform. I tried the same code butgot an error.

        Thanks
        Leesha

        • #1169799

          Does the Referral Date text box have an input mask? If so, does it make a difference if you remove the input mask?

          If you want to set focus to a control on the first subform when loading the form, you must use the On Load event of the main form. You can’t use Parent there, obviously since the main form doesn’t have a parent. The code should look like this:

          Code:
          Private Sub Form_Load()
            Me.sfrmFirst.SetFocus
            Me.sfrmFirst!txtSomething.SetFocus
          End Sub

          where sfrmFirst is the name of the first subform as a control on the main form, and txtSomething is the name of the control on that subform that should get the initial focus.

          • #1169807

            I had the code in the on load section but it was written wrong. Your explanation makes sense as to why it wasn’t working.

            Re the control on the subform, no there are no input masks on that control or any of the other ones that I tried. All I’m trying to do is set the focus to the control.

            Leesha

            • #1169827

              Re the control on the subform, no there are no input masks on that control or any of the other ones that I tried. All I’m trying to do is set the focus to the control.

              I’d have to see a stripped down and zipped copy of the database to investigate why it doesn’t work.

            • #1169839

              Here you go!

            • #1169843

              The problem is the combo box cmbGender. It is bound to the text field Gender, and the record for Jane Doe contains “Male” (she had a gender change?)
              The row source of the combo box, however, is a SQL statement based on tblGender, with the number field GenderID as bound column. So Gender should be a number field, and it should contain 1 or 2, not “Male” or “Female”.
              If you apply this change, the error message will go away.

              See the attached version.

            • #1169851

              Thanks!! I would never have found this!

              Leesha

            • #1169847

              Here you go!

              Get out of the habit of using the On Exit event to move the cursor like this. It doesn’t work well. A better choice is to create a transparent Command button and put the same code in its GotFocus event. Such that when you tab out of the Notes field, focus goes to the Command button, which then transfers it to other subform.

              Some other thoughts:

              Why do you have a subform for the Student Name? This should be on the main form.

              And get out of the habit of using spaces in field names. It will make your life easier.

              And you should try to rename your form controls so they aren’t the same as the control source. A good idea is to use a standard prefix that describes the control, like txt, chk, cbo, etc. So instead of a control named “LastName”, you would rename it to “txtLastName”.

              But more serious problems exist in your database design. You haven’t defined any relationships between tables. Your Student Edit form seems to indicate there can only be a single Demographics record and a single Referral record for each student. Yet your database design seems to indicate you can have several. If in fact you can only have a single record for each, you SHOULD NOT have separate tables for this data! It should all be part of the Student table.

              And although not required, it is usually a good idea to define a Primary Key for each table.

            • #1169852

              Thanks Mark for the pointers. This is a very old database built by someone else and that I’ve been “tweaking” for years. I’m in the process of trying to redo some of their forms. Many of the things you pointed out I do do on one that I build from scratch, however I’ve never renamed controls as you suggest. I’d be interested to know the rationale for this if you have a minute?

              Thanks,
              Leesha

            • #1169854

              I agree with almost all Mark’s recommendations, but I’m not convinced of the merit of giving controls different names from the fields they’re bound to, even though it’s a recommendation that is often given on Access-related sites. The idea behind it that you can distinguish between a field in the form’s record source (e.g. LastName) and the control bound to it (e.g. txtLastName). This seems handy in theory, but in practice I’ve never encountered a situation where it was really needed.

              When I started to develop Access databases, I encountered this recommendation, and experimented a bit with it by creating two versions of a form: one where all bound controls had the same name as their control source, and another one where bound controls were meticulously renamed. Neither performed better or worse than the other. So I gave up on it, and have happily left the names of bound controls the same as their control source ever since…

              I have one other recommendation: several of the tables have duplicate indexes, for example the primary key and another index on the same field. That other index isn’t necessary, it takes up space and it may have a negative impact on the performance of the databas, since Access has to keep two indexes up-to-date instead of one. So I’d remove those (as in the version that I attached).

            • #1169855

              Thanks Hans! I would gladly start doing as recommended if it meant a decrease in issues, but wasn’t sure that it was necessary and seemed like such a time consuming task. When I add new controls I already do identify them as txt……, cmb……….. etc.

              >>I have one other recommendation: several of the tables have duplicate indexes,

              You can’t believe what I started working with on this. Every table required that the student name be entered etc. and there were tons of duplicate info in each table. It’s impossible to set up referential integrigty between the tables at this point as there are so many blank fields that were never filled in over the years. Instead I’m trying to hard code for required info. I’d prefer to totally rewrite the database but the user doesn’t have the funds for that, so I just keep tweaking. A friend of mine once said “if its not broke doen’t fix it” so since the database does work, I take some solice in that. I do however appreciate all of your and Marks suggestions for building a better database, as this is always a learning experience for me!
              Thanks!
              Leesha

            • #1169857

              … but I’m not convinced of the merit of giving controls different names from the fields they’re bound to, even though it’s a recommendation that is often given on Access-related sites. The idea behind it that you can distinguish between a field in the form’s record source (e.g. LastName) and the control bound to it (e.g. txtLastName). This seems handy in theory, but in practice I’ve never encountered a situation where it was really needed.

              Hans and Leesha,

              Admittedly, it is one of those things that probably won’t matter the vast majority of the time, except for that one time it does matter and then you will probably never figure it out! Basically, as I understand it, it is possible for Access to have to guess which one you are referring to in a given situation, and it might pick wrong. I know I have hit a few times when it did matter (I can’t remember the exact situations), so I’ve just made this part of my programming standards.

              My reason for passing this along is the source of the tip. Back in the heyday of the old Access forum on Compuserve, we had weekly on-line conferences. Frequent attendees were guys like John Viescas (author of the “Running Microsoft Access” series) and Joe Celko (of SQL fame). One week Ken Getz (co-author of the “Access Developer’s Handbook”) was the special guest. I was an Access newbie at the time and I asked this very question. He responded unequivocally “YOU SHOULD ALWAYS RENAME YOUR CONTROLS” (his emphasis, not mine).

              So considering the source (and that it is not just folklore), I have taken this as gospel ever since.

            • #1169859

              I don’t doubt the authority of people such as Ken Getz, but I suspect that this was important in early versions of Access, and less so in later versions. Of course, it doesn’t hurt to rename bound controls, but it’s a lot of work, with (in my experience in recent versions of Access) little or no benefit.

            • #1169881

              I don’t doubt the authority of people such as Ken Getz, but I suspect that this was important in early versions of Access, and less so in later versions. Of course, it doesn’t hurt to rename bound controls, but it’s a lot of work, with (in my experience in recent versions of Access) little or no benefit.

              I don’t disagree, but I still do it. Personally I find it makes things a little easier for me, makes code easier to understand, etc. It is admittedly a little extra work, but for me it is worth it.

            • #1169957

              It has been interesting to read this discussion about the names of controls. I too have read somewhere that you ‘should’ rename all controls, but I don’t usually bother.
              I sometimes rename a few, but I have never encountered any problems.
              I have always felt a guilty about being lazy, as if this was something I really should do.
              If you cut and paste controls, they get names like Label53, Text57, combo59 etc. These names work OK, but are a real nuisance when setting the Tab Order, or using the Expression Builder.

              The one time it matters (only on reports from memory) is when you add a control, say RecordID, then change the control source to =Sum([RecordID]) or =Count([Recordid]).
              Unless you change the name to txtRecordID (or anything else) Access gets confused by the circular reference.

            • #1169958

              … If you cut and paste controls, they get names like Label53, Text57, combo59 etc. These names work OK, …

              I do always give text boxes, combo boxes etc. meaningful names if they don’t/can’t have the same name as their control source. It makes maintenance and writing VBA code a lot easier. I might be more lazy with controls such as lines.

    • #1169795

      Thanks Mark for the sample and Hans for the additional education. I would’ve replaced “parent” with the name of the parent form.

      Worked beautifully,

      Leesha

    • #1170035

      I have also found that I often times need to rename controls in queries when using formulas as I will get ciccular reference errors. I learned the hard way to make sure to rename anything I cut and past because over time I fail to remember what the control is when referrring to it in code and waste more time going back to find it than if I just renamed it.

    Viewing 3 reply threads
    Reply To: Tabbing between subforms

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: