如何在SQL INSERT INTO语句中自动增加主键

Question

Private Sub btnAddInfo_Click()
On Error GoTo Error_Routine
'Declare variables
Dim intStudentID As Integer
Dim intTestID As Integer
Dim dblMark As Double
Dim intResultID As Integer
'Declare database
Dim db As DAO.Database
Dim rst As DAO.Recordset
'Set the database
Set db = CurrentDb
Set rst = db.OpenRecordset("Select ResultId FROM StudentResult ORDER BY RESULTID DESC", dbOpenDynaset)
'assign value to intResultID variable
intResultID = rst!ResultId
'Adds the additional 1 to the latest result id that was used
If Not rst.EOF Then
intResultID = intResultID + 1
End If
'Assigns value to variables
intStudentID = Forms!frmAdd!lstStudentID
strDescription = Forms!frmAdd!lstTest
dblMark = txtMark.Value
intTestID = Forms!frmAdd!lstTest
'Checks that Student ID has been selected
If Not IsNull(lstStudentID) Then
'Inserts new test record into StudentResult table
db.Execute "INSERT INTO StudentResult " _
& "(ResultId,StudentId,TestId, Mark) VALUES " _
& "('" & intResultID & "','" & intStudentID & "','" & intTestID & "','" & dblMark & "');"
End If
'Clears fields
txtMark.Value = ""
lstStudentID.Value = ""
lblExistingStudent.Caption = "Existing Student Name:"
'Closes database
Set db = Nothing

我正在尝试添加新记录。有一个包含4个测试的列表。ResultId是主键，是AutoNumber列。

如果所选的StudentID尚未为该TestId添加分数，则按钮可以添加测试分数。但是当我尝试添加之前输入的StudentId和TestId组合时，它不会添加新记录甚至更新现有记录。

StudentId和TestId都允许复制。我试过做这个计数器变量，但它没有工作。这是一门课，教授说学生应该可以重考，应该只添加一个新记录。

提前感谢你的帮助。请让我知道，如果你需要任何图片的形式，表，或更多的我的代码。

Answer 1

排除自动编号字段，并且不要用引号括住数字:

If Not IsNull(lstStudentID) Then
' Verify values:
Debug.Print "StudentID:", intStudentID, "TestID:", intTestID, "Mark:", Str(dblMark) 
'Inserts new test record into StudentResult table
db.Execute "INSERT INTO StudentResult " _
& "(StudentId, TestId, Mark) VALUES " _
& "(" & intStudentID & "," & intTestID & "," & Str(dblMark) & ");"
End If