Changing value of fields a set of records based on a field value change (1 Viewer)

Jezarooo · Dec 8, 2016

Hello all!

I'm trying to learn VBA and Access and to do so I'm in the process of developing a training management database.

The area I am currently working on relates to my tblTrainingCourses tblTrainingClasses. As you can imagine, each course can have many classes associated with it, moreover, each class has the potential to be cancelled and is part of a linear sequence of lessons. When a class is cancelled then the lesson number for the next lesson needs automatically adjusting, as per the example below.

tblTrainingClasses:
ClassID_PK | LessonNumber | LessonStatus (dropdown)
-------------------------------------------------------------------------------
1 | 1 | -
2 | 2 | -
3 | N/A | Cancelled
4 | 3 | -

My question is (as a noobie trying to learn): what is the best way to achieve this effect?

I would like to present the data so there is a tblTrainingCourses record form, with a Subform within that for tblTrainingClasses listing all of the lessons for that course that will update the LessonNumber for future lessons if a dropdown ("LessonStatus") is adjusted.

I'm not seeking the code itself, just hopefully some specific pointers on the best approach for me to research and explore.

Thanks in advance!

J

MarkK · Dec 8, 2016

Welcome to the forum.

It's actually an interesting problem because in a database there is no inherent order to the rows. You may sort on a field, or reverse sort on a different field, and so the concept of one record being number 1, and some other record being number 2 is not absolute. You can make some external rule, as you've done, about what sort should be applied and under what conditions a row is counted or not, but then your sequence data is a product of the external rule and does not really belong in the row. It's more like a kind of meta-data, it's data about your data. See what I mean?

A question I have is, why do you need this sequential number? What value does it add to you data? Obviously we can start to see that keeping it accurate is going to be a headache. So now, does it deliver some kind of value that makes enduring the headache worth while?

My answer is no. Abandon the sequential number. If you must have it in a report or something, calculate it in the report, but I would abandon it as something to store in a table, and bother to constantly validate.

Jezarooo · Dec 8, 2016

Hi Mark,

Thanks for getting back to me!

There are unfortunately a number of reasons in my business context why knowing the exact lesson number on each date is needed, and being able to update that data in a way that affects all subsequent lessons easily would be good.

- I'd like to use that data in a calendar format to support the admin team when it comes to preparing learning resources each week (this could be a report list though)

- I'd like to keep track of the end dates of courses, which will change depending on the kind of cancellations. Normally we reschedule, but sometimes we do not (depending on the Lesson Status dropdown), this would impact the lesson number and the last lesson number

- I'd like to build a later feature that will prompt users to create the extra lesson if needed at the end of the course cycle for every cancellation made. I also want to build a feature in Access that enables full courses to be created quickly based on the set of initial parameters i.e. Monday 4pm - 6pm, Wednesday 5pm - 7pm, Total Lessons: 40, Client Name: X, Start Date: Y.

- Tracking course end dates is a big one, as it impacts the planning of sales actions and end of course academic actions.

With those in mind, would you still recommend a report? I don't mind taking on a challenge

Also I think the order of the lessons could be tied to the sequence of dates, the number of lessons in the tblCourses, and the status of each record (rather than the Primary Key) - but I'm just not sure how and what features of Access would be best to learn about to achieve the result.

Thanks in advance!

J

MarkK · Dec 8, 2016

To create the sequence you would probably need to take 2 steps,
1) null numbers you know are wrong, so you could write an update query like...

Code:

UPDATE tblClass SET LessonNumber = Null WHERE SessonStatus = 'Cancelled'

2) Open a sorted recordset of rows that should be included in the number scheme, and loop thru each one, and update each record.

Code:

const SQL as string = _
   "SELECT LessonNumber " & _
   "FROM tblClass " & _
   "WHERE NOT SessionStatus = 'Cancelled' " & _
   "ORDER BY ???"
dim i as integer

with currentdb.openrecordset(sql)
   do while not .eof
      i = i + 1
      .edit
      !LessonNumber = i
      .update
      .movenext
   loop
   .close
end with

But the hard part now is to ensure that this operation is performed without fail in cases where it needs to be performed. How do you trigger it? At startup each day? On a timer? When a course is cancelled?

Sequential numbers are a headache. They don't represent a fact about each data point, they only represent facts about the whole list, so they don't really belong in each row. Really, they belong to the presentation of the data as a list.

Jezarooo · Dec 9, 2016

Hi Mark,

Thanks for getting back to me!

As I mentioned, I'm trying to learn so still a little shaky on some of the core concepts but I've been working through tutorials recently and I think get most of the code you kindly sent over.

However, I have a few questions to help me get a better grasp:

Where would I place the two sections of code? Would they both be placed in the same VBA module?
For the update query for null values, would that not impact the record set loop? Or will the recordset i + 1 function skip over null values and only create the sequence from the previous integer? (if that makes sense)

