How to avoid a "You must enter a value" error message in Access VBA

Question

I'm having issues avoiding a "You must enter a value in the __ field" error message in Access 2016. I have three tables, Tasks, Users, and TaskAssignments, and a split form that looks like:

User    Task    Assigned?

User1   Task1   True
User1   Task2   False
User1   Task3   True
User2   Task1   False
User2   Task2   False
User2   Task3   True
User3   Task1   True
User3   Task2   True
User3   Task3   True

Each task can have multiple users assigned to it, and each user is assigned to multiple tasks. I want my form to display every possible value, then use a checkbox, so that I can click and add a user to that task. The TaskAssignments table has a primary key and a unique constraint on both TaskID and UserID.

The recordsource for my form is a query:

select x.UserName, x.TaskName, ta.is_assigned
from (select * from Tasks, Users) x
left join TaskAssignments ta on (ta.TaskID = x.TaskID and ta.UserID = x.UserID)

I have an on click event that checks if a record exists in TaskAssignments and either updates or inserts into TaskAssignments. When I debug.print and manually run my queries, they both do what's expected. When I manually insert a record into my TaskAssignments table, my form behaves how I expect. When I need to insert a new record, however, I receive a message stating that I must enter a TaskID in TaskAssignments.

I've tried requerying the form, but I still receive the error message. Why can't it find the record that I just inserted?

Help please?!? Do I need to drastically rethink my approach here?

Here's the VBA:

Private Sub is_assigned_Click()

Dim CurrentUser, AssignmentQuery As String, SelectedUserID, SelectedTaskID As Integer
Dim ShouldInsert, IsAssigned As Boolean

CurrentUser = Environ$("Username")
SelectedUserID = Me.UserID
SelectedTaskID = Me.TaskID
IsAssigned = Me.is_assigned

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb
strSQL = "select UserID, taskID from TaskAssignments where UserID=" & SelectedUserID & " and taskID =" & SelectedTaskID & ";"

Set rs = db.OpenRecordset(strSQL)

If rs.EOF = True Then
    ShouldInsert = True
    Else: ShouldInsert = False
End If

If ShouldInsert = True Then
    AssignmentQuery = "insert into TaskAssignments (UserID, taskID, DateAssignmentUpdated, AssignmentUpdatedBy, is_assigned) values " _
    & vbCrLf & "(" & SelectedUserID & "," & SelectedTaskID & ",#" & Now & "#,'" & CurrentUser & "'," & IsAssigned & ");"

ElseIf ShouldInsert = False Then
    AssignmentQuery = "update TaskAssignments set UserID=" & SelectedUserID & ", DateAssignmentUpdated=#" & Now & "#, AssignmentUpdatedBy='" & CurrentUser & "',is_assigned=" & IsAssigned _
    & vbCrLf & " where taskID = " & SelectedTaskID & " And UserID = " & SelectedUserID & ";"
End If

MsgBox AssignmentQuery
db.Execute (AssignmentQuery)

Forms("Task Assignments").Requery

Set rs = Nothing
Set db = Nothing

End Sub

Edit - here are the queries produced:

Insert

insert into TaskAssignments 
(UserID, TaskID, DateAssignmentUpdated, AssignmentUpdatedBy, is_assigned) 
values (301,4,Now(),'mylogin',True);

Update

update TaskAssignments 
set UserID=270, DateAssignmentUpdated=Now(), AssignmentUpdatedBy='mylogin', is_assigned=False
where TaskID = 1 And UserID = 270;

And a constraint on my TaskAssignments table. Both TaskID and UserID are set as required in my table design (which was my whole goal - I was hoping to avoid adding records to TaskAssignments until the user has actually been assigned to a task).

alter table TaskAssignments add constraint TaskAssignmentsConstraint unique (TaskID, UserID);

Answer 1

Beware of wrong datatypes, each Dim needs its own datatype!

Dim CurrentUser As String, AssignmentQuery As String
Dim SelectedUserID As Long, SelectedTaskID As Long  ' don't use 16-bit Integer for ID columns
Dim ShouldInsert As Boolean, IsAssigned As Boolean

---

To avoid troubles with date/time formatting: the database engine knows Now(), so you can directly use this in the Insert SQL:

AssignmentQuery = "insert into TaskAssignments (UserID, taskID, DateAssignmentUpdated, AssignmentUpdatedBy, is_assigned) values " _
& vbCrLf & "(" & SelectedUserID & "," & SelectedTaskID & ", Now(), '" & CurrentUser & "'," & IsAssigned & ");"

If it still doesn't work, use Debug.Print AssignmentQuery instead of MsgBox and add the actual SQL to your question (Ctrl+G shows the output).

---

Edit

Re-reading the question and comment, I think the problem is:

You are editing a bound form, and are updating/inserting in the same table the form is based on. That's where the Write conflict on Update comes from, the other error is probably because the bound form is trying to insert a record when you click is_assigned, but can't.

So yes, you need to rethink your approach, at least partially.

One solution is to insert the recordsource into a temp table, and base your form on that. Then the rest of the code will probably work.

It may be over-complicating things, though.