none
Question about MS Access Froms-- RRS feed

  • Question

  • Hi,

    I'm getting below error when I added my code to 'Unbound' label for the field 'After Update'

    The expression after update you entered as event property produce the following error in access

    Code:

      

    Private Sub analystfilter_AfterUpdate()
    On Error GoTo ErrLogger
        Dim strsql As String
        Dim Mainsubform As Form
        Set Mainsubform = Form_Mainsubform

        strsql = RetrieveProjectList(Me)
        Mainsubform.RecordSource = strsql

    exitCode:
        Exit Sub
    ErrLogger:
        g_FunctionName = "analystfilter_AfterUpdate"
        Call errHandler(Me.Name, g_FunctionName)
        Resume exitCode
    End Sub

    Please help. Thanks!

    Friday, June 29, 2018 2:56 AM

Answers

  • I assume when you in VBA editor go debug->compile, you get this line highlighted?

    >>Set Mainsubform = Me.Project_Status.Form

    You want to use intel-sense, and let access “suggest” the name of the sub form control.

    So you type in me.

    The instant you hit the “dot” in above, Access should pop up a list of choices. You from that list choose the name of the sub form control (not the name of the sub form). Usually access will give the sub form control the same name as the sub form (but this is just like any other control – usually the control for example is given the same name as the column it is bound to, but it don’t have to be). So often a control name might have defaulted to say “child12” or whatever. However the control name used often can be different then what the actual sub form setting is for the sub form control.

    So while in the VBA editor, choose debug from the menu, and then compile. So do a debug->compile. This will allow you to eliminate any and all compile errors you have. (Since if you have other errors, then intel-sense often will stop working).

    So you can try/test/compile before you actually try to run the code.

    The assumptions thus are:

    The code snippet you have is in the main form, and the code you have wants to reference with an instance of the sub form for that main form. Also, we assume that the sub form is a “form” as opposed to a report (access does allow both – and if it is a report, the me.ControlName.report would be used.

    However, it certainly looks like you working with a sub form as opposed to a sub report.

    So make sure debug-compile eliminates all errors in your application (before you attempt to run/use the code). And as noted, let intel-sense popup the choices for you, and ensure that you select the correct control from that popup list, and then hitting “dot” again after the control name should present “form” as a choice.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by testqa9 Friday, July 13, 2018 1:29 AM
    Wednesday, July 11, 2018 5:06 PM

All replies

  • You want to grab the sub form “instance” from the control. You are allowed to place a sub form more than once – so multiple instances of the form can exist at the same time.

    Try this to get the particular instance of the sub form

    Set Mainsubform = me.SubFormControlName.Form

    So let inteli-sense pop up the list of sub form (controls) that you have in the code editor – and use a syntax similar to above.

    The rest of your code looks fine to me.

    So me.Name of your sub form control.Form (the .Form part is always ".form" and that gets/grabs the form instance of the sub form control.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Friday, June 29, 2018 8:53 PM
  • Hello Testqa9,

    What's the state of the thread? There should be a detail error message, what did it say?

    Does your code break on Set Mainsubform = Form_Mainsubform as Albert said? Is your original issue resolved? 

    Please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by vanujkpol Thursday, January 2, 2020 4:59 PM
    Monday, July 2, 2018 6:27 AM
  • Thanks for the reply Albert, but I got compile error

    "Method or Data Member Not Found".

    Please help. Thanks!

    Wednesday, July 11, 2018 2:10 PM
  • Getting compile error and replied to Albert saying so. Please see above. Thanks!
    Wednesday, July 11, 2018 2:10 PM
  • Here is my updated code:

    Private Sub analystfilter_AfterUpdate()
    On Error GoTo ErrLogger
        Dim strsql As String
        Dim Mainsubform As Form
        'Set Mainsubform = Form_Mainsubform
        Set Mainsubform = Me.Project_Status.Form
        strsql = RetrieveProjectList(Me)
        Mainsubform.RecordSource = strsql
    exitCode:
        Exit Sub
    ErrLogger:
        g_FunctionName = "analystfilter_AfterUpdate"
        Call errHandler(Me.Name, g_FunctionName)
        Resume exitCode
    End Sub

    Wednesday, July 11, 2018 2:17 PM
  • I assume when you in VBA editor go debug->compile, you get this line highlighted?

    >>Set Mainsubform = Me.Project_Status.Form

    You want to use intel-sense, and let access “suggest” the name of the sub form control.

    So you type in me.

    The instant you hit the “dot” in above, Access should pop up a list of choices. You from that list choose the name of the sub form control (not the name of the sub form). Usually access will give the sub form control the same name as the sub form (but this is just like any other control – usually the control for example is given the same name as the column it is bound to, but it don’t have to be). So often a control name might have defaulted to say “child12” or whatever. However the control name used often can be different then what the actual sub form setting is for the sub form control.

    So while in the VBA editor, choose debug from the menu, and then compile. So do a debug->compile. This will allow you to eliminate any and all compile errors you have. (Since if you have other errors, then intel-sense often will stop working).

    So you can try/test/compile before you actually try to run the code.

    The assumptions thus are:

    The code snippet you have is in the main form, and the code you have wants to reference with an instance of the sub form for that main form. Also, we assume that the sub form is a “form” as opposed to a report (access does allow both – and if it is a report, the me.ControlName.report would be used.

    However, it certainly looks like you working with a sub form as opposed to a sub report.

    So make sure debug-compile eliminates all errors in your application (before you attempt to run/use the code). And as noted, let intel-sense popup the choices for you, and ensure that you select the correct control from that popup list, and then hitting “dot” again after the control name should present “form” as a choice.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by testqa9 Friday, July 13, 2018 1:29 AM
    Wednesday, July 11, 2018 5:06 PM
  • Tried above and it worked.. Thanks a lot for the help :)

    I have a scenario where I click on the button it should automatically refresh the number of claim count.

    Right now my code configured that each time I have to click the button, it's not refreshing automatically.

    Here is my code:

    Option Compare Database
    Const intRefreshInterval As Integer = 120
    Private Sub Form_Timer()
    '    Dim intTimerCount As Integer
    '
    '    intTimerCount = intRefreshInterval
    '
    '    Do While intTimerCount > -1
    '        intTimerCount = intTimerCount - 1
    '
    '        If intTimerCount = 0 Then
                Me.Child179.Requery
    '            intTimerCount = intRefreshInterval
    '        End If
    '    Loop

    End Sub

    • Proposed as answer by vanujkpol Thursday, January 2, 2020 5:08 PM
    Friday, July 13, 2018 1:29 AM