Checking for duplicates using two fields

[bonoman]

Hmm, I need some help.

I have a table that is keeping student report cards and I need to check if a student Id has already been entered for a class already.

The situation is that there are several students per class, and several hundred classes. Each student has a unique ID number and each class has a unique Class Code. The primary key is an autonumber. A student may have taken several classes (so I must allow duplicates in this field) and there are several students to a class (so I must allow duplicates in this field too).

I like to have all the report cards in one place so we can easily track student progress and records.

Everything with the database has been proceeding smoothly for several months now, but my data entry staff have made a few mistakes with the entry and have entered a student Id twice for the one class.

Can I create a warning message in my form that tells the staff that they have already entered a report for this student in this course, and to check the id number and class code again?

Thanks for any help you can offer.
tm

 

[dcx693]

There are two ways I can suggest to solve your problem.

1- design your class assignment table so that the combination of ClassCode, studentID (and session code, or semester code - whatever you use to identify when the class took place) is a primary key. That way, the unique combination of Class, Student, and Session can be entered only once into the table.

2- if you can't change the design, then use some sort of function to check if the combination already exists in the table. DCount is usually what is used. Something like:
If Dcount("[StudentID]","ClassTable","[StudentID]=" & Me.cboStudentID & " AND [ClassCode]=" & Me.cboClassCode & " AND [Session]=" & Me.cboSession Then
Beep
MsgBox "That student is already registered for that class"
Exit Sub 'or whatever you need to do to cancel out of the process
End If

 

[bonoman]

Hey Dcx693,

Thanks for the reply. Im pretty new to all this. I think the second option is the go, but I have no idea how to go about it. I have had a look around the
board for advice on functions and Dcount, and cant find any.

I've got a few questions (!) and the first one is How do I enter the function and where?

thanks again
tm

 

[dcx693]

I would put the Dcount into the Before Update event of the form where you enter the info. That way, before a record is updated, a check will be made to see if a student already exists for the same class and session.

The Before Update event of the form will "fire" anytime you edit a record and just before it gets saved. There are other places to trigger the check (you could have it check for duplicate entries anytime you select a student, class, or session, but using the Before Update is a bit simpler).

If you've never used VBA code, read some of the Access online help on basic VBA coding.

If you do use Before Update, replace the line where I wrote "Exit Sub, etc..." with:
Cancel=True

Also, realize that I made some guesses in that formula for what your tables and fields would be called. Make sure to replace those with the actual names of those object within your database.

Write back with your questions.

 

[bonoman]

ok, I seem to be making some progress, and have put the code in to the before update section.

However the debugger comes back at me about the spacing in the code.

I have:

If DCount("[Student ID]", "Classes - Student Lists - Completed", "[Student ID]=" & Me.cboStudent ID & " AND [Class Code]=" & Me.cboClass Code) Then
Beep
MsgBox "That student is already registered for that class"
Cancel = True
End If

Now, in your original there is no spaces in the names - was it a mistake to put spaces in my field names???

If I take the spaces out of the code, then 'data member not found' for "cboStudentID" appears and then if I put the space back in "ID" becomes highlighed with the message 'expected list sperator or )'

If this is not the problem, any suggestion as to what it might be?]

thanks in advance

 

[dcx693]

It's good programming technique not to put spaces into field names, only because it makes things a bit more complicated when referring to things, but not terribly so.

If there are any "special" characters in your field names, like spaces, then you need to enclose the entire name within [ ] brackets, like this:

If DCount("[Student ID]", "Classes - Student Lists - Completed", "[Student ID]=" & Me.[cboStudent ID] & " AND [Class Code]=" & Me.[cboClass Code]) Then

 

[bonoman]

Ok, thanks for the tip about the field names. Will begin that job next time.

The code seems to be working at the right time, but comes up with this error now

"Microsoft Access cannot find the field "l" referred to in your expression"

And then with the debugger opens, this line is highlighted yellow:

If DCount("[Student ID]", "Classes - Student Lists - Completed", "[Student ID]=" & Me.[cboStudent ID] & " AND [Class Code]=" & Me.[cboClass Code]) Then

Any advice?

thanks
tm

 

[dcx693]

I don't see anything obviously wrong syntactically with the Dcount function. I would suggest taking out the criteria and seeing what the function returns. Do this by copying the function, opening the form, then hitting Control+G. That will bring up the immediate window. Place this into the immediate window:
? DCount("[Student ID]", "Classes - Student Lists - Completed")
that will just print out a count of all studentID's.

Next, add one of the criteria back in, like this:
? DCount("[Student ID]", "Classes - Student Lists - Completed", "[Student ID]=" & Me.[cboStudent ID])
and see if that works. If so, then there's some problem with the final criteria.

Also, you should change your expression to something like:
If DCount(.....) >0 Then

Right now, your DCount function won't do a lot unless it's equal to 0.

 

[samonwalkabout]

bonoman- check and re check the syntax but i think you have a null value somewhere. Try checking the fields to be used for NULL first.

if isnull([cboStudent ID]) then
msgbox "null student id"
else

if isnull(cboClass Code) then
msgbox " null cboclass"

else

"your code here"


dcx693 - Can you use this type of function for to stop duplicate vaules when using multi select list boxes to run code i.e

Dim i as variant

ect
ect

for each i

If DCount("[Student ID]", "Classes - Student Lists - Completed", "[Student ID]=" & Me.[ListboxValue] & " AND [Class Code]=" & i ) >0 Then

ect
ect

next i

 

[dcx693]

salmon, I don't see why not. Just make sure to refer to the items in the multiselect box correctly. Something like:

Dim var As Variant

    For Each var In Me.lstBox.ItemsSelected
        If DCount( _
            "[Student ID]", _
            "Classes - Student Lists - Completed", _
            "[Student ID]=" & Me.lstBox.ItemData(var)) > 0 Then
    Next var

 

[samonwalkabout]

Just tried it with some old DB's works a treat, i will be using that again in the future!

 

[dcx693]

Cool. Just realized I'd forgotten the "End If" portion of that code.

Dim var As Variant

    For Each var In Me.lstBox.ItemsSelected
        If DCount( _
            "[Student ID]", _
            "Classes - Student Lists - Completed", _
            "[Student ID]=" & Me.lstBox.ItemData(var)) > 0 Then
                'do something
        End If
    Next var

 

[bonoman]

This is starting to go over my head!

I have checked for null values and that is all fine, everything is where it should be. I've added that last bit "> 0", and when I first add the code its fine, but when i go to test it out on the form, it brings up that same message "runtime error 2465" and the "Microsoft Access cannot find the field "l" referred to in your expression" bit.

So, for this one the code looks like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[Student ID]", "Classes - Student Lists - Completed", "[Student ID]=" & Me.[cboStudent ID] & " AND [Class Code]=" & Me.[cboClass Code]) > 0 Then
Beep
MsgBox "That student is already registered for that class"
Cancel = True
End If
End Sub

Any sugggestions?

Also, I'm looking at other solutions (because this one is driving me crazy), and is it possible that in the query behind the form I could combine the two fields (Student ID) and (Class code) and then have a criteria that says No duplicates in that field or that every field must be unique? Or am I way off track? If it is possible, how do I add the criteria "No duplicates"?

Thanks for your advice

tm