Update first null record in a Scheduling table with Current Record's ID number in "Main" table

[natilton]

Hello,

I have a "main" table (tblMonCE) with information about each person recruited for a clinical trial. I created another table (tblEvalProg) with the following structure, so that I could automatically fill up evaluation date-slots with ID numbers after each person enrolls.

SlotNum	FechaEval	InfID
1	2-Mar
2	2-Mar
3	2-Mar
4	3-Mar
5	3-Mar
6	3-Mar

It looks like my query to select the first null record is working

qry1stAvailCE

SELECT TOP 1 tblEvalProg.SlotNum, tblEvalProg.FechaEval, tblEvalProg.InfID
FROM tblEvalProg
WHERE (((tblEvalProg.InfID) Is Null));

However, when I call QueryAvail to update InfID in the selected record in an AfterUpdate procedure, it updates with caseID in the last record in tblMonCE instead of the current record.

QueryAvail

UPDATE qry1stAvailCE, tblMonCE SET qry1stAvailCE.InfID = [tblMonCE].[caseID];

The VBA AfterUpdate code that calls QueryAvail

Private Sub Enroll_Date_AfterUpdate()
 
Dim strSQL As String
strSQL = "QueryAvail"

    If Not IsNull(Enroll_Date) Then
        CurrentDb.Execute strSQL
    End If

End Sub

Am I missing something simple? I apologize for any ham-handedness here, and thanks in advance for any help you might be able to provide. I am seriously stuck.

[June7]

Never seen anything like this. Maybe apply SORT criteria in qry1stAvailCE to make sure the earliest record really is the TOP record when the UPDATE runs.

[natilton]

Never seen anything like this. Maybe apply SORT criteria in qry1stAvailCE to make sure the earliest record really is the TOP record when the UPDATE runs.

Hello, and thanks for your reply. I don't think qry1stAvailCE is the problem here, because when I run it by itself, it always selects the record with the lowest SlotNum to have a null InfID. The problem is that when I run the Update query from my form (after an update to the control for the field Enroll_Date), the selection from qry1stAvail is updated with the caseID from the last record in tblMonCE instead of the form's current record.

I'm fairly new to all this, but in my UPDATE statement, is there some identifier I'm supposed to use to indicate that I want to set the selection equal to the caseID from the form's current record?

Thanks

[June7]

Sorry, missed that in the original post.

Then refer to the form instead of the table.

UPDATE qry1stAvailCE, tblMonCE SET qry1stAvailCE.InfID = [Forms].[formname].[caseID];

[natilton]

Hello, and thanks for your reply. I don't think qry1stAvailCE is the problem here, because when I run it by itself, it always selects the record with the lowest SlotNum to have a null InfID. The problem is that when I run the Update query from my form (after an update to the control for the field Enroll_Date), the selection from qry1stAvail is updated with the caseID from the last record in tblMonCE instead of the form's current record.

I'm fairly new to all this, but in my UPDATE statement, is there some identifier I'm supposed to use to indicate that I want to set the selection equal to the caseID from the form's current record?

Thanks

That did the trick, thanks so much.