It doesn't sound like a locking issue at all to me. If it were, the user would be getting an ODBC error stating that the record could not be inserted (unless you've trapped it).
What does
mean?
I take it you're not using bound forms. Have you considered that the VBA logic that inserts the records is wrong? Are you willing to post the VBA code here?
This is the executing code when pressing that button:
Private Sub cmdNewStudent_Click()
On Error GoTo Finish_NewStudent
Dim strSQL As String, rst As DAO.Recordset, studentID As String, NewGuardianID, dbs As DAO.Database
Dim i As Long, CurrentYear As Integer, IDNum As String
Dim Region As String
CurrentYear = DLookup("CurrentYear", "tCurrent")
Region = DLookup("DatabaseID", "tCustomization")
Set dbs = DBEngine(0)(0)
DoCmd.Echo False
DoCmd.Hourglass True
If IsNull(GuardianID) Then
'if needed set new Guardian ID number
GuardianID = "G" & Region & Right(1000000 + GetNewGuardianID, 6)
'create tGuardian record
strSQL = "
INSERT INTO tGuardians ( ID, GuardianFirst, GuardianLast, [Update], AwardYear ) " _
& "VALUES ( '" & GuardianID & "', " & AddQuotes(FirstName) & ", " & AddQuotes(LastName) _
& ", #" & Date & "#, " & StartYear & " );"
ActionQuery strSQL
'create tguardianyearly record
strSQL = "
INSERT INTO tGuardianYear ( GuardianID, GuardYear, QualifyIncome ) " _
& "VALUES ('" & GuardianID & "', " & CurrentYear & ", 'None' );"
ActionQuery strSQL
'Write ID to UsedIDs table
strSQL = "INSERT INTO tUsedIDs ( IDNumber) VALUES ( '" & GuardianID & "' );"
ActionQuery strSQL
End If
'Assign new ID number
'-n- Changed cycle through the IDs: now just looks for the max ID and adds one.
strSQL = "SELECT Max(Mid([IDNumber],5)) AS IDNum FROM tUsedIDs WHERE ((Left([IDNumber],1) = '" & Funding & "') AND (Mid([IDNumber],2,3)='" & Region & "'));"
Set rst = dbs.OpenRecordset(strSQL)
i = Nz(rst!IDNum, 0)
i = i + 1
studentID = Funding & Region & Right((1000000 + i), 6)
'Create tRecipient Students record
strSQL = "
INSERT INTO [tRecipient Students] ( [Student ID], StudentFirst, StudentLast, Funding, " _
& "Start, Sibling, Guardians_ID, UpdateStudent ) VALUES ( '" & studentID & "', " _
& AddQuotes(StudentFirst) & ", " & AddQuotes(StudentLast) & ", " & Funding & ", " & CLng(StartYear) _
& ", " & Sibling & ", '" & GuardianID & "', #" & Date & "# );"
ActionQuery strSQL
'Get ParentQual if this is an addition to existing family
If GuardianExists = 1 Then
Dim Qualified As String, ParentQual As Long
Set rst = dbs.OpenRecordset("SELECT ParentQual FROM tStudentYear INNER JOIN [tRecipient Students] ON " _
& "tStudentYear.studentID = [tRecipient Students].[Student ID] WHERE Guardians_ID = '" _
& GuardianID & "' AND Year = " & CurrentYear & ";")
If rst.RecordCount > 0 Then
ParentQual = rst!ParentQual
If InStr(1, Left(ParentQual, 3), "7") Then Qualified = "No"
End If
Set rst = Nothing
End If
'Create tstudentyear record
If Len(Qualified) = 0 Then Qualified = "Pending"
If ParentQual = 0 Then ParentQual = 2220
strSQL = "
INSERT INTO tstudentyear ( Year, studentID, Qualified, ParentQual ) " _
& "VALUES ( " & CurrentYear & ", '" & studentID & "', '" & Qualified _
& "', " & ParentQual & " );"
ActionQuery strSQL
'Write ID to UsedIDs table
strSQL = "INSERT INTO tUsedIDs ( IDNumber) VALUES ( '" & studentID & "' );"
ActionQuery strSQL
'Modified JEE 6/1/05.
If SysCmd(acSysCmdGetObjectState, acForm, "Qualification Form") <> 0 Then
Forms![Qualification Form].Requery
Else
DoCmd.OpenForm "Qualification Form"
End If
Forms("Qualification Form").[Student ID].SetFocus
DoCmd.FindRecord studentID, acEntire, , , , acCurrent
Forms("Qualification Form").[AppDate] = Date
Forms("Qualification Form").[Grade].SetFocus
Forms("Qualification Form").SetFocus
DoCmd.Close acForm, "New Student Input"
Finish_NewStudent:
DoCmd.Hourglass False
DoCmd.Echo True
End Sub
Thank you