Results 1 to 11 of 11
  1. #1
    JohnLute is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    6

    Setting Focus on Control in Continuous Subform


    I have a parent form with a combobox. The parent form has a continuous style subform with a combobox. The subform will have only either one or two records related to the parent form.

    The after update event of the parent form’s combobox checks if the combobox in the subform has a value or not. If not then set focus on the subform and its combobox. If there is a value then set focus on the parent form’s combobox.

    I need to tweak the after update event to handle setting focus on the combobox of the SECOND or next record in the subform if the first record’s combobox has a value. If BOTH comboboxes in BOTH records of the subform have values then set focus on the parent form’s combobox.

    I’m only now getting back into Access after having been away from it for about 12 years so I am LOST as to how this can be achieved. I’m hoping this might make sense to someone who may have the skills to show me the way.

    Simple after update event needs a lot of work to achieve the desired effect:


    Code:
    Private Sub ParentCbox_AfterUpdate()
    
    If Forms![frmParent]![sfrmChild]![ChildCbox].Value <> 0 Then
    
            Me.ParentCbox.SetFocus
    
                Else
    
            Forms![frmParent]![sfrmChild].SetFocus
    
            Forms![frmParent]![sfrmChild]![ChildCbox].SetFocus
    
    End If
    
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Is it possible for two records in subform without data in specific field for both?

    Need to know if there is a second record to even look at and whether either of subform records has value in field of interest. Have textboxes in subform that count total records and how many have data. These controls can be not visible if you want.
    =Count("*")
    =Count([field subform combobox is bound to]).
    Code:
    Private Sub ParentCbox_AfterUpdate()
    With Me
    If .sfrmChild!tbxCount = .sfrmChild!tbxCountData Then
        .ParentCBox.SetFocus
    Else
        .sfrmChild.SetFocus   
        .sfrmChild!ChildCbox.SetFocus
        DoCmd.GoToRecord , , acLast
    End If
    End With
    End Sub
    Does seem like an odd requirement. If you want to provide db for analysis, follow instructions at bottom of my post.
    Last edited by June7; 07-03-2022 at 03:04 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I would probably just go to the last record in the subform?

    Code:
    Forms!frmEmails!sfrmEmails.SetFocus
    DoCmd.RunCommand acCmdRecordsGoToLast
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    JohnLute is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    6
    Quote Originally Posted by June7 View Post
    Is it possible for two records in subform without data in specific field for both?

    Need to know if there is a second record to even look at and whether either of subform records has value in field of interest. Have textboxes in subform that count total records and how many have data. These controls can be not visible if you want.
    =Count("*")
    =Count([field subform combobox is bound to]).
    Code:
    Private Sub ParentCbox_AfterUpdate()
    With Me
    If .sfrmChild!tbxCount = .sfrmChild!tbxCountData Then
        .ParentCBox.SetFocus
    Else
        .sfrmChild.SetFocus   
        .sfrmChild!ChildCbox.SetFocus
        DoCmd.GoToRecord , , acLast
    End If
    End With
    End Sub
    Does seem like an odd requirement. If you want to provide db for analysis, follow instructions at bottom of my post.
    Thanks, June7 for your engagement and help! This is very helpful but I see I need to provide more details. Your question: Is it possible for two records in subform without data in specific field for both? Yes. The subform records represent existing events waiting to be completed. A parent record may have one or two existing events. These events become completed after a user scans their name into them resulting in a timestamp being generated. I hope this helps describe the design a little better.

    If a parent record has ONE subform event, then the code I use now works just fine. When a parent record has TWO subform events then my code sets focus on the first record in the subform - if the value in the subform's field of interest is NULL. To be more clear on the desired effect: check if there are two records in subform. If so, then set focus on FIRST record that is NULL. If BOTH records are NOT NULL then set focus to parent form control.

    In your example, I'm seeing help setting focus on the last record. This is desired but only if the first record is NOT NULL.

    When I try counting comboboxes then Access returns #Error however when I try counting the field that links the parent form with the subform, then the count returns the correct value. I get either a "1" or a "2". With this count what I'm seeing is this:
    - If count is 1 AND subform Cbox = NULL then set focus on subform Cbox ELSE (record is NOT NUL) set focus on parent Cbox.
    - If count is 2 set focus on next NULL subform Cbox ELSE (both records are NOT NULL) set focus on parent Cbox.

    Yes, this is an odd requirement! It involves users using a hand-held scanner to scan an existing list of values. I'm trying to automate things so that they scan for a value and then scan their name into the subform's existing events without having to use the keyboard to manually move the cursor.

    I hope this helps clarify! I'm feeling good about the progress!

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You don't Count comboboxes, you Count the field.

    Try:

    If .sfrmChild!tbxCount = 2 And Not IsNull(.sfrmChild!ChildCbox) Then DoCmd.GoToRecord , , acLast
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    JohnLute is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    6
    Quote Originally Posted by June7 View Post
    You don't Count comboboxes, you Count the field.

    Try:

    If .sfrmChild!tbxCount = 2 And Not IsNull(.sfrmChild!ChildCbox) Then DoCmd.GoToRecord , , acLast
    Thanks, June7! I've been tinkering with just the "1" condition. It's not working and I suspect it's because the count of the subform is completed AFTER the After Update event has fired. I've included my After Update event code with actual control names. Again, this After Update event is for the Parent form's control named [cbLOCID].

    I'm noticing that the subform's count control [CountLOCIDS] is populating AFTER the After Update event of [cbLOCID] has fired.

    Any ideas how to resolve?


    If Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs] = 1 And Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value <> 0 Then
    Me.cbLOCID.SetFocus
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = False
    ElseIf Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs] = 1 And Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value = "" Then
    Forms![frmSCANLOCIDS]![sfrmLOCIDS].SetFocus
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = True
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].SetFocus
    End If

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No, I don't understand why this happens. Want to provide db for analysis?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    JohnLute is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    6
    Quote Originally Posted by June7 View Post
    No, I don't understand why this happens. Want to provide db for analysis?
    I'm happy to offer my db however it would require some tweaking to share as it contains some user interface functionality. I'm unable to do this at this time but maybe in the next couple days.

    I tried tweaking the code to isolate things. I flipped the IF's around to put the check of the count later. This didn't help. To test if my guess about the subform's count populating AFTER the parent form's combobox After Update event - I simply changed the code to look for a null value in the count (below). Bingo! When it looks for null then the code finds null and sets focus accordingly. So this to me confirms my guess that the count is happening AFTER the event looks at it.

    I like this count solution however would you know of anyway to "delay" the after update event in order to allow the count in the subform to populate?

    If IsNull(Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value) = True And Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs] = "" Then
    Forms![frmSCANLOCIDS]![sfrmLOCIDS].SetFocus
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = True
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].SetFocus
    End If

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Try moving code that decides whether or not to move to a second record into the subform. Or possibly the subform container OnEnter event.

    I try to have objects manage their own behavior when possible.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    JohnLute is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    6
    Quote Originally Posted by June7 View Post
    Try moving code that decides whether or not to move to a second record into the subform. Or possibly the subform container OnEnter event.

    I try to have objects manage their own behavior when possible.
    This is weird. This morning, on a different computer using same OS and Office version - everything is working ok! Go figure. I'll need to test again on previous computer. Maybe something was hung-up in cache - who knows.

    The following works however, it still needs tweaking when the value is 2 so that I can set focus on the record that has a Null cbCleanedBy value. To do this, the if statement also needs to check if the FIRST record in the subform has a Null cbCleanedBy value. Would you know how I might I add a check like this to this line:
    If Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs].Value = 2 And IsNull(Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value) = False Then

    Thanks for all your encouragement!

    If Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs].Value = 1 And IsNull(Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value) = False Then
    Me.cbLOCID.SetFocus
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![LOCType].Enabled = False
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = False
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![DateTime].Enabled = False
    ElseIf Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs].Value = 1 And IsNull(Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value) = True Then
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![LOCType].Enabled = False
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = True
    Forms![frmSCANLOCIDS]![sfrmLOCIDS].SetFocus
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].SetFocus
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![DateTime].Enabled = False
    ElseIf Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs].Value = 2 And IsNull(Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value) = False Then
    Me.cbLOCID.SetFocus
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![LOCType].Enabled = False
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = False
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![DateTime].Enabled = False
    ElseIf Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs].Value = 2 And IsNull(Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value) = True Then
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![LOCType].Enabled = False
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = True
    Forms![frmSCANLOCIDS]![sfrmLOCIDS].SetFocus
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].SetFocus
    Forms![frmSCANLOCIDS]![sfrmLOCIDS]![DateTime].Enabled = False
    End If

  11. #11
    JohnLute is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    6
    Well, I cleaned up a few things and arrived at the following. It's been working consistently so I'm calling this one resolved. Thanks for the Count idea!

    Code:
        If Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs].Value = 1 And IsNull(Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value) = False Then
            'Do nothing
            Me.cbLOCID.SetFocus
            Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = False
            Exit Sub
        ElseIf Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs].Value = 1 And IsNull(Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value) = True Then
            Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = True
            Forms![frmSCANLOCIDS]![sfrmLOCIDS].SetFocus
            Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].SetFocus
            Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].BackColor = vbYellow
            Exit Sub
        ElseIf Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs].Value = 2 And IsNull(Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value) = True Then
            Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = True
            Forms![frmSCANLOCIDS]![sfrmLOCIDS].SetFocus
            Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].SetFocus
            Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].BackColor = vbYellow
            Exit Sub
        ElseIf Forms![frmSCANLOCIDS]![sfrmLOCIDS]![CountLOCIDs].Value = 2 And IsNull(Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value) = False Then
            Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = True
            Forms![frmSCANLOCIDS]![sfrmLOCIDS].SetFocus
            DoCmd.GoToRecord , , acLast
            Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].SetFocus
            Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].BackColor = vbYellow
                If IsNull(Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Value) = True Then
                'Do nothing
                Exit Sub
                Else: Me.cbLOCID.SetFocus
                Forms![frmSCANLOCIDS]![sfrmLOCIDS]![cbCleanedBy].Enabled = False
                End If
            Exit Sub
        End If

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Setting Focus On Tab Control Page
    By DMT Dave in forum Access
    Replies: 3
    Last Post: 03-01-2022, 02:50 AM
  2. Setting the focus on a control in a subform
    By fritesmodern in forum Programming
    Replies: 1
    Last Post: 07-31-2015, 09:10 AM
  3. Replies: 6
    Last Post: 09-17-2014, 01:04 PM
  4. Setting the focus on a subform
    By Paul H in forum Forms
    Replies: 3
    Last Post: 01-10-2012, 09:17 AM
  5. Replies: 2
    Last Post: 09-12-2011, 11:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums