Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Posts
    110

    Unanswered: Update multiple tables from field on form

    Hello! I'll try to explain what im doing.. I have my main table that data is entered into from a form. It is used for storing information on helpdesk calls from hospitals.

    When the user enters the caller's name, the phone number populates from query using my hospital_users table that has the phone number stored. My main table is called helpdesk.

    I have left the phone number field is left unlocked, so users can update it as needed.

    When the phone number on my form is updated, it is only updating the helpdesk table where the helpdesk record is. I would like it to also update the phone number in the hospital_users table as well.

    I have searched, but havent found anything I could use to acheive this.

    Thanks in advance!

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    weeellllll - the first point is that one doesn't have the same data in two places in a "properly" normalized relational database....as I scold you and shake my finger.... so that's really the issue here....

    if those fields are indeed always identical data - abandon the field in the helpdesk table.
    if those fields are not always identical data - then have both fields in the helpdesk form via either a join in your underlying record source for that form or via a subform technique.

  3. #3
    Join Date
    Oct 2006
    Posts
    110
    assuming I get rid of the field in my helpdesk table, how would I show the phone number coming from the hospital_users table? Right now I am using a after_update event on my callers name that adds the phone number to the helpdesk table from my query.

    Me!Requester_Phone = Me!Requester.Column(4)

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    via either a join in your underlying record source for that form or via a subform technique.

  5. #5
    Join Date
    Oct 2006
    Posts
    110
    Just to update my question.. I found a solution. Thanks for your help and suggestions.

    Here is my code if it can help someone:

    Private Sub Phone_Exit(Cancel As Integer)

    Dim AstrSQL As String
    AstrSQL = "update hospital_users SET phone = " & "'" & Me!Phone & "'" & "WHERE Users = " & "'" & Me!Caller & "'"
    CurrentDb.Execute AstrSQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •