Form/Table get a MAX of a field based on combobox filter (1 Viewer)

Ports

Registered User.
Local time
Today, 11:05
Joined
Jun 30, 2019
Messages
64
Hi,
I have a few fields in a form and a corresponding table. The first element of a form is a combo box. Based on the selection, the "Course Sequence" field is being populated based on another field. Where I'm stuck is the third field which is supposed to get a MAX of type of courses of a type selected in the combo box.

I've attached the db file but just for reference, the concept is as follows:


The table is:


Code:
LAIM | CourseSequence | Count
1234   | AB                      | 1
1234   | AB                      | 2
4321   | AC                      | 1
1234   | AB                      | 3


These are manually typed in a table but If I were to use the form now adding another cohort of learners and choose LAIM in the combobox, eg. 1234, the Course Sequence would be automatically populated as "AB" but I'm at a complete loss how to find MAX(Count) Where CourseSequence = AB (if any of the instances of AB alredy exist) and add 1 to it so it becomes AB 4. If there are no instances of that particular CourseSequence it'd just be 1.


So far I've added the On Change Event to the LAIM combo box (cmbLaim) to update the Code Sequence field (txtBoxCodeSequence on the form)



Code:
Private Sub cmbLaim_Change()
    Me.txtBoxCodeSequence.Value = Me.cmbLaim.Column(0)
End Sub


Please advise. Hope that makes sense.
 

Attachments

  • Generator - test.zip
    58.5 KB · Views: 86

June7

AWF VIP
Local time
Today, 10:05
Joined
Mar 9, 2014
Messages
5,423
Generating a custom identifier is a common topic.

Are LAIM and CodeSequence unique to each other. I mean, a CodeSequence will not associate with multiple LAIM? From what I see in LAims table, answer is yes. Should not duplicate CTYPE values into CourseCodeGenerator. Or save CTYPE and not LAims. Need to define primary key in LAims table.

Use DMax() domain aggregate function to pull maximum Count from table.

Me.Count = DMax("[Count]","CourseCodeGenerator","LAims='" & Me.cmbLaim.Column(1) & "'")

Count is a reserved word and should not use reserved words as names for anything.

What if a record is deleted? The Count sequence could be out-of-sync. An alternative to saving count in table is to calculate it when needed. On a report, use Sorting&Grouping feature and textbox RunningSum property.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:05
Joined
May 7, 2009
Messages
19,169
It can be generated, yes.
my question is, do you input each learners 1 by 1.
thereby creating a 1 record for each learner in the CourseCodeGenerator table?
or do you want to update the table with the Max("[Count]")+1 if the Provider and CTYPE is already in the table?
I did not see any Learner table, or do you have one.
how can he be tied to a specific course?
 

Ports

Registered User.
Local time
Today, 11:05
Joined
Jun 30, 2019
Messages
64
Generating a custom identifier is a common topic.

Are LAIM and CodeSequence unique to each other. I mean, a CodeSequence will not associate with multiple LAIM? From what I see in LAims table, answer is yes. Should not duplicate CTYPE values into CourseCodeGenerator. Or save CTYPE and not LAims. Need to define primary key in LAims table.

Use DMax() domain aggregate function to pull maximum Count from table.

Me.Count = DMax("[Count]","CourseCodeGenerator","LAims='" & Me.cmbLaim.Column(1) & "'")

Count is a reserved word and should not use reserved words as names for anything.

What if a record is deleted? The Count sequence could be out-of-sync. An alternative to saving count in table is to calculate it when needed. On a report, use Sorting&Grouping feature and textbox RunningSum property.


Yes, the LAIM and CodeSequence are (or should be) unique to each other. The table in this test database hasn't been cleaned up yet. In the end, there will be one LAIM corresponding to one CodeSequence.


Thanks for the formula. I'll change the Count to something else. Anyway, I don't think it's the right word for that. I think I'll change CourseSequence to CodeRoot and Count to Sequence. I think that would be more appropriate.


What if the record is deleted?
I was thinking about that. To be honest, once a course code is created, there should be no reason to delete it. I actually wanted to lock the form so that the user cannot delete/amend the previously created records. Having said that, calculating the MAX when needed might be an option. I would be creating a query out of the table anyway.
 

Ports

Registered User.
Local time
Today, 11:05
Joined
Jun 30, 2019
Messages
64
It can be generated, yes.
my question is, do you input each learners 1 by 1.
thereby creating a 1 record for each learner in the CourseCodeGenerator table?
or do you want to update the table with the Max("[Count]")+1 if the Provider and CTYPE is already in the table?
I did not see any Learner table, or do you have one.
how can he be tied to a specific course?




No, this is just for Course Code creation for cohorts of learners. Then learners will be assigned one of those course codes. There will be nothing about individual learners in the CourseCodeGenerator table.


In the end there will be 3 main tables:
- Learners (learner id and personal details)
- Actual enrolments (each row will have a learner ID and corresponding course identifier which is system generated (sometimes multiple rows per learner if enrolled on multiple courses)
- CourseCodes table (this one) which will have a much more descriptive course codes


The idea of CourseCodeGenerator is actually to automate a lot of things related to course planning. The table (and a subsequent query) will calculate a number of things. The administrator uses the form to enter which qualification another cohort of learners will be (future tense!) enrolled on. They input a start and end date, provider. All the behind the scenes calculations will result in a course code as follows:


ABC13-XPP19J
ABC - Course Sequence identifying the course/subject/level
13- it's the 13th cohort ever enrolled on this course/subject/level (this is where I'm stuck now)

X- funding stream (will be just a select box in a form
PP - Short code of a provider
19 - course started in 2019 (extract from the start date)
J - the course started in January (extract from the start date


Additionally, the course code generator table/query will calculate automatically the following information that are not included in the course code but are esssential for enrolments:
- how many sessions are there between the start and end date (eg. how many working mondays are there between the start date and end date)
- how many planned learning hours are there for this cohort (number of sessions x hours per each session (to be inputted in a form)
- how many of those hours fall in this academic year and how many in the next one (if the course spans over 2 academic years), etc.


It'll calculate a lot of things that are tedious and were done manually by my predecessor. Once a course code (and accompying information) is done, there'll be a report that will be sent so that particular cohort of leanrers can be officially enrolled on our system with that information.


Hope that makes sense.
 

Cronk

Registered User.
Local time
Tomorrow, 05:05
Joined
Jul 4, 2013
Messages
2,770
If you want to ensure that LAIM and CodeSequence are unique, create a composite index on both fields (search for "create a composite unique index) and set its property to Unique. That way Access will prevent a duplicate occurring.
 

Users who are viewing this thread

Top Bottom