SQL question

I am writing an application using VB with an access database. After a use a SQL SELECT statement ( SELECT * FROM Customer WHERE Active = True ORDER BY SearchName"),
I want to be able to find a particular record without narrowing my record set to one record, because I want to have be able to access the next and previous records. How can I do this?

That depends on HOW you want to narrow the search to one item. It also depends on what kind of a cursor you have on the database (Forward Only, for example).

Post a little more info and we may be able to help.

-Pete

I am using a dynaset-type Recordset(keyset cursor). I was using data.Recordset.Seek etc.. type commands to find the record I wanted, but I was running into a lot of "operation not supported" errors.
Here is the scenario. The form is a customer maintenance form. Using option buttons the user can order the customers by number or name. The user can also elect to see only active, inactive or all customers. Thus my SELECT statement. From the customer form, the user can go to another form and view a list of customers, select one, return to the customer maintenance form where the customer they selected is then displayed. At that point they should be able to go to the next or previous record.
When the user comes back from the customer list, how do I get the record they selected, keep it current for update purposes and still be able to access the previous and next record?

Hmm... I'm a little rusty on my VB, but it seems to me like the key is going to be to save the user's SQL Query in a Global/Shared Variable along with some sort of an index value. (Possibly the primary key)

I'm not sure that there is a "good" way to do what you're asking if you use the Database as a data structure, mainly because in order to be accurate you'll have to be making lots of database queries.

I think I would probably save the query from the user, then (on the customer maintenance form) rerun the query and either seek for the primary key, or iterate through to it. Then you'll be able to use recordset.movenext(), etc... to accomplish the task.

This method could become VERY cumbersome if you have a lot of customers and/or a lot of people using the software. If you're dealing with good numbers, this should be handled pretty well.

I hope this helps a little.