Insert Record: Past dates only.

[visualAd]

I have form which uses VBA to manually insert a record into a database when a command buttons is clicked.

The table I am inserting the record into is an enrolments table. Each enrolment must include a student ID, a course code and course start date for the course. The course code and start date form a composite key in a courses table.

The code I have worked fine and INSERT's the record - no problem. However, earlier I added a couple of new courses to the courses database and attempted to use the enrolment form to add an enrolment for the course. I was confronted with the following runtime error:

You cannot add or change a record because a related record is required in table 'courses'.

Now this error would make a lot more sense if the course did not exist in the courses database; but it does. Granted, adding the record manually is successful. I then decided to find out a common factor for all the courses which I could not add enrolments for.

To my utter dismay it turns out my VBA code will only allow me intert a record for a course which started in the past. I have spent the last two hours trying to figure out why, to no avail - I am at my wits end.

The VBA code is as follows:

Private Sub cmdConfirm_Click()
    Dim StudentId As Long
    Dim CourseCode As String
    Dim StartDate As Date
    Dim enrolmentDate As Date
    Dim amount As Single
    Dim status As String * 1
    
    Dim conn As ADODB.Connection
    
    Set conn = Application.CurrentProject.Connection
    
    status = "P"
    enrolmentDate = Date
        
    StudentId = Parent!step1!studentdisplay!StudentId
    
    With Parent!step2!foundcourses
        CourseCode = !CourseCode
        StartDate = !StartDate
        amount = !CoursePrice
    End With
    
    strSql = "INSERT INTO enrolments (StudentId, CourseCode, StartDate, EnrolmentDate,"
    strSql = strSql & " Amount, Status) VALUES (" & StudentId & ",'" & CourseCode & "',#"
    strSql = strSql & StartDate & "#,#" & enrolmentDate & "#," & amount & ",'" & status
    strSql = strSql & "');"
    
    conn.Execute strSql
End Sub

If anyone can help I would be most grateful. Maybe I am missing something simple here, maybe access uses different SQL to other DBMS's.

Thanks in advance.