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
Code:
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
Code:
UPDATE qry1stAvailCE, tblMonCE SET qry1stAvailCE.InfID = [tblMonCE].[caseID];
The VBA AfterUpdate code that calls QueryAvail
Code:
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.