Add record with dynamically pre-defined field

[TBOB]

Sorry about that subject line, but this is a little trickier than what I could fit on there. Here's what I'm trying to do: I have a record that has 7 contacts. They are in order like this:

Name Order Contacted

Contact 1 1
Contact 2 2
Contact 3 3
Contact 4 4
Contact 5 5 X
Contact 6 6
Contact 7 7

After trying 1 thru 4 with no response Contact 5 answers. This along with some other information would be on a record. Here comes the tricky part (at least for me). The next time I want to add a new contact record I would like Contact 6 to be first on the contact list like so:

Name Order Contacted

Contact 1 3
Contact 2 4
Contact 3 5
Contact 4 6
Contact 5 7
Contact 6 1
Contact 7 2

I know how to determine who was last contacted by going to the last record in the recordset and the math to change the order of the numbers, but what I need help with is how and where do I pre-define, dynamically, the value of a field in a record that is being added based on the value of a field in the previous record? If this is a little unclear, I’m sorry. Please let me know what other information you might need.

Thanks for the help,

Tony

 

[RobHudson]

Not totally sure I understand what you want, but could you not use an auto increment field?

 

[stix4t2]

Tony,
Can you use a DMAX("field", "table", "filter") where filter is "[id] < myID". The names are fictious, but the DMAX will return the max number less than the current id.

 

[TBOB]

Rob,

Basically I want whoever was actually contacted to be the last person on the list next time and and the person that was next on the list to be first one tried next time. It is basically a rotating contact list, but it's not always going to be the next person.

Stix,

I'll look into DMAX

Thanks,

Tony

 

[RobHudson]

Ok.

So could you not set all other contacts to their order -1 where the order number is greater than that of the person contacted. Then person contacted order to COUNT(Name)?

eg.

UPDATE tbl
SET Order = Order - 1
WHERE Name <> "Contact 7"
AND Order > (SELECT Order FROM tbl WHERE Name = "Contact 7")

UPDATE tbl
SET Order = (SELECT COUNT(NAME) FROM tbl)
WHERE Name = "Contact 7"

Thus moving "just contacted" to end of list and everybody else after him/her up one.

 

[TBOB]

Rob,

Let me try that one. I'll get back to you.

Thanks,

Tony