In terms of when this would be triggered, I was hoping for this to be from an afterupdate event on the lessonStatus field. So when the lessonStatus is changed then the code will run. Regarding the ordering of the records, then that would be based on the date of the lesson, the first date is always going to be lesson 1, the next uncancelled lesson will always be lesson 2 etc.

I did some reading and I'm wondering if it would also be necessary to run a filter on the recordset so that it is only dealing with changes to the specific course being viewed? If I cancel a lesson I only want to impact the sequence for lessons in the specific course where that has happened.

In terms of presentation, this will be in a subform of a course record form. It would be presented as a list, but with the ability to change a status field. I'm not sure if this is what you mean by presentation as a list?

Thanks again for your help!

J

Minty · Dec 9, 2016

Just my 2p worth here, as Markk has stated you will need to ensure that any change, made anywhere in your database keeps these numbers up to date. Over time and with added complexity this will become very difficult if not impossible to maintain. Especially if you decided to add another lesson into a course list.

I think (and I haven't thought this through completely) I would have another table with the IntendedLessonDate of each class ID, along with a cancelled flag. You can then use this to present the list of classes in the correct order. Store the Lesson Number as a fixed field for that course group in the classes table. you can then display that linked back to the list of dates excluding the cancelled ones and you will always have a up to-date ordered list.

Put it down on bits of paper, move them around and see if it makes sense.

Grumm · Dec 9, 2016

I think that since the order of the records is determined by a date, you don't need to store that number in the database. Just calculate it when you do a select on the data.
example :

Code:

select Date, ROW_NUMBER() OVER (ORDER By Date DESC) LessonNumber, LessonStatus

Jezarooo · Dec 9, 2016

Minty said:
Just my 2p worth here, as Markk has stated you will need to ensure that any change, made anywhere in your database keeps these numbers up to date. Over time and with added complexity this will become very difficult if not impossible to maintain. Especially if you decided to add another lesson into a course list.

I think (and I haven't thought this through completely) I would have another table with the IntendedLessonDate of each class ID, along with a cancelled flag. You can then use this to present the list of classes in the correct order. Store the Lesson Number as a fixed field for that course group in the classes table. you can then display that linked back to the list of dates excluding the cancelled ones and you will always have a up to-date ordered list.

Put it down on bits of paper, move them around and see if it makes sense.

Hi Minty,

Thanks for chipping in

I'm a little unsure of what you mean regarding storing the Lesson Number as a fixed field for that course group? Think I follow you up to there though.

Thanks,

J

Jezarooo · Dec 9, 2016

Grumm said:
I think that since the order of the records is determined by a date, you don't need to store that number in the database. Just calculate it when you do a select on the data.
example :

Code:

select Date, ROW_NUMBER() OVER (ORDER By Date DESC) LessonNumber, LessonStatus

Hi Grumm,

Thanks for getting back to me!

That seems to make sense. However, I'm unsure how I would then make apply this. I can follow the code, but not sure where I can make use of it.

My end goal is to have a Courses form, with a subForm listing Lessons with a lesson status field that can be adjusted to cancelled. How would you recommend implementing your lesson number solution in terms of UI? Can I present the data where I would like in an interactive way?

It's my first database project and first stab at learning VBA so still a little shaky on the core concepts.

Any help kindly appreciated!

Thanks,

J

static · Dec 9, 2016

It might be worth backing up to define what these fields are.

A class. Group of students / period of time.
A lesson. Subject matter.

Both lessons and classes should have their own tables and unique ID's. So

tblTrainingClasses:
ClassID_PK | LessonNumber | LessonStatus (dropdown)
-------------------------------------------------------------------------------
1 | 1 | -
2 | 2 | -
3 | N/A | Cancelled
4 | 3 | -

What does the above mean? Did Class 4 replace class 3 or did the students take time out?

Lesson number should be an ID from the Lessons table.

Classes have start and end times which give you the order they run in..

tblTrainingClasses:
ClassID_PK | LessonID | Start | End | LessonStatus (dropdown)
-------------------------------------------------------------------------------
1 | 1 | 1/1/2016 09:00 | 1/1/2016 09:30 | Complete
2 | 2 | 1/1/2016 09:30 | 1/1/2016 10:30 | Complete
3 | 3 | 1/1/2016 10:30 | 1/1/2016 12:30 | Cancelled
4 | 4 | 1/1/2016 10:30 | 1/1/2016 12:30 | In Progress

Jezarooo · Dec 9, 2016

static said:
It might be worth backing up to define what these fields are.

A class. Group of students / period of time.
A lesson. Subject matter.

Both lessons and classes should have their own tables and unique ID's. So

tblTrainingClasses:
ClassID_PK | LessonNumber | LessonStatus (dropdown)
-------------------------------------------------------------------------------
1 | 1 | -
2 | 2 | -
3 | N/A | Cancelled
4 | 3 | -

What does the above mean? Did Class 4 replace class 3 or did the students take time out?

Lesson number should be an ID from the Lessons table.

Classes have start and end times which give you the order they run in..

tblTrainingClasses:
ClassID_PK | LessonID | Start | End | LessonStatus (dropdown)
-------------------------------------------------------------------------------
1 | 1 | 1/1/2016 09:00 | 1/1/2016 09:30 | Complete
2 | 2 | 1/1/2016 09:30 | 1/1/2016 10:30 | Complete
3 | 3 | 1/1/2016 10:30 | 1/1/2016 12:30 | Cancelled
4 | 4 | 1/1/2016 10:30 | 1/1/2016 12:30 | In Progress

Hi Static,

Thanks for chipping in!

Probably first best to point out I may have mixed labels here between Classes and Lessons which are identical - sorry if that led to confusion. The basic structure will be Course with a one to many relationship with Lessons.

In a sense Lesson 4 did replace Lesson 3. This is for corporate training in the context of long courses (language development), and where it is common for clients to cancel their lessons. When that happens we don't move onto the next lesson and not teach the missed lesson, we add an additional lesson onto the end of the course and complete lesson 3s lesson on what would originally have been lesson 4s date / time.

There are a number of reasons I'm trying to chase this functionality, but one of the main ones is that it's not just a case of a lesson being a bunch of students, it's also a case of a lesson involving the preparation and delivery of a set of materials by our admin team - and this is where staying on top of the lesson number and date becomes most practical for me.

I'm perfectly happy to deal with the presentation of this data in a query or a report (if either can achieve the affect I'm seeking - still need a little support guiding me on that), but I would also like - if this is possible - for it to be presented when interacting with the course record view of a form so my staff can quickly search a client and course, then view the course parameters and also view the list of lessons.

I was initially seeing this as a CourseForm with a LessonSubForm style structure that would ideally also allow users to also cancel lessons presented in that subform list as needed which would automatically trigger a rebuild of the lesson order list (which is where I think it's getting complicated :/)

I think what I am trying to work out what to do is:

Present a record set of lessons related to a course (a subform seems to handle this easily enough without any code needed)
Trigger a VBA function / module thingy (not really there with the correct vernacular for coding yet lol) when the Lesson Status field is changed to a certain value ("cancelled")
Then the VBA will go through the courses record set. This I think will mean that the code will have to create a filtered recordset based on the name of the course or course ID (so we don't make edits to the wrong set) and ordered this by date.
Then updates to the Lesson Number field of all records in the selected ordered set will be made by adding 1 to the previous record in the ordered list if that record is not set with a lesson status as "cancelled" otherwise it will add 1 to the record before that and so on
Then ideally that will show in the show in the subform because the records have been updated.

Despite it being fiddly to achieve does this sound essentially correct to anyone?

Cheers,

J

static · Dec 9, 2016

Jezarooo said:
Then updates to the Lesson Number field of all records in the selected ordered set will be made by adding 1 to the previous record in the ordered list if that record is not set with a lesson status as "cancelled" otherwise it will add 1 to the record before that and so on

Then ideally that will show in the show in the subform because the records have been updated.

Despite it being fiddly to achieve does this sound essentially correct to anyone?

Cheers,

J

If the number sequence is based on filtered data in date order it can be calculated. Don't store calculated data unless you need to do some sort of audit trail.

You could use a custom function and a static variable in your query...

Code:

Public Function QRecNum(v, Optional resetandload As Boolean) As Long
    Static qRow As Long
    If resetandload Then
        qRow = 0
        DoCmd.OpenQuery "query1"
    Else
        qRow = qRow + 1
        QRecNum = qRow
    End If
End Function

Call it from your query.

Code:

SELECT Table1.*, QRecNum([id]) AS RecordNo FROM Table1;

Static variables retain their values between each call so you need to reset them, in this case by setting 'resetandload' to true which also loads the query. i.e.

Code:

call QRecNum("",true)

MarkK · Dec 9, 2016

I would be tempted in this situation to use a query with a subquery, and not store the sequential number. I think a query like this would be simplest and fastest...

Code:

SELECT q.*, ( 
   SELECT Count(sq.ClassID_PK)
   FROM tblClass As sq
   WHERE sq.End < q.Start 
      AND Not sq.LessonStatus = 'Cancelled' 
   ) + 1 As LessonNumber
FROM tblClass As q

That query counts the uncancelled lessons that end before the current lesson starts. Then you need to add 1. You would also need to only count the classes for a particular course, so you still need another expression in the subquery WHERE clause, but this should give what you want.

Changing value of fields a set of records based on a field value change (1 Viewer)

Jezarooo

New member

MarkK

bit cruncher

Jezarooo

New member

MarkK

bit cruncher

Jezarooo

New member

Minty

AWF VIP

Grumm

Registered User.

Jezarooo

New member

Jezarooo

New member

static

Registered User.

Jezarooo

New member

static

Registered User.

MarkK

bit cruncher

Similar threads

Users who are viewing this thread