Assign Next User Automatically based on Another Table

[SueHopson]

I have a table called Intake Staff with the following fields

ID
LastName
FirstName
Archive

For simplicity, lets say I have 3 records as shown below:

ID 1  LastName1  FirstName1  Archive = True
ID 2  LastName2  FirstName2  Archive = False
ID 3  LastName3  FirstName3  Archive = False

In my primary table called Tasks I also have a field called "Assigned To" which pulls from the Intake Staff table and displays only those records where Archive = False.

SELECT Contacts_Active.ID
     , Contacts_Active.[Display As]
     , Contacts_Active.Archive
FROM   Contacts_Active
WHERE  ((Contacts_Active.Archive)=False);

My question is, how can I set this field to automatically select a user to be assigned when a new record is opened, and then to start over at the top of the list when the last valid record is reached?

[NeoPa]

Let me start by suggesting you don't confuse an ID field with a sequencing one. It will often work that way if required but it isn't designed to so to rely on that is unwise.

Having said that you will need a sequencing field as well as a flag to indicate which record is next to be used. There may be some SQL that will do both jobs for you smoothly (IE. Clearing one record as well as setting the next.) but as Jet SQL has so many limitations that make a query non-updatable it's so much more straightforward to use recordset code in VBA to make the updates.

The Form Control would be set with a DefaultValue in the relevant Field (Your explanation talks of two tables but then gives an example of SQL referring to a table with a completely different name.) to match the value from the record with the flag set. This would be updated only after the record has actually been created though. You don't want to create gaps when a record is started but never saved.

The code would be something like :

Dim lngSequence As Long
Dim strSQL As String
Dim dbVar As DAO.Database
 
lngSequence = DLookup(Expr:="[SequenceField]" _
                    , Domain:="[YourTable]" _
                    , Criteria:="([NextFlag])")
Set dbVar = CurrentDb()
strSQL = Replace("SELECT   TOP 2%L" _
               & "       , [SequenceField]%L" _
               & "       , [NextFlag]%L" _
               & "FROM     [YourTable]%L" _
               & "WHERE    (Not [Archive])%L" _
               & "  AND    ([SequenceField]>=%V)%L" _
               & "ORDER BY [SequenceField]" _
               , "%L", vbNewLine)
strSQL = Replace(strSQL, "%V", lngSequence)
With dbVar.OpenRecordset(Name:=strSQL, Type:=dbOpenDynaset)
    Call .Edit
    !NextFlag = False
    Call .Update
    Call .MoveNext
    Call .Edit
    !NextFlag = True
    Call .Update
    Call .Close
End With

[SueHopson]

Thanks, I'll review more carefully tomorrow and try to apply the suggestions. Apologies for the late reply and truly appreciate the suggestion.

[NeoPa]

Happy to help :-)

No need to apologise for lateness on a forum. It doesn't have to be very interactive. You just post with what's there at the time and move on to the next one.