Results 1 to 6 of 6
  1. #1
    Jezarooo is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    3

    Make changes to field value based of changes made to value in field in the previous record

    Hello all,



    I'm a little new to VBA and Access but I'm taking on a project to try and learn both and I'm hoping to get some pointers on a problem I've been working from the guys and gals here

    My project is a database system to handle training programs. I have a concept (table) of Training Classes that are associated with a table for Courses. Each course will have many classes and it is common for a class to be cancelled, in such a situation the lesson number for future classes needs updating accordingly.

    For example:

    Course 1
    - Class 1
    - Class 2
    - Class 3
    - Class 4

    If a status field in a class record were to be changed to 'cancelled' then the class sequence would change for the class number in future records in the sequence of classes:

    Course 1
    - Class 1
    - Class 2 (cancelled)
    - Class 2
    - Class 3

    I'm wondering if anyone can recommend the best approach to achieve this outcome? I'm looking to learn so ideally looking for pointers on the VBA or Access functionality to research please.

    Thanks in advance!

    Jez

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    as you said, tCourses table
    tClasses table (a sub table to Courses)
    tStudents table

    and you need
    tStudentClasses table:
    studentID
    ClassID
    DateTaken
    Grade

    when the student signs up for the Course, the app must populate the StudentID, and all Classes in the course into the tStudentClasses table.

  3. #3
    Jezarooo is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    as you said, tCourses table
    tClasses table (a sub table to Courses)
    tStudents table

    and you need
    tStudentClasses table:
    studentID
    ClassID
    DateTaken
    Grade

    when the student signs up for the Course, the app must populate the StudentID, and all Classes in the course into the tStudentClasses table.
    Thanks for getting back to me!

    However, I may not have been clear in my initial post. I'm seeking a way to dynamically change a class lesson number field based on changes to the class status field (to cancelled) and based on the class lesson number series. The class record will still exist but just hold a different status so I cannot work directly off the classID field (ID5 might actually be lesson number 4 if ID4 was set to cancelled).

    So I'm seeking a way to update all lesson numbers following the cancellation in the entire course based on a field changing. Does this help clarify?

    I'm happy to explore creating any fields needed, calculated fields, expressions, queries, macros or VBA depending on which solution (or combination of) is likely most appropriate.

    Appologies if I'm not using the best terminology, as I say I'm little new to Access.

    Thanks

    J

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Yes,use an update query. This would join the like tables and set all to cancelled.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What are the fields in "tblCourses"?
    What are the fields in "tblClasses"?

    You could have a main form of the courses and a subform of classes. Selecting a course in the main form would display all classes for that course in the subform. You could then change the status of a class.

    tblCourses
    CourseID_PK.....Autonumber
    CourseName.....Text
    CourseDesc......Text

    tblClasses
    ClassID_PK............Autonumber
    CourseID_FK.... .....Number (Long Integer)
    ClassLocation........Text
    ClassDate.............Date/Time
    ClassTime.............Date/Time
    ClassStatusID_FK...Number (Long Integer)

    tblClassStatuses
    StatusID_PK.....Autonumber
    StatusDesc......Text

  6. #6
    Jezarooo is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    3
    Quote Originally Posted by ssanfu View Post
    What are the fields in "tblCourses"?
    What are the fields in "tblClasses"?

    You could have a main form of the courses and a subform of classes. Selecting a course in the main form would display all classes for that course in the subform. You could then change the status of a class.

    tblCourses
    CourseID_PK.....Autonumber
    CourseName.....Text
    CourseDesc......Text

    tblClasses
    ClassID_PK............Autonumber
    CourseID_FK.... .....Number (Long Integer)
    ClassLocation........Text
    ClassDate.............Date/Time
    ClassTime.............Date/Time
    ClassStatusID_FK...Number (Long Integer)

    tblClassStatuses
    StatusID_PK.....Autonumber
    StatusDesc......Text
    Hi guys!

    Thanks for getting back to me!

    In terms of the structure of the tables and the UI, I totally agree. A form for each Course record linked to a Subform listing the Course_Classes is the direction I'm taking.

    However, there was a key field that you left from your list that I would like to include: tblClasses: "LessonNumber". As far as I can see this cannot be an autonumber (so I cannot simply use the Primary Key because it will need to change depending on the status of a field in previous related records).

    Perhaps the best way to illustrate what I mean is to check out the Excel tool I'm trying to rebuild in Access (with more bells and whistles). You can download it here. As you will see, when you change the lesson status the lesson number for future lessons changes depending on the status, for example if the lesson is cancelled then the lesson numbers for lessons happening after the cancellation will be updated. It is this that I am trying to achieve in Access.

    ClassesID_PK Lesson Number ClassStatus (dropdown)
    1 1
    2 2
    3 N/A Cancelled
    4 3
    5 4

    In terms of how the UI in Access would work, I would like for the table of Classes (presented in a Subform as you suggest) to have a dropdown for "ClassStatus" and when that is updated the following lesson numbers will also be updated in the same way as my Excel version (and in the tables below).

    What I don't know how to do is create a query (if that is the correct approach) that will update the "LessonNumber" field in all future class records in the sequence (and how that query could be integrated into the Form / Subform UI), or the functions and methods I need to be aware of to do the same programmatically using VBA (if that is the correct approach) or if an expression or Macro may be the path of least resistance.

    Thanks again in advance

    J

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

Similar Threads

  1. Replies: 1
    Last Post: 12-04-2016, 05:43 PM
  2. Replies: 1
    Last Post: 11-14-2016, 10:36 AM
  3. Replies: 3
    Last Post: 09-19-2014, 08:22 AM
  4. Replies: 4
    Last Post: 01-23-2014, 04:34 PM
  5. Make new field based on previous field's answer
    By VictoriaAlbert in forum Access
    Replies: 1
    Last Post: 04-11-2011, 09:54 PM

Posting Permissions

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