VBA Skipping the Field I Want It To Consider

[sidra]

Hi everyone!

I've been creating an MS Access database to store info on venues, courses, students, assessors, and enrolments. I've completed the database itself but as part of my assignment I need to add an improvement at the end and I chose to add a label that shows the user how many fields they have left to add in the form. The save button also responds to this and unlocks once all required fields have been added. My issue is that the VBA I am using is not responding to the EnrolmentDate field, but instead wants the user to click on the EnrolmentID field (which is an AutoNumber PK). The other two required fields, StudentID and CourseID, respond properly. Here's the VBA used in the form's On Current:

Private Sub CourseID_AfterUpdate()


    ' Populate AssessorID based on the selected CourseID
    Me.txtAssessorID.Value = DLookup("AssessorID", "Courses", "CourseID=" & Me.CourseID.Value)
    Call UpdateProgress


End Sub


Private Sub CourseID_Click()


End Sub






Private Sub Form_Current()
    Call UpdateProgress
End Sub

Private Sub UpdateProgress()    Dim TotalFields As Integer
    Dim FilledFields As Integer
    Dim RemainingFields As Integer


    ' Set the total number of fields (exclude EnrolmentID and Achieved)
    TotalFields = 3 ' Adjust based on required fields


    ' Check required fields
    If Nz(Me.StudentID, 0) <> 0 Then FilledFields = FilledFields + 1      ' Combo box
    If Nz(Me.CourseID, 0) <> 0 Then FilledFields = FilledFields + 1      ' Combo box
    If Not IsNull(Me.EnrolmentDate) And Me.EnrolmentDate >= Date Then FilledFields = FilledFields + 1




   ' Date field


    ' Calculate remaining fields
    RemainingFields = TotalFields - FilledFields
    Debug.Print "Filled: " & FilledFields & " | Remaining: " & RemainingFields


    ' Update label
    If RemainingFields > 0 Then
        Me.lblRemainingFields.Caption = RemainingFields & " field(s) remaining"
    Else
        Me.lblRemainingFields.Caption = "All fields complete!"
    End If


    ' Enable Save button only when all fields are complete
    Me.Command20.Enabled = (RemainingFields = 0)
End Sub


Private Sub StudentID_AfterUpdate()
    Call UpdateProgress
End Sub


Private Sub EnrolmentDate_AfterUpdate()
    Call UpdateProgress
End Sub

[June7]

Why would user be forced to "click" on EnrolmentID? I don't see anything in code doing that. Also see no reason for "not responding to" EnrolmentDate. Have you step-debugged?

This can probably done with an expression in textbox and no VBA except for enabling button.

Why save AssessorID? Is Course selected from a combobox? Include AssessorID in combobox RowSource and then just reference that column by index in textbox expression: =[comboboxname].Column(2)

If you want to provide db for analysis, follow instructions at bottom of my post.


BTW, correct spelling for "enrolment" is "enrollment".

[sidra]

Hello! I honestly have no idea! This is the first database I've ever made, and I was doing well with it until this part! The AssessorID bit is to fit the assignment's requirements that state that only one assessor can teach one course- its to ensure that 1 to 1 relationship I'll go ahead and attach the database, thank you!

[sidra]

I've attached the database to this reply, please let me know if you can access it!

[June7]

First of all, AssessorID is in Courses so there is no need for AssessorID in Enrollments. Retrieve AssessorID from Courses table in queries.

I suspect the relationship is really 1-to-many: a course can have only one assessor but each assessor can associate with multiple courses. If these entities are truly in a 1-to-1, then Assessors and Courses could be one table.

Your code requires EnrolmentDate to be >= Date - it is not when opening to existing record so of course the control is not counted (I should have recognized from posted code - !). If you want to allow edit of existing and keep older dates, modify code. Eliminate this criteria and do a separate test to validate the date input to a new record with EnrolmentDate BeforeUpdate event.

If Me.NewRecord Then
    If Me.EnrolmentDate < Date() Then
        Cancel = True
        MsgBox "Date must be >= today."
    End If
End If

Could set DefaultValue property to Date() so it will automatically populate for new record.


Strongly advise not to use spaces in any names for anything, including forms.