locked
Update query - automatic field update RRS feed

  • Question

  • I have a Customers table and a Customer_Contacts table (and a form for each) for which the primary keys are ID and Customer_ID, respectively. Contact names and titles are stored in the contacts table and when Contact_ID is updated on Customer form (via a Lookup combo displaying the contact name list) I should like to update the Contact title automatically (on the Customers form). I tried an update query in the After Update event on the Contact_ID field but it doesn't work. I actually set it up in Design View but the generated SQL is:

    UPDATE Customers INNER JOIN Customer_Contacts ON Customers.ID = Customer_Contacts.Customer_ID SET Customers.Contact_Title = [Customer_Contacts].[Contact_Title];

    That looks OK to me, given my limited grasp of SQL, but I've no doubt missed something obvious.

    Any help much appreciated.
    Monday, April 23, 2018 5:23 PM

Answers

  • Many thanks to all responders for your help.

    Special thanks to Miriam --- your solution works a treat!

    I was aware of the issue with normalization in my original setup, and between posting and getting replies I added the unbound field (which you subsequently suggested)....but I wasn't sure what to do next. So thanks again.

     - Norman (NK-Bristol)

    • Marked as answer by NK-Bristol Tuesday, April 24, 2018 2:30 PM
    Tuesday, April 24, 2018 2:29 PM

All replies

  • Hi,

    What exactly does "doesn't work" mean? Were you getting any errors? Or something else?

    If you can provide more details (screenshots might help), we could probably help you figure out what is wrong.

    Just my 2 cents...

    Monday, April 23, 2018 6:23 PM
  • Hello NK-Bristol,

    Has you tried to test your code? What's the result? What's the whole code in your After Update event?

    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.

    Tuesday, April 24, 2018 8:33 AM
  • Hi -

    Reading your post, your design doesn't sound quite right.  Correct me if I'm misreading this, but it sounds like you have Contact_Title in both tables, and are pursuing this UPDATE approach for the purpose of getting the Contact_Title displayed on the Customers form, corresponding to the Contact Name/ID that the user selects through a combo box.

    If this is your purpose, a better approach would be to store your Contact_Title ONLY in the Contacts table. 

    Then, to display the Contact_Title on your Customers form:

    1. Add Contact_Title to the rowsource of your combo box (so that the select includes Contact_ID, Contact_Name and Contact_Title), and increase the Column Count property accordingly. You can set the width of the Contact_Title column to 0 if you don't want it displayed in the drop down.

    2. Add an Unbound Textbox alongside your combo box to display (but not store) the title.

    3. Set the Control Source of the textbox to:

        = YourComboBoxName.Column(2)

    (Note that the column index is zero-based, so Column 2 is the third column in the SELECT clause of the combo's rowsource query)

    With this approach, the textbox will display the Contact_Title corresponding to whatever Contact the users select in the combo box.


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Tuesday, April 24, 2018 12:26 PM
    Tuesday, April 24, 2018 12:26 PM
  • Many thanks to all responders for your help.

    Special thanks to Miriam --- your solution works a treat!

    I was aware of the issue with normalization in my original setup, and between posting and getting replies I added the unbound field (which you subsequently suggested)....but I wasn't sure what to do next. So thanks again.

     - Norman (NK-Bristol)

    • Marked as answer by NK-Bristol Tuesday, April 24, 2018 2:30 PM
    Tuesday, April 24, 2018 2:29 PM
  • Norman, Glad to help out!

    Miriam Bizup Access MVP

    Tuesday, April 24, 2018 2:59 PM