OrderBy code in OnCurrent event

[TOTCOM11]

I have a database that helps me keep tabs of a live fantasy football draft. I have a continuous subform on my main form that keeps track of what round I am drafting in and the order of the teams that are drafting. All odd rounds (1, 3, 5, etc...) are sorted in ascending order and all even rounds are sorted in descending order. I am using the following code to achieve this:

Private Sub Form_Current()

Me.OrderByOn = True

If Me.MOD = 1 Then
Me.OrderBy = "DraftOrder"
Else
Me.OrderBy = "DraftOrder DESC"
End If

End Sub

MOD is a control that = 1 if the round number is odd and it is = 0 when the round number is even. The code runs perfectly, except for one minor flaw...I can only select the top record of the continuous form (or in this case, the team that drafts first in that round).

I notice that when Me.OrderByOn = True, that I can only select the top record on my continuous form. However, when Me.OrderByOn = False, I can select any record I want, but the records are not in the desired order. Is there any way around this? Note: When I select any other record, the cursor (focus) is set on the first record instead of the record that I clicked on. Can anyone help me?

TOTCOM11

 

[TOTCOM11]

Note: I have a unbound combo box that allows me to select the round that I want to view. This box is not on the subform, but on the main form. When I select an odd round, I want all the teams to be sorted ascending by DraftOrder and descending if I select an even round. Only those records for the round that is selected is shown on the subform.

 

[Rolliee]

I suspect your problem is when the event is firing. When do you expect this event to fire? Put a MsgBox in it and see when it fires. I am not familiar with the event you are programming.


Rollie E

 

[TOTCOM11]

My bad.... I forgot to mention that this is happening in the OnCurrent event of the subform. It's not an issue about when it fires, what the OrderBy function itself does. When OrderBy is set to False, everything works fine except nothing is ordered the way I want it to be (well, even rounds are not sorted descending. When OrderBy is set to True (by me) I can only select the first record.

 

[Rolliee]

When you do the on_current, you are moving the record back to the first one - just as in a re-query. If you want to return to the current record after the 're-set' you should include this code.

DIM BM as variant

BM = me.boookmark

do the orderby code

me.bookmark = BM


Rollie E

P.S. In the old days we used to say that you need the DWIM card. That is for Do What I Mean.

 

[TOTCOM11]

When I run the code, I get an error on the following line:

BM = me.boookmark

"bookmark" is the thing that is highlighted. I currently am running Access 2002, does this have anything to do with it?

TOTCOM11

 

[TOTCOM11]

Nevermind...you just spelled Bookmark with 3 "o"s instead of two....I'm running the code now...I'll let you know if it works or not.

 

[TOTCOM11]

OK, now I'm getting an error on the following line:

Me.Bookmark = BM

The error I'm getting says, "Not a valid bookmark." Any ideas?

TOTCOM11

 

[Rolliee]

That says that you are not 'setting' on a record when you click the bookmark. It must be a new record that has not had iinfo sent back to the table.

Rollie E

 

[TOTCOM11]

I don't think so. The form does not even display new records...

 

[Rolliee]

It could be that your recordset is not populated. Show the code associated with the problem area. Or send me a zipped sample mdb.

rollie@bwsys.net

 

[LachlanD]

Have you considered just putting your original code in the On Activate event rather than on current. The then code only runs when you open the form and the cursor stays put