467,877 Members | 1,077 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,877 developers. It's quick & easy.

Assign Next User Automatically based on Another Table

I have a table called Intake Staff with the following fields


For simplicity, lets say I have 3 records as shown below:
Expand|Select|Wrap|Line Numbers
  1. ID 1  LastName1  FirstName1  Archive = True
  2. ID 2  LastName2  FirstName2  Archive = False
  3. 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Contacts_Active.ID
  2.      , Contacts_Active.[Display As]
  3.      , Contacts_Active.Archive
  4. FROM   Contacts_Active
  5. 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?
Jan 28 '20 #1
  • viewed: 1624
3 Replies
Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim lngSequence As Long
  2. Dim strSQL As String
  3. Dim dbVar As DAO.Database
  5. lngSequence = DLookup(Expr:="[SequenceField]" _
  6.                     , Domain:="[YourTable]" _
  7.                     , Criteria:="([NextFlag])")
  8. Set dbVar = CurrentDb()
  9. strSQL = Replace("SELECT   TOP 2%L" _
  10.                & "       , [SequenceField]%L" _
  11.                & "       , [NextFlag]%L" _
  12.                & "FROM     [YourTable]%L" _
  13.                & "WHERE    (Not [Archive])%L" _
  14.                & "  AND    ([SequenceField]>=%V)%L" _
  15.                & "ORDER BY [SequenceField]" _
  16.                , "%L", vbNewLine)
  17. strSQL = Replace(strSQL, "%V", lngSequence)
  18. With dbVar.OpenRecordset(Name:=strSQL, Type:=dbOpenDynaset)
  19.     Call .Edit
  20.     !NextFlag = False
  21.     Call .Update
  22.     Call .MoveNext
  23.     Call .Edit
  24.     !NextFlag = True
  25.     Call .Update
  26.     Call .Close
  27. End With
Jan 28 '20 #2
Thanks, I'll review more carefully tomorrow and try to apply the suggestions. Apologies for the late reply and truly appreciate the suggestion.
Feb 4 '20 #3
Expert Mod 16PB
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.
Feb 5 '20 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by colleen1980 | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.