By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Help with Error Handling - 2 Keyed Fields

P: 2
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).

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  3.     DoCmd.SetWarnings False
  5.     Dim Answer As Variant
  6.     Answer = DLookup("[CourseID]", "OOSMap", "[CourseID] = '" & Me.CourseID & "'")
  7.     If Not IsNull(Answer) Then
  8.     MsgBox "Course already exists in this Map," & vbCrLf & "Please select another course", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
  10.  Cancel = True
  11.  Me.CourseID.Undo
  13.     DoCmd.SetWarnings True
  15.  Else:
  16.  End If
  17. End Sub
Thanks for any suggestions... and yes... I am not a programmer.
Aug 26 '18 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 2.5K+
P: 3,284

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:

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!
Aug 27 '18 #2

Expert 100+
P: 1,430
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW tblSeat.SeatID, tblSeat.Seat AS Unallocatedt
  2. FROM tblSeat
  3. WHERE (((tblSeat.SeatID) 
  4. Not In 
  5. (SELECT tblInstr.InstrSeatID FROM tblSeat 
  6. INNER JOIN (tblHRm INNER JOIN (tblStudents 
  7. INNER JOIN tblInstr ON tblStudents.StudentID = tblInstr.InstrStudentID) 
  8. ON tblHRm.HRmSectionID = tblStudents.StudentHRmSectionID) 
  9. ON tblSeat.SeatID = tblInstr.InstrSeatID 
  10. WHERE (((tblInstr.InstrDate)=Format([Forms]![frmDailyInstruction]![txtTheDate],"mmm/dd/yyyy")) 
  11. AND ((tblHRm.HRmABPeriodID)=[forms]![frmDailyInstruction]![cboABPeriodID])) ORDER BY tblInstr.InstrSeatID )))
  12. ORDER BY tblSeat.SeatID;
The critical thing is the "Not In(" statement

Hope that gets you started

Aug 27 '18 #3

P: 2
Thanks a lot, I thought an Not In or Union statement would work better. I'll give it shot ASAP.
Aug 28 '18 #4

Post your reply

Sign in to post your reply or Sign up for a free account.