Rank Me...

[jfgambit]

I've been reading throught the old posts, but can't seem to grasp this:

I have a table of Bids containing the following MemberID, VehicleID, AuctionDate, ChoiceNumber. What I need to do is to rank the bids from 1 to ....(however many there are) based on the lowest BidAmount within the form by updating the ChoiceNumber field with the appropriate Rank number.

1) What would be the best way to re-rank the ChoiceNumber field utilizing the AfterUpdate event of the BidAmount Field? Criteria: All BidAmounts from tblBids where Vehicle ID = VehicleID on frmBid and AuctionDate = AuctionDate on FrmBid

2) What would be the best way to re-rank the ChoiceNumber field if a number is manually entered in the ChoiceNumber field?
Example: A user changes ChioceNumber 3 to 1...I need all the remaning BidAmounts to rank in order by Lowest Bid, but keep the manually changed one to 1.



Thanks in advance for any help you can provide.

 

[Fizzio]

1. How I would do this is to create a recordset based on the records in the form then sort this, loop through the recordset and re-allocate the choice numbers dynamically. Update the recordset and requery the form.

Someone may have a better idea though - maybe using an update query and a custom function to allocate the choice number.

2. Not sure on this one - A way I'm thinking of is similar to the first but adding a field called LockChoice as a yes/no so if you manually select a choice and lock it, the code will keep that as the manual choice and re-arrange all the others. This is fairly simple if the manual choice will always be '1' but a little more tricky if it is something else.

any help?