Checking for duplicate records

[GreenshootProgrammer]

Solved:

Private Sub txtCourseID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblCourse", "CourseID='C" & Me.txtCourseID.Value & "'") > 0 Then
    Cancel = True
    MsgBox "This Course ID is already being used."
    Me.txtCourseID.Undo
End If
End Sub

The "C"000 input mask must have been removing the C from the textbox value.

 

[RuralGuy]

Change your BeforeUpdate event to:
MsgBox "[" & Me.txtCourseID & "]"
If DCount("*", "tblCourse", "CourseID='" & Me.txtCourseID & "'") > 0 Then
...and let me know what is in the MsgBox, please.

 

[RuralGuy]

Why are you so reluctant to use an AutoNumber as the PK?

 

[GreenshootProgrammer]

Why are you so reluctant to use an AutoNumber as the PK?

I want the IDs to be PKs and I want the IDs to have a specific format.

 

[RuralGuy]

I want the IDs to be PKs and I want the IDs to have a specific format.

Why do the ID's have to be the PK? Is this some school exercise?

 

[GreenshootProgrammer]

Last year we had a database assignment with 5 scenarios to choose from, I'm doing the other scenarios I didn't choose now, so yea it was part of an assignment. BTW Thanks for all your help RuralGuy.

 

[nanscombe]

Rather than entering in a value and having to check for duplicates you could use of variation of creating a key with the DMAX() function to do it all for you.

Private Function nextId()
nextId = nz(DMax("ID", "tblCourse"), "C000") ' Gets largest existing value of ID
nextId = val(Mid(nextId, 2) & vbNullString) + 1 ' Derive lagrest numeric value and add 1
nextId = "C" & Format(nextId , "000") ' Reformat ID
End Function

Private Sub Form_BeforeUpdate()
If Len(Me.txtCourseID & vbNullString) = 0  then Me.txtCourseID = nextId
End Sub

For Students ...

Private Function nextId()
nextId = nz(DMax("ID", "tblStudents"), "S000") ' Gets largest existing value of ID
nextId = val(Mid(nextId, 2) & vbNullString) + 1
nextId = "S" & Format(nextId , "000")
End Function

 

[RuralGuy]

Most developers try and code systems for understandability and maintainability for even years later. You can see how much you had to jack the system around to accommodate your scheme and anyone that comes behind you will have just as much trouble understanding and maintaining it. It seems you declared a thread Solved once before and had to backtrack. Good Luck.

 

[GreenshootProgrammer]

Rather than entering in a value and having to check for duplicates you could use of variation of creating a key with the DMAX() function to do it all for you.

Private Function nextId()
nextId = nz(DMax("CourseID", "tblCourse"), "C000") ' Gets largest existing value of ID
nextId = val(Mid(nextId, 2) & vbNullString) + 1 ' Derive lagrest numeric value and add 1
nextId = "C" & Format(nextId , "000") ' Reformat ID
End Function

Private Sub Form_BeforeUpdate()
If Len(Me.txtCourseID & vbNullString) = 0  then Me.txtCourseID = nextId
End Sub

For Students ...

Private Function nextId()
nextId = nz(DMax("StudentID", "tblStudents"), "S000") ' Gets largest existing value of ID
nextId = val(Mid(nextId, 2) & vbNullString) + 1
nextId = "S" & Format(nextId , "000")
End Function

Fantastic!

 

[nanscombe]

I actually use a variant of that for naming and storing photographs.

ID is 001-02026505
Filename(s) are 001-02026505.(extension)
Path is {root}\001-02\026\001-020265xx\001-02026505.jpg

In my case 001-02 is the prefix and xxxxxx is an incrementing number.

When I reach 999999 I'll just change the prefix to 001-03.