writing an ODBC compatible cursor statement for access?

[braedon]

I am building a client referral tool. When a potential client makes a purchase inquiry, I want to send the inquiry request to a product dealer in the city specified by the client. I have a table with a list of dealers sorted by city.

One complication is that I need the referral tool to select only a single dealer from a list of potential dealers in that city.

My question is this: I can query the dealers with a WHERE statement to group my results on a specific city. This will give a number of results in my recordset. How do I select only a specific record from a recorset?

I was told that I could use a cursor to accomplish this task. My problem is that I am unsure how to write a cursor for access - and if it is possible, can I write one in a Cold Fusion Query? CF passes queries in an ODBC compatible format.

Any ideas? I have never written a cursor before and have only just read about it. If you have a suggestion, I would really appreciate any examples you could provide.

Thanks.

 

[nabiS2K]

How are you wanting to determine which dealer to use from this multi-record recordset? Randomly? First Record? Last Record? How?

 

[braedon]

I keep track of the number of dealers in any city, and I keep a placeholder that tracks which dealer is next in the list.

If there are 5 dealsers in a city, I will know which of those dealers will be next. I will pass this value (a number from 1 to 5) in a dynamic variable.

I want to be able to grab the record number in the recordset that has this variable value (Eg: the 3rd record in the recordset).

 

[nabiS2K]

You can use a FindFirst command to locate the record. The FindFirst works like the where of a SQL statement.

Here is an example:

Dim dbs as Database
Dim rst as RecordSet

Dim NewDealer as Long

Set dbs = CurrentDb
Set rst = dbs.TableDefs("MyTable").OpenRecordSet

With rst
     .FindFirst "[DealerNbr] = " & NextDealer 
     NewDealer = rst("DealerNbr").Value
End With

Hope this helps... Let me know if you need more clarification.

God Bless ;-)