Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

    Error 2474 --The expression you entered requires the control to be in the active window

    I have a form with subform and subsubform. (employees, orders, orderDetails)
    I can delete subsubform entries (orderDetails from an Order)
    However, after I delete all subsubform entries for a subform, I am unable to delete the subform (order). I am getting error 2474 " The expression you entered requires the control to be in the active window."

    Anyone have suggestions to resolve this?



    Thanks in advance.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    how are you deleting? manually or with code?

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hi Ajax,
    I'm attaching additional info in zip.
    The jpgs UP1 ..UP4 show the forms and action.

    UP1 ----This shows tabbed form Employee and Subform Order and SubSubform Cafeteria Items with EmployeeID 8,OrderID 30 and DetailItems 84 and 85. Notice this is Order 2 of 2 for this Employee.

    UP2----I have highlighted both Item records and set my cursor in the record selector and hit Delete key on keyboard. I confirmed the delete request, but haven't yet moved off the subsubform.

    UP3---I have moved the cursor to the Order record selector and hit Delete key on keyboard. And received the 2474.

    UP4--- shows the vba code and the results of some debug.prints in the immediate window.

    In overview, I'm trying to get some audit code working with subforms as part of an ongoing assist to a poster. The original was for mainform only. I have it working to properly save log records(Add,Edit,Delete) for the OrderItems. It is the Order delete that is causing the issue. It works for Order Add and Edit.

    Note in the immediate window you'll see that the Order 30 was physically deleted. Also confirmed when I list records in TOrder. But I am not capturing the info to write the Order Delete details to the log.

    I have found references to 2474, but none seem to fit my issue.

    I think it has to do with the focus.

    Thanks for looking.
    Attached Files Attached Files

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I think the issue is when you deleted the record the screen.activecontrol became unassigned - in UP2, the whole new record has been highlighted, in UP3 you have selected the order record, but there is not an active control.

    Suggest put a

    debug.print screen.activecontrol.name

    before line 490 to test the theory

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I added the code as suggested

    Code:
    470     Next ctl
            'case DELETE added by jed to record values for fields in Deleted record
    480   Case "DELETE"
      Debug.Print "AJAX  " & Screen.ActiveControl.Name
    490     For Each ctl In Screen.ActiveControl.Parent.Controls
    
    500         If ctl.Tag = "Audit" Then
                    'ctl.SetFocus '<---attempt to debug 2474???
                    'If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
    510             With jtmpRST
    I then deleted the details (72,73) for Order 23.

    Immediate window is:
    Code:
    frmOrderSubSubform Form_Delete(Cancel As Integer)  gRecID is 72
    ORDERDetail Calling Audit to prepare to DELETE ID =72
    AJAX  Item
    frmOrderSubSubform Form_Delete(Cancel As Integer)  gRecID is 73
    ORDERDetail Calling Audit to prepare to DELETE ID =73
    AJAX  Item
    frmOrderSubSubform Form_AfterDelConfirm(Status As Integer)
    tmpAuditRec  was deleted 19-Apr-2017 11:33:37 AM
    ORDERDetail Record physically deleted ID 73
    FrmOrderSubform Form_Delete(Cancel As Integer)  gRecID is 23
    ORDER Calling Audit to prepare to DELETE ID =23 <<<------not going thru the Debug.print for the order???  
    Form_AfterDelConfirm(Status As Integer)
    tmpAuditRec  was deleted 19-Apr-2017 11:33:52 AM
    ORDER Record physically deleted ID 23
    Here is the latest. It doesn't appear to be going thru the DELETE action code for the Order record???

    Perhaps there is/are other options to either set the active control, or focus, or ???

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    perhaps the reporting for the line number for the code is wrong and actually the error is occuring somewhere else?

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Agreed. I'm using some basic code by Martin Green and trying to expand it to subform and subsubform. I think it is related to the activeForm etc. It isn't an area in which I have any real experience. Googling hasn't suggested anything that I can relate to my issue.
    I'll tr some additional searches.
    Thanks for looking and commenting.
    If you can think of anything that might apply, or a different approachetc., please advise.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    difficult without knowing which line is actually causing the error. The code you provided is part of a case statement - case what?

    not sure if you are using activeform - but it is always the main form, if on a subform, activeform is still the mainform.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    just answering a question on another forum and found you can also refer to activecontrol using the form object

    this generates an error when the form first opens (no active control error)

    Code:
    Function changeborder()
        screen.ActiveControl.BorderWidth = 5 
    End Function
    this doesn't

    Code:
    Function changeborder(frm As Form)
        frm.ActiveControl.BorderWidth = 5 
    End Function
    so if you are bringing the form object through to your function/sub, try referencing that instead of screen

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Quote Originally Posted by Ajax View Post
    difficult without knowing which line is actually causing the error. The code you provided is part of a case statement - case what?

    not sure if you are using activeform - but it is always the main form, if on a subform, activeform is still the mainform.
    Thanks ajax.
    I was doing some googling last night and found some info that led me to the fact that the activeform is always the main form.
    I added a function to a form event in order to see all of the controls.
    Here is a partial output
    Code:
    The following are the results of sub testActive 
     to review forms and controls 
    
    Main/Sub         Form        ControlName   ControlType
    
    MainControl(0)  FormTestAudit  TabCtl0  123  TabControl
    MainControl(1)  FormTestAudit  Employee  124  Page
    MainControl(2)  FormTestAudit  EmployeeId  109  TextBox
    MainControl(3)  FormTestAudit  Label3  100  Label
    MainControl(4)  FormTestAudit  LastName  109  TextBox
    MainControl(5)  FormTestAudit  Label4  100  Label
    MainControl(6)  FormTestAudit  FirstName  109  TextBox
    MainControl(7)  FormTestAudit  Label5  100  Label
    MainControl(8)  FormTestAudit  Gender  109  TextBox
    MainControl(9)  FormTestAudit  Label6  100  Label
    MainControl(10)  FormTestAudit  Title  109  TextBox
    MainControl(11)  FormTestAudit  Label7  100  Label
    MainControl(12)  FormTestAudit  HrlyRate  109  TextBox
    MainControl(13)  FormTestAudit  Label8  100  Label
    MainControl(14)  FormTestAudit  DepartmentId  109  TextBox
    MainControl(15)  FormTestAudit  Label9  100  Label
    MainControl(16)  FormTestAudit   Department  124  Page
    MainControl(17)  FormTestAudit  frmDepartmentSubform  112  SubForm
    -Subform name(1)  frmDepartmentSubform
        SubControl(1) DepartmentID  controlType 112  TextBox
        SubControl(2) DepartmentID_Label  controlType 112  Label
        SubControl(3) Department  controlType 112  TextBox
        SubControl(4) Department_Label  controlType 112  Label
    .....
    I'm still looking into things since ActiveControl and ActiveForm are used in the original audit trail material that dealt with a mainform and I am extending it to handle multiple levels of form/subform/subsubform.

    Thank you for the info.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Function changeborder(frm As Form)
    frm.ActiveControl.BorderWidth = 5
    End Function
    so if you are bringing the form object through to your function/sub, try referencing that instead of screen
    Ajax,
    Just noticed this post. Yes, it might be better to use the form names as parameter to the audit recording code. The developer of the original code was indicating his code used activeform and activeControl and did not need explicit form names/references.
    I have found issues with his delete case action and was trying to get it(the audit trail) more useful by including subforms, so this might be critical to making proper object references.

    Thanks for the info.

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    did you see my second post?

    Alternatively I have some code which identifies THE active form, even if it is a subform or a subform of a subform, but not sure if relevant to resolving your particular problem

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    it might be better to use the form names as parameter to the audit recording
    not sure if I'm taking this out of context, but the parameter is the form object not a string to it's name - otherwise you have the same problem with subforms

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Quote Originally Posted by Ajax View Post
    did you see my second post?

    Alternatively I have some code which identifies THE active form, even if it is a subform or a subform of a subform, but not sure if relevant to resolving your particular problem
    I'd like to see it. Sounds like that is the crux of the issue.
    And I did mean form not form name.

    Thanks again ---learning more each day.

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    here you go

    put in a separate module and use in your code something like

    dim frm as form
    frm=theActiveForm


    So you may need to adapt it in some way.

    Code:
    Option Compare Database
    Option Explicit
    'form management
    'does not require references or modules
    
    Function theActiveForm() As Form
    'finds and returns the active form object
    Dim frmActive As Form, ctlActive As Control
    Dim hWndParent As Long
        ' Clear the control variable.
        'theActiveForm = Nothing
        
        ' Assume a subform is not active.
        'Got_ScreenActiveForm = False
        
        ' Get the active form and control.
        On Error Resume Next
        Set frmActive = Screen.ActiveForm
        Set ctlActive = Screen.ActiveControl
        If err = 0 Then
        
            ' Get the unique window handle identifying the form the active control is on.
            hWndParent = ctlActive.parent.Properties("hWnd")
            
            ' If the active form window handle is the same as the window
            ' handle of the form the active control is on, then we are on the mainform, so exit.
            If hWndParent = frmActive.hWnd Then
                Set theActiveForm = Screen.ActiveForm
                'Got_ScreenActiveForm = True
            Else
                ' Find a subform control that has a window handle matching the
                ' .. window handle of the form the active control is on.
                Set theActiveForm = theActiveSubform(frmActive, hWndParent)
            End If
        End If
        
    End Function
    
    Private Function theActiveSubform(frmSearch As Form, hWndFind As Long) As Form
    'finds the active form if a subform
    Dim i As Integer
    
        On Error GoTo noSubform
        
        ' Visit each control on the form frmSearch.
        For i = 0 To frmSearch.Count - 1
            ' If the control is a subform control...
            If TypeOf frmSearch(i) Is SubForm Then
                ' .. does the window handle match the one we are looking for?
                If frmSearch(i).Form.hWnd = hWndFind Then
                    ' We found it! Set the global control variable and exit.
                    Set theActiveSubform = frmSearch(i).Form
                    Exit Function
                Else
                    ' Otherwise, search this subform control (recursively)
                    ' .. to see if it contains a sub-subform control
                    ' .. with a window handle matching the one we are
                    ' .. interested in.
                     
                    ' If we found a subform control, then exit.
                    If Not theActiveSubform(frmSearch(i).Form, hWndFind) Is Nothing Then Exit Function
                End If
            End If
        Next i
        
    noSubform:
        Set theActiveSubform = Nothing
          
    End Function

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-18-2016, 12:06 PM
  2. Replies: 7
    Last Post: 12-18-2015, 11:43 AM
  3. Replies: 8
    Last Post: 06-19-2015, 02:19 AM
  4. Control source Expression giving #Error
    By N3w2access in forum Forms
    Replies: 5
    Last Post: 01-21-2014, 07:53 AM
  5. Replies: 13
    Last Post: 12-05-2011, 05:10 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