By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,275 Members | 1,937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,275 IT Pros & Developers. It's quick & easy.

Assign Next User Automatically based on Another Table

P: 5
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:
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?
3 Weeks Ago #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,660
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
  4.  
  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
3 Weeks Ago #2

P: 5
Thanks, I'll review more carefully tomorrow and try to apply the suggestions. Apologies for the late reply and truly appreciate the suggestion.
2 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,660
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.
2 Weeks Ago #4

Post your reply

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