Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Query via VBA using Joins (2003 SP2)

    I need to update one Table based on the value of another when a Form is modified. I know the Syntax, etc, but I'm not sure how to proceed when there are Inner Joins involved.

    The SQL looks like this:
    UPDATE REFERRALS INNER JOIN PLACEMENTS ON (REFERRALS.[Registration Code] = PLACEMENTS.[Registration Code]) AND (REFERRALS.ID = PLACEMENTS.ID) SET REFERRALS.Status = [PLACEMENTS].[Status];

    I know it needs to be formatted like this in VBA:

    strSQL = "UPDATE [REFERRALS] SET [Status] = " & Me.[Status] & _
    " WHERE [Registration Code] = " & Me.[Registration Code]
    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL

    But can someone tell me how I tell VBA how the joins occur, when more than 1 Field defines the Relationship?

    Thanks in advance. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    I'm not sure what exactly you want, but it could look like this:

    strSQL = "UPDATE REFERRALS INNER JOIN PLACEMENTS ON " & _
    "(REFERRALS.[Registration Code] = PLACEMENTS.[Registration Code]) AND " & _
    "(REFERRALS.ID = PLACEMENTS.ID) " & _
    "SET REFERRALS.Status = [PLACEMENTS].[Status]"

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    Excellent, thanks Hans.

    I now realize I need to add a WHERE condition, but when I try to enter the following, I get a SYNTAX ERROR in Update Statement:
    I've tried the WHERE condition in various places, and I've added and removed brackets, thinking some weren't necessary, but . . . ???


    strSQL = "UPDATE REFERRALS INNER JOIN PLACEMENTS ON " & _
    "(REFERRALS.[Registration Code] = PLACEMENTS.[Registration Code]) AND " & _
    "(REFERRALS.ID = PLACEMENTS.ID) " & _
    " WHERE (PLACEMENTS.ID) = ([Forms]![Data Entry]![Data Entry (REFERRALS) Subform].[Form]![Placements (By date)].[Form]![ID])" & _
    "SET REFERRALS.Status = [PLACEMENTS].[Status]"
    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL


    Can you shed some light on where I'm going wrong?

    Thanks <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    If you look at the help for UPDATE in the Microsoft Jet SQL Reference (part of the Access help), you'll see that the syntax is

    UPDATE ...
    SET ...
    WHERE ...

    instead of

    UPDATE ...
    WHERE ...
    SET ...

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    That's how I had it, but I got the Error Message mentioned. I only moved it to see whether it would make a difference? [img]/forums/images/smilies/sad.gif[/img]

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    Try inserting a space between the quote " and the word SET.

    If that doesn't help, I'm afraid we'd have to see the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    Thanks Hans

    Error resolved.

    Can I ask you another question, please?

    Once this update occurs, it then needs to cascade back up to the Original Form and update the Status of a Combo Box on that Form. So, I need to repeat this procedure, which seems to work OK, but there's a weird delay (even if I requery the Form). Eg, if I set Subform 3's control to equal Subform 2's control, then subform 2's control should equal Form 1's control, ie, they should all have the same ID Number.

    However, even though I get the 2 Update messages to state that 2 updates are about to occur, only Subform 2's control changes to equal Subform 3's control. Form 1 remains unchanged. Now, here's the weird part. If I change Subform 3's control again, Form 1 THEN resets to what it should have been, but it doesn't change to the ID Number it currently is. This only occurs when I change it AGAIN. I've requeried the Database; opened and closed the Form, etc, but Form 1's ID will only change after another update.

    Here's the code I'm using:

    Private Sub Combo21_AfterUpdate()
    On Error GoTo Err_Combo21_AfterUpdate
    Dim strSQL As String

    strSQL = "UPDATE REFERRALS INNER JOIN PLACEMENTS ON " & _
    "(REFERRALS.[Registration Code] = PLACEMENTS.[Registration Code]) AND " & _
    "(REFERRALS.ID = PLACEMENTS.ID) " & _
    "SET REFERRALS.Status = [PLACEMENTS].[Status]" & _
    "WHERE PLACEMENTS.ID = ([Forms]![Data Entry]![Data Entry (REFERRALS) Subform].[Form]![Placements (By date)].[Form]![ID])"
    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL

    strSQL = "UPDATE CLIENT INNER JOIN REFERRALS ON " & _
    "(CLIENT.[Unique Identification Code] = REFERRALS.[Unique Identification Code]) AND " & _
    "(CLIENT.[Registration Code] = REFERRALS.[Registration Code]) " & _
    "SET CLIENT.CurrentStatus = [REFERRALS.Status]"

    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL

    Exit_Combo21_AfterUpdate:
    Exit Sub

    Err_Combo21_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Combo21_AfterUpdate
    End Sub

    Can you see what I'm doing wrong?

    Thanks in advance.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    Please post a stripped down copy of your database, See my previous reply.

  9. #9
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Done. Thanks Hans. You're a star. It's slightly bigger than 100KB, so I've uploaded it to one of my sites:

    If you go to http://www.fanlistings.org/COPY.zip, you should be able to download it and see what's happening.

    As ever, thanks!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    Sheesh... Why did you include so many records, so many tables, so many queries and so many forms? I have no idea where to look.

  11. #11
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    I sincerely apologize Hans. It's not my Database--I've just been making modifications this last month or so, and this problem was the one that spun me out.

    I left the Tables, Queries, and Forms in just so I wouldn't break anything, but I realize that I was stupid not to provide information pertaining to the problem. Again, I apologize.

    The Form's control with which I'm having problems is Data Entry. There's a Tab Control containing 'Referrals & Placements'.
    You will see a STATUS combo box with, eg, Discontinued. The VBA for this contains the problem I discussed.

    Obviously, if I need to provide any further information, just ask. Thanks again for taking the time to look at it. [img]/forums/images/smilies/smile.gif[/img]

    Brian

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    When the After Update event of the combo box occurs, the value of the combo box has been changed, but the record has not been saved yet. So the value of the Status field in the Placements table has not been updated yet. Your query refers to the value in the table, not to the value of the combo box. Hence the "lag".

    Try the attached version. If refers to the combo box, and also refreshes the record in the main form and first subform after the update.

  13. #13
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    Oh right. I thought Access automatically saved after every event/change. I see the change you made, and it (naturally) works perfectly. Do you ever get tired of being told what a star you are?

    Thank you so much for clarifying that--it was spinning me out.

    Have a wonderful day tomorrow, and enjoy the rest of your week.

    God Bless

    Brian

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    Access automatically saves a record when you move to another record (or close the form), not while you move from field to field within the current record.

  15. #15
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query via VBA using Joins (2003 SP2)

    So does it just store info to RAM until the close or move occurs, and then it performs an update?

    Anyway, can I ask you a bit of advice? You may have seen that the Parent Form contains a date updated field--Access automatically completes this whenever a new Record is added, etc. What would be the best way to get this field to autocomplete (ie, =date) whenever any record or field is added/changed on any of the child forms (which are all located on the Tab Controls)?

    I was going to add AfterUpdate Events to each Subform, but I thought there might be a better way to do it, and if there is, you'll know it!

    Thanks

    Brian

Page 1 of 3 123 LastLast

Posting Permissions

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