Results 1 to 6 of 6

Update query in VBA

  1. #1
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    229

    Update query in VBA

    Experts:



    I have a question about updating some table references/records. Hopefully someone could assist me with some UPDATE query.

    Background:
    - I have a "Archive Record" button on a form.
    - I pressed (and confirmed), the boolean value TRUE will be set to FALSE based on the VBA (line #1). This works fine.

    What I'd like to Accomplish:
    - I'd like to also update the junction table (with lines 2 and 3). These two lines are executed w/o errors if the RecordIDpk exists.
    - However, I just realized that not all staff members have a record ID in the junction tables. There are scenarios where they may have a record in T00_JunctionTable_BCFT AND/OR in T00_JunctionTable_BOPT.

    Right now, if no record exists the record (obviously) cannot be updated. My question: How can I, e.g., use an IF statement (only for VBA lines 2 and 3 below) in the event a record exist in neither both tables nor in T00_JunctionTable_BCFT nor in T00_JunctionTable_BOPT?


    Code:
    CurrentDb.Execute "UPDATE T01_StaffMembers SET T01_StaffMembers.All_Onboard = False WHERE (((T01_StaffMembers.StaffMemberIDpk)=" & StaffMemberIDpk & "))"            
    CurrentDb.Execute "UPDATE T00_JunctionTable_BCFT SET T00_JunctionTable_BCFT.RecordIDfk = 1 WHERE (((T00_JunctionTable_BCFT.RecordIDfk)=" & Me.RecordIDpk & "))"
    CurrentDb.Execute "UPDATE T00_JunctionTable_BOPT SET T00_JunctionTable_BOPT.RecordIDfk = 1 WHERE (((T00_JunctionTable_BOPT.RecordIDfk)=" & Me.RecordIDpk & "))"

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,406
    Are you saying there's no value in Me.RecordIDpk? I ask because I would not expect an error unless that control is empty. If it has a value and there are no matching records in the table(s), I'd expect it to run without error but not update any records (which you can test for if you want).

    If there's no value in
    Me.RecordIDpk, then I'd test that before running the second and third lines.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    229
    All staff members exists in the staff members table. That's where the archiving (boolean from TRUE to FALSE) takes place in line 1. No issues.

    Then I have a table that links jobs to Cross-Functional Teams (CFT). If an employees is part of the but leaves the organization, I still want the job to be linked to the CFT but remove the employee ID from the CFT membership. Again, I didn't get any errors to replace the employee ID with generic employee ID #1 (i.e., vacant position). Still, no issues if an employee who leaves the organization will have to be removed from the junction table.

    However, the error occurs when an employee is archived I want to update their record ID with "1" but the employee was never part of the CFT. So, basically, I'm trying to update a record that doesn't exist. This is where the errors came into play.

    If I hear you right, nothing should have happened and I shouldn't have seen any VBA?

    Thanks,
    EEH

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,406
    What error do you get exactly? If your SQL specifies record ID 123 and that ID doesn't exist, you shouldn't get an error, just no records updated.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    229
    Paul:

    Thank you... please see attached VBA error.

    It appears there's something wrong w/ the number of left/right parenthesis. Unless I'm overlooking though, the construct is just line the Update routine in line #1 (included in original post).

    What am I missing?
    Tom
    Attached Thumbnails Attached Thumbnails Error.JPG  

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,406
    There's no value coming from Me.RecordIDpk. You can either use the Nz() function around it or test its contents and only run those when it has a value in it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-11-2018, 11:44 AM
  2. Replies: 2
    Last Post: 08-03-2017, 04:58 AM
  3. Replies: 1
    Last Post: 10-27-2016, 12:14 PM
  4. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  5. Replies: 2
    Last Post: 08-30-2012, 07:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums