How to check for already existing records

[Hakello]

Hello,

I have a database where teachers can create new tests with the click of a button so I need a dynamic way to add new results for the students.

To do this I made the following button:

Private Sub btnToetsen_Click()

Dim sSQL As String
Dim toetsQuery As String
Dim studentindex As Integer
Dim opleidingid As Integer

    studentindex = Me.tbStudentindex
    opleidingid = Me.tbOpleidingid

    toetsQuery = "SELECT Student.studentindex, Toets.toetscode FROM (Koppelvak INNER JOIN Toets ON Koppelvak.vakcode = Toets.vakcode) INNER JOIN Student ON Koppelvak.opleidingid = Student.opleidingid WHERE (((Student.studentindex)=" & studentindex & ") AND ((Student.opleidingid)=" & opleidingid & "))"
    sSQL = "INSERT INTO Resultaat (studentindex, toetscode)" & toetsQuery
    MsgBox (sSQL)
    
    DoCmd.RunSQL (sSQL)
    DoCmd.Requery
      
End Sub

The button works perfectly but it has a major flaw, I don't know how to check if a certain combination already exists (the primary key is generated). So I could make it a primary key consisting of two foreign keys and disable warnings or, what I preffer, is a way to handle this in the code. My coding isn't very good though and I don't really know what the best way would be to handle this.

So my question is:
How do I check if my INSERT INTO doesn't add a combo of [studentindex] and [toetscode] that already exists.

 

[PeterF]

Use Dcont to check if the combination is already present like:

If Dcount("*", "Resultaat", "studentindex = " & studentindex  " and toetscode = " & opleidingid ) > 0 then
    msgbox "This record already exists"
else
...your Sql code
End if

 

[Hakello]

Thanks Peter, smart to simply count any matches

Here's the final code I used (in case people with similiar issues ever find this thread)

Private Sub btnToetsen_Click()

Dim insertSQL As String
Dim toetsQuery As String
Dim toetsCode As String

Dim studentindex As Integer
Dim opleidingid As Integer

Dim db As DAO.Database
Dim rs As Recordset

    DoCmd.SetWarnings (WarningsOff)

    studentindex = Me.tbStudentindex
    opleidingid = Me.tbOpleidingid
    toetsQuery = "SELECT Student.studentindex, Toets.toetscode FROM (Koppelvak INNER JOIN Toets ON Koppelvak.vakcode = Toets.vakcode) INNER JOIN Student ON Koppelvak.opleidingid = Student.opleidingid WHERE (((Student.studentindex)=" & studentindex & ") AND ((Student.opleidingid)=" & opleidingid & "))"

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(toetsQuery, dbOpenSnapshot)
    rs.MoveFirst

    Do
        toetsCode = rs!toetsCode
        If DCount("*", "Resultaat", "studentindex = " & studentindex & " AND toetscode = '" & rs!toetsCode & "'") > 0 Then
            'do nothing
        Else
            insertSQL = "INSERT INTO Resultaat (studentindex, toetscode) VALUES (" & studentindex & ", '" & toetsCode & "')"
            DoCmd.RunSQL (insertSQL)
        End If
        rs.MoveNext
        Loop While Not rs.EOF
        DoCmd.SetWarnings (WarningsON)
        Me.Requery
End Sub