Run query from form?

[JP Romano]

This is probably a really stupid question, but I'm having a mental block and could use a hand.

I need to run a query, called 'getnew' from a button click in a form called 'workitems'

The end usage should be something like this:
- Trouble ticket summary info is loaded into a table. Every ticket starts out with a status of "Available"
- When the user opens the form, the next sequential ticket is loaded and the status changed to "In Process"
Here's where I have the trouble.
- If the user is unable to resolve the ticket, it must go back to the queue (in other words, the status is changed BACK TO Available, and another ticket is displayed).
The problem is, once the ticket's status is changed back to Available, it automatically becomes the next "available" ticket, and goes right back to the same user.

I need to be able to change the status back to "Available" and grab the NEXT ticket with status "Available"

Can anybody offer me some quick advice on this?

THANK YOU!

 

[Kafrin]

Can you identify the next available ticket before you change the status back? Ie store it in a variable, then change the status, then go to the ticket.

Similarly, can you store the primary key of the ticket you are leaving unresolved? Use this to limit your recordset. You say your tickets are ordered sequentially, I'll assume you mean using a number/autonumber field for now. So you load a ticket into the table, presumably from another recordset. When you loop round to get the next ticket, reopen this recordset with a criteria that TicketNumber > Number of this ticket.
If your tickets are not ordered on a numeric field, then store the ticket ref, do a FindFirst on the recordset looking for the ticket ref, and then MoveNext to find the next one.

If none of these suggestions help then really we could do with some more info about where your data is coming from and what code you have in place already.

 

[JP Romano]

Thanks for the quick reply... I took your advice (storing the autonumbered ID as a variable), and it seems like so far, it's working well.
Thanks again...