Renumbering 1

[SMHSleepy]

I have a tabular form based on a select query which simply displays 7 names along with their corresponding SortOrder numbered 1-7. Something like this:

SortOrder Name
1 John
2 Fred
3 Jane
4 Joe
5 Henry
6 James
7 Lisa

I'd like to be able to either double click a name or press a control button next to the name to change its SortOrder value to 1 and increment the others accordingly. For example, if I want Jane to be first (i.e. SortOrder = 1), the table would now look like:

SortOrder Name
6 John
7 Fred
1 Jane
2 Joe
3 Henry
4 James
5 Lisa

Notice how the person before and after are always the same, it's just a way of denoting who starts first.

Any ideas? Thank you.

 

[TheAceMan1]

How are ya SMHSleepy . . .

In the forms [blue]On Dbl Click[/blue] event, copy/paste the following:

[blue]   Dim rst As DAO.Recordset, RecCnt As Integer
   
   Set rst = Me.RecordsetClone
   rst.Bookmark = Me.Bookmark
   
   Do
      RecCnt = RecCnt + 1
      
      rst.Edit
      rst!SortOrder = RecCnt
      rst.Update
      
      rst.MoveNext
      If rst.EOF Then rst.AbsolutePosition = 0
   Loop Until RecCnt >= rst.RecordCount[/blue]

Just double-click the [blue]record selector[/blue] of the record of interest. Thats it!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SMHSleepy]

Thank you Aceman! Works like a charm.

I just wish I could figure out WHY it works.......

 

[TheAceMan1]

SMHSleepy . . .

Well ... how are your VBA skills?

Have you worked with recordsets?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SMHSleepy]

Unfortunately, my skills with recordsets are little to none. I should make it a point to learn because I'm finding they are very powerful tools.

That said, can you help me with my next (and last) step in this project?

I now have a select query with results which look like this (I've simplified it a bit but for all intents and purposes, works the same):

ID Date Code Hours Name
1 2011-07-07 D 7.5 John
1 2011-07-08 D 7.5 John
1 2011-07-09 N 11.25 John
2 2011-07-07 D 7.5 Jane
2 2011-07-08 N 11.25 Jane

I can make a form where I enter a date range and select an employee, then have the query select those records meeting the criteria. However, I want to actually use a looped recordset to look at each record then copy to a temporary table. The temporary table would have the same column headings but the code and hours may differ. If you could help me set up the looped recordset to do this, I can put in the conditions. For example, it would look at the first line and see that the code is 'D' so nothing changes and it copies all fields the same. However, when it sees the 'N' code, it splits it into two rows: e.g.

ID Date Code Hours Name
1 2011-07-09 N 11.25 John

gets split into:

ID Date Code Hours Name
1 2011-07-09 E 4.00 John
1 2011-07-10 O 7.25 John

Then I generate a report based on the temporary table and finally clear the temporary table. Does this make sense?

 

[TheAceMan1]

SMHSleepy . . .

It would be to your benefit to [blue]start a new thread.[/blue] Doing so would expose your problem to the entire forum! ... instead of being burried here. You can simply copy/paste your latest post (11 Jul 11 15:37) to the new thread.

[blue]Do it![/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]