Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2011
    Posts
    11

    Unanswered: After Update: VBA Logic or Update Query?

    I posted this on a couple of other forums but haven't been able to resolve it. I am having trouble figuring out how to accomplish the following:

    I have a form linked to tblProjects and subform linked to the junction table tblProjectEmployeeRole.

    There is an After Update event on the subform:

    Code:
    If Me.fkRoleID = 3 Then
    DoCmd****nSQL "UPDATE tblEmployees SET EmloyeeCar = '-1' WHERE EmployeeID = Forms!frmCourseInfo!EmployeeID"
    End If

    This part works. If Role 3 (Driver) is selected, the EmloyeeCar field is updated to "-1". This is what I need: if Role 3 is deleted or changed, "-1" in the same field should be automatically updated to "0."

    I am not sure if VBA or a query is the answer
    I also don't know if this process should be tied to the form or whether it should be a query that runs against the junction table directly.

    More details:

    I am using Access 2002.
    There are 4 possible roles, different people, and different projects.
    Examples:

    Project 1
    John Smith, Driver
    Michelle Glow, Driver
    Michael Star, Mechanic

    Project 2
    Jeff Smirk, Teacher
    Linda Jame, Coder
    Gina Marcus, Driver

    When the role Driver is selected for a Project, "-1" is automatically entered in tblEmployees.EmloyeeCar. This works.

    However, let's assume that Gina Marcus is no longer part of the Project 2 team and I use the subform to delete the record in the junction table that relates her to the Project; well now I want the "-1" to be changed to "0."

    As you can see in Project 1, at the same time, even if John Smith drops from the Project, Michelle Glow, the other Driver, is still there, so the "-1" should remain.

    Basically, whenever there is at least one Driver (Role 3) in the Project, I need a "-1" in tblEmployees.EmloyeeCar, but when there is no Driver in a Project, I need a "0" in that field.

    Should I take a query or VBA approach here, or a combination?

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You reached one limitation of Access here: You cannot guarantee the data consistency between two tables in this way because Access has no triggers and only has very limited possibilities as far as constraints (validation rules) are concerned.

    If the form is the only place in your application where what you name the "role" can be changed, I would go for running an UPDATE query in the AfterUpdate event of the control related to the "role" column.

    In more complex situations, I would consider creating a class that would incorporate the necessary rules and only access the data through an instance of this class.

    In any case, opening the table directly should be forbidden.
    Have a nice day!

  3. #3
    Join Date
    Mar 2011
    Posts
    11
    Thank you for your reply.

    Quote Originally Posted by Sinndho View Post
    You reached one limitation of Access here: You cannot guarantee the data consistency between two tables in this way because Access has no triggers and only has very limited possibilities as far as constraints (validation rules) are concerned.
    If this is an Access limitation, are there other applications or database systems that could do this?


    Quote Originally Posted by Sinndho View Post
    If the form is the only place in your application where what you name the "role" can be changed, I would go for running an UPDATE query in the AfterUpdate event of the control related to the "role" column.
    Currently the After Update procedure is linked to the subform. Are you saying that I should link it to the combo box that is used to select the role and then have the procedure run an update query with the selected role as the criteria?

    Quote Originally Posted by Sinndho View Post
    In more complex situations, I would consider creating a class that would incorporate the necessary rules and only access the data through an instance of this class.
    What do you mean by class here?

    Thanks again.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Any RDBMS that implements triggers (MS SQL Server, Oracle, etc.). I don't know Oracle enough, but in SQL Server there would be other solutions (apart from triggers), using computed columns and/or data constraints.

    2. Precisely: the place where the data is changed seems to be the most logical place where to modify any associated or logically related piece of information.

    3. This is a complex matter that requires an in-depth knowledge of Access, of the VB/VBA language and of Object-oriented programming in general. In Access you can create Class modules that are not bound to a form or to a report. In such a Class, you can use code to implement any rule you want. This class would act as a proxy, an intermediate layer, between the table(s) and the application interface. Rules for updating a value in one table when a defined value is inserted or updated into another table could be implemented there. Of course, you would probably lose many built-in functionalities of Access with this solution.
    Have a nice day!

  5. #5
    Join Date
    Mar 2011
    Posts
    11
    Quote Originally Posted by Sinndho View Post
    2. Precisely: the place where the data is changed seems to be the most logical place where to modify any associated or logically related piece of information.
    This makes sense, but I am struggling with coming up with the logic.
    Can such a query be done in design view or do I need to enter an IF statement into the SQL code?

    I am still not sure whether the query should review the combo box selection or the junction table that holds that selection.

    I see that I can enter both After Update and On Change procedures. But is deleting a record/role considered a change? I don't see On Delete.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Something like this:
    Code:
    Private Sun Role_AfterUpdate()
    
        Const c_SQL = UPDATE tblEmployees SET EmloyeeCar = '-1' WHERE EmployeeID = @Id"
    
        If Me.Role.Value = 3 Then
            CurrentBd.Execute Replace(c_SQL, "@Id", Me.EmployeeID.Value), dbFailOnError
        End If
    Have a nice day!

  7. #7
    Join Date
    Mar 2011
    Posts
    11
    I don't think I understand this, but I will try...

  8. #8
    Join Date
    Mar 2011
    Posts
    11
    Could something like this work?

    UPDATE tblProjects INNER JOIN tblProjectPeopleRoles ON tblProjects.ProjectID = tblProjectPeopleRoles.ProjectIDFK SET tblProjects.EmployeeCar = IIf([RoleIDFK]="3","-1","0");

    I am getting a type conversion error failure.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the column "ProjectIDFK " is defined as a numeric data type, it should be:
    Code:
    UPDATE tblProjects INNER JOIN tblProjectPeopleRoles ON tblProjects.ProjectID = tblProjectPeopleRoles.ProjectIDFK SET tblProjects.EmployeeCar = IIf([RoleIDFK]=3,"-1","0");
    You don't include numeric values between quotes.

    Moreover, you don't define the row in which to test for the value of RoleIDFK, nor the table in which to search for it.
    Have a nice day!

  10. #10
    Join Date
    Mar 2011
    Posts
    11
    Yes, ProjectIDFK is numeric. I removed the "", so I am no longer getting the type conversion failure error.

    I added the table name but I thought that was already implied by the beginning of the code:

    Code:
    UPDATE tblCoursePeopleRoles LEFT JOIN tblCourses ON tblCoursePeopleRoles.CourseIDFK = tblCourses.CourseID SET tblCourses.CourseHasTA2 = IIf([tblCoursePeopleRoles].[RoleIDFK]=3,-1,0);
    What do you mean by "define the row in which to test for the value of RoleIDFK"?

    Thanks for your help.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. When you have a query expression with more than one table (INNER JOIN, etc.) it's recomanded to use the full name: TableName.ColumnName, because you'll get an error if several tables included in the query have a column with the same name.

    2. When you do not specify a WHERE condition, a query works on a whole data set, not only on the row that represents the current record of the form. In the present case, the test inside the IIF function is performed on every row of the data set issued from the INNER JOIN operation. So, for every row where [tblCoursePeopleRoles].[RoleIDFK]=3, [tblCourses].[CourseHasTA2] will be set to -1; it will be set to 0 otherwise.
    Have a nice day!

  12. #12
    Join Date
    Mar 2011
    Posts
    11
    Thank you for the clarification. I got this thing to work using a combination of VBA and sql. All's good, for now...

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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