none
Updating Table through Form Textbox change with Subform formula RRS feed

  • Question

  • I'm using Access 2010.   I'm trying to automatically update a table field (System1) upon change in a textbox [Y] or [N]. This (S1)textbox is populated by the control source =IIf(IsNull([SYSTEM1_PD_SUBFORM].[Form]![txtCnfirm1_ID]),"N","Y")

    All other form & subform entries update the table due to being bounded to other tables.

    the summary table would read like

    p1 n1 e1 s1 s2

    50 df ry Y N

    system1 table

    sid pid cnfrmID

    where cnfrmID is being checked

    How can I use an event change for the S1 textbox to update the summary table?


    • Edited by Ackrite55 Monday, June 26, 2017 4:07 PM
    Monday, June 26, 2017 4:00 PM

All replies

  • Hi,

    Unfortunately, code does not trigger an event, so trying to use an event from the S1 Textbox will probably not work. Instead, you might try using an event from the subform's Textbox.

    Just my 2 cents...

    Monday, June 26, 2017 4:09 PM
  • Can I place the =IIf(IsNull([SYSTEM1_PD_SUBFORM].[Form]![txtCnfirm1_ID]),"N","Y") in the After Update expression box?
    Monday, June 26, 2017 4:48 PM
  • Sorry, no, you can't. The event boxes expect to either have [Event Procedure], [Embedded Macro], or a name of a function. You can only enter expression in the Control Source, Default Value, or Validation Rule, and other similar properties.
    Monday, June 26, 2017 5:02 PM
  • Hi Ackrite55,

    you can try to use VBA code with After_Update event.

    you can try to use DoCmd.RunSql method in that event.

    Dim SQL As String 
    
        SQL = "UPDATE Employees " &; _ 
              "SET Employees.Title = 'Regional Sales Manager' " &; _ 
              "WHERE Employees.Title = 'Sales Manager'" 
    
        DoCmd.RunSQL SQL 

    Reference:

    DoCmd.RunSql Method (Access)

    Regards

    Deepak


    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.

    Tuesday, June 27, 2017 1:23 AM
    Moderator
  • Hi Ackrite55,

    I agree with theDBguy that event for (S1)textbox would not fire if you change the value for txtCnfirm1_ID. After_Update only fires when you manually change the values.

    Based on your current description, you did not bind S1 to the field of table and its value depends on txtCnfirm1_ID, I think you could try After_Update of txtCnfirm1_ID, update the other tables.

    If you have any trouble to implement this, please feel free to let us know.

    Best Regards,

    Edward


    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.

    Friday, June 30, 2017 6:25 AM
  • Thanks,

    I have created a macro (mrcUpdateS1) using SetValue and placed it in the AfterUpdate() properties

    SetValue 

    Item = [Summary]tbl![S1]

    Expression = [Form]![Summarytbl]![txtS1]

    but the Summaytbl hasn't yet.. still working on it.

    Friday, June 30, 2017 4:06 PM
  • Hi Ackrite55,

    is your issue solved by creating a macro?

    if yes , then please try to update the status of this thread and let us know about that.

    if your issue is solved now then you can mark that solution as an answer. so that we can close this thread.

    if your issue is still exist then let us know about the latest status of your issue and we will try to provide you further suggestions to solve the issue.

    Regards

    Deepak


    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.

    Monday, July 3, 2017 5:14 AM
    Moderator
  • the Textbox [ ] control : =IIf(IsNull([SYSTEM1_PD_SUBFORM].[Form]![txtCnfirm1_ID]),"N","Y")

    AfterUpdate() property macro:

    SetValue 

    Item = [Summary]tbl![S1]

    Expression = [Form]![Summarytbl]![txtS1]

    but the Summaytbl hasn't yet.. still working on it.

    is where I am now

    Monday, July 3, 2017 6:46 AM
  • Hi Ackrite55,

    if you are having trouble to implement macro then you can try to use vba code and docmd.runsql.

    which is very easy to use and you can use it on the same event.

    you just need to write an update query and you can run it from the code.

    Regards

    Deepak


    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.

    Monday, July 3, 2017 9:02 AM
    Moderator
  • I have deleted the unbounded textbox:  Textbox [ ] control : =IIf(IsNull([SYSTEM1_PD_SUBFORM].[Form]![txtCnfirm1_ID]),"N","Y")  and added the existing field s1 to the mainform so that would be linked to the summary table.

    s1 field of the summary table returns a "Y" if a subform record is created through a s1 confirmation number entry.

    Could I eliminate the textbox altogether & evaluate the system table  as it consist of sid [pid] [cnfrmID] and the summary table includes the following:

    pid n1 ... e1 s1 s2

    50 df .... ry Y N

    subform:

    sid pid cid

    1  50   [ user entry]

    Tuesday, July 4, 2017 8:48 PM
  • Hi Ackrite55,

    you had mentioned that you added the existing field s1 to the mainform so that would be linked to the summary table.

    is it working correctly?

    you are talking about system table in previous post.

    what are you doing with system table , I did not understand well from your description.

    if you try to provide more details about it then we can try to understand it better.

    Regards

    Deepak


    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.

    Friday, July 7, 2017 7:53 AM
    Moderator