Originally Posted by
June7
Whoops, look at previous post, did an edit on the SQL statement.
Ok. Here's what I did, in case others are interested. Thank you June7 for the advice!
HTML Code:
Private Sub Form_BeforeUPdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
If Me!Survey_Type = 2 And IsNull(Online_Code) Then
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT TOP 1 [code] FROM tbl_Codes_2018 WHERE DistrictID_SchoolID = '" & Me.DistrictID_SchoolID & "' AND" _
& " [tbl_Codes_2018].[Assigned] = False ORDER BY tbl_Codes_2018.[code];", dbOpenDynaset)
If (rs.RecordCount = 0) Then
MsgBox "No School in Code table", vbOKOnly, "No Such School"
Exit Sub
Else
Me!Online_Code = rs!code
'Turn warnings off so user isn't prompted by row update.
DoCmd.SetWarnings False
'Update Assigned field to True after code assigned.
DoCmd.RunSQL ("UPDATE tbl_Codes_2018 SET tbl_Codes_2018.Assigned = TRUE WHERE Code = '" & Me.Online_Code & "'")
'Update Date Assigned field with date after code assigned.
DoCmd.RunSQL ("UPDATE tbl_Codes_2018 SET tbl_Codes_2018.[Date Assigned] = Date() WHERE Code = '" & Me.Online_Code & "'")
DoCmd.SetWarnings True
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End If
End Sub