Help with Error Handling - 2 Keyed Fields

[JetB]

Hello All,

Thanks for any info you might have.

I have an Access database that is tracking Student Grades. Part of the gig is mapping their future courses [CourseID].

What I'm trying to do is either narrow down a Combobox as courses are selected (remove a course if it has already been entered), or stay away from the Access built-in error messages.

One student can have multiple terms [ProgramID], and multiple courses per term [CourseID]. These two fields make up the Key on the Record source for OOSMap table.

I have a Main form (OOSMain) - a subform (OOSMap) - and a subform off OOSMap (OOSMapSubForm).

The code I have works for the [CourseID] but doesn't when the [ProgramID] is factored in. It won't let me enter a [CourseID] for a second map (required) if it exists in the first map (ignores the [ProgramID] part of the key).

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    DoCmd.SetWarnings False
 
    Dim Answer As Variant
    Answer = DLookup("[CourseID]", "OOSMap", "[CourseID] = '" & Me.CourseID & "'")
    If Not IsNull(Answer) Then
    MsgBox "Course already exists in this Map," & vbCrLf & "Please select another course", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.CourseID.Undo
 
    DoCmd.SetWarnings True
 
 Else:
 End If
End Sub

Thanks for any suggestions... and yes... I am not a programmer.

[twinnyfo]

JetB,

Welcome to Bytes!

First, it looks like your CourseID is a text value--or at least you are referring to it as a text value in your code (Line 6). If it is a text value that is fine, but usually "IDs" in database jargon is for a long integer (numeric) value. If this is the case, then you would want to remove the single quotes fro your DLookup() statement.

However, you don't provide enough information for us to troubleshoot your real problem:

JetB:
The code I have works for the [CourseID] but doesn't when the [ProgramID] is factored in. It won't let me enter a [CourseID] for a second map (required) if it exists in the first map (ignores the [ProgramID] part of the key).

It appears you have given us the code that works? If this is the case, there is nothing to troubleshoot. However, if you provide the code you have been working with that does not work, we will be glad to take a look at it and try to troubleshoot the problem areas.

Hope this hepps!

[PhilOfWalton]

There is a much more elegant way of handling your problem, but without seeing detailed table structures and your form, it will not be easy to give further guidance. An Image of your relationship pane and form would help.

Basically the method shows only the courses still available in the combo box, so you don't need to have a Msgbox about "the course already having been allocated", as it just doesn't show up.

Basically you create a query that shows which courses the student is enrolled for in that term, and a second query that lists all the coursed available that aren't in the first query.

Here is an example of query used as the RowSource of a combo box for seat allocation, oddly enough for a school application.

SELECT DISTINCTROW tblSeat.SeatID, tblSeat.Seat AS Unallocatedt
FROM tblSeat
WHERE (((tblSeat.SeatID) 
 Not In 
(SELECT tblInstr.InstrSeatID FROM tblSeat 
INNER JOIN (tblHRm INNER JOIN (tblStudents 
INNER JOIN tblInstr ON tblStudents.StudentID = tblInstr.InstrStudentID) 
ON tblHRm.HRmSectionID = tblStudents.StudentHRmSectionID) 
ON tblSeat.SeatID = tblInstr.InstrSeatID 
WHERE (((tblInstr.InstrDate)=Format([Forms]![frmDailyInstruction]![txtTheDate],"mmm/dd/yyyy")) 
AND ((tblHRm.HRmABPeriodID)=[forms]![frmDailyInstruction]![cboABPeriodID])) ORDER BY tblInstr.InstrSeatID )))
ORDER BY tblSeat.SeatID;
 

The critical thing is the "Not In(" statement

Hope that gets you started

Phil

[JetB]

Thanks a lot, I thought an Not In or Union statement would work better. I'll give it shot ASAP.