Results 1 to 11 of 11

Thread: Update Query

  1. #1
    Join Date
    Jan 2004
    Posts
    77

    Unanswered: Update Query

    This may sound stupid but is it possible (if so how) to update records in one table based on a linked field via input on a form? I understand how to display "linked" results from a keyin. However, I want to physically update fields in myTable.

    1. From the frmPermits form - If I input a value in the Notice_ID field, it returns “linked” data from the tblLegals table. However, I would like it to physically populate the tblPermits table rather than simply displaying the linked data. I may also need to be able to change the results that are returned from the linked data. The linked data is coming from a table where a sidewalk notice was sent. However, sometimes between the time the notice is sent and the time the permit is taken out, the property is sold. Therefore we need to change the OwnerName in the tblPermits table (not the tblLegals table) to track who was sent the notice and who actually took out the permit (most of the time these are the same).
    2. Because permits are taken out by anyone doing sidewalk work, many permits are issued to persons who never received a Sidewalk Repair Notice. Therefore, the Notice_ID field is not always populated. This is only input if the person had received a notice.

    Sorry for the lengthy explanation but I thought it might help. I have attached an abridged version of my SIDEWALK database (only contains items pertaining to this concern). If possible, at your convenience, could you please take a look at it and see what I am referring to.

    Thanks,
    SKK
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    My first step was to add the tblPermits.OwnerName, Par_Addr, Line1-5 fields to the form

    I added this vba:
    Private Sub tblPermits_Notice_ID_AfterUpdate()
    Dim qdf As QueryDef
    If IsNull(tblPermits.Notice_ID) = 0 Then
    tblPermits.OwnerName = tblLegals.OwnerName
    tblPermits.Par_Addr = tblLegals.Par_Addr
    tblPermits.Line1 = tblLegals.Line1
    tblPermits.Line2 = tblLegals.Line2
    tblPermits.Line3 = tblLegals.Line3
    tblPermits.Line4 = tblLegals.Line4
    tblPermits.Line5 = tblLegals.Line5
    End If
    End Sub

    I made the tblLegal fields hidden

  3. #3
    Join Date
    Jan 2004
    Posts
    77
    jmrSudbury,

    Again, a bit stupid, but where do you put the VBA code (in the form, a module, ??)?

    Thanks,
    SKK

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i didn't download your zip, but jmrs... tells you:

    tblPermits_Notice_ID_AfterUpdate()

    since jmrs... is offline right now----

    somewhere in your app you have a control "tblPermits_Notice_ID"
    find which form
    open the form in design view
    ALT-F11
    in the left combo at the top, select tblPermits_Notice_ID
    (mr gates will default to _beforeupdate)
    in the right combo at the top, select AfterUpdate

    and paste the code in the _afterupdate() event.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jan 2004
    Posts
    77
    Thanks Izy,

    I'm still confused, but I'll keep on it.

    SKK

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I am assuming you added the fields I listed in my first post at this point

    Go to your form
    Double click on your tblPermits_Notice_ID field
    In the property window, go down until you see After Update
    Double click in that field so it will say [Event Procedure]
    Click on the button on the far right with the 3 dots (elipses)
    Paste the code I wrote in my first post

  7. #7
    Join Date
    Jan 2004
    Posts
    77
    JMRS,

    OK, I wasn't sure if I was doing it correctly but that is what I had done. When keyin a new entry in the tblPermits.Notice_ID field, it returns a 'Run-time error 424'

    When I click on Debug, it highlights in yellow

    If IsNull(tblPermits.Notice_ID) = 0 Then

    If possible, could you send me a copy of the DB you have working so I could look at it.

    Thanks for your time,
    SKK

  8. #8
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    The error was Object Required.

    I had to rename all of the fields to get rid of the periods:

    tblPermits.OwnerName is now tblPermitsOwnerName
    tblLegals.OwnerName is now tblLegalsOwnerName
    tblPermits.Par_Addr is now tblPermitsPar_Addr
    tblLegals.Par_Addr is now tblLegalsPar_Addr
    ... etcetera ...

    And I changed the code touse thenew names:

    Dim qdf As QueryDef
    If IsNull(tblPermitsNotice_ID) = 0 Then
    tblPermitsOwnerName = tblLegalsOwnerName
    tblPermitsPar_Addr = tblLegalsPar_Addr
    tblPermitsLine1 = tblLegalsLine1
    tblPermitsLine2 = tblLegalsLine2
    tblPermitsLine3 = tblLegalsLine3
    tblPermitsLine4 = tblLegalsLine4
    tblPermitsLine5 = tblLegalsLine5
    End If

    Sorry about that!

  9. #9
    Join Date
    Jan 2004
    Posts
    77
    JMRS,

    Sorry to keep bugging you but could you post (or e-mail me) a copy of the DB as you have it working?

    Thanks,
    SKK
    skipping@manitowoc.org

  10. #10
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Here is my version
    Attached Files Attached Files

  11. #11
    Join Date
    Jan 2004
    Posts
    77
    JMRS,

    Thank you very much for your time on this. This is what I had in mind. I'm going to modify slightly now and include in our full version DB.

    Thanks again...
    Greatly appreciated,
    SKK

Posting Permissions

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