Alphabetical order

[constantG]

Thanks Wayne,

I will try it when I'm at work again on Monday.

 

[constantG]

Hi, I tried it out on a copy I had, works perfectly. I did have one problem and that was that it applied the reorder to the whole table, what I wanted it to do was just reorder today's records. So I just created a query which showed all today's records and had the counter code point to that resulting table. Spot on.

Thanks for all your help guys.

 

[constantG]

Ahh, one problem.

The number starts at 1 for each day which is not the desired effect. What I suppose I need to do is have the FamNumber field get it's number from another source on a different table but I don't know how to do this.

For example:

Day one has FamNumber 1 - 20
20 needs to be stored in another table
then reorder 1 - 20 into alphbetical order

Then on day 2 FamNumber gets 21 as the next number from the other table
21 to 40 are then reordered and 40 is stored on the other table

etc... etc.

Any Ideas?

 

[WayneRyan]

G,

Change:

Counter = 1

To:

Counter = DLookUp("[FamNumber]", "YourTable")

Wayne

 

[constantG]

I'm sorry if this sounds noobish.. but how do I set it up so that the last number is stored in another table.

 

[constantG]

Hi,

Finally sorted it. I had to use:

Counter = Dmax("FamNumber","YourTable") - 1

And it works fantastically. Thanks again for all your help.

 

[WayneRyan]

G,

Glad you got it working.

Wayne

 

[constantG]

I wish I hadn't started this. It only works if the button is only pressed once in a 24 hour period. If it is pressed again on the same day it reorders them based on the last number in the second table.

i.e.

Day 1 input is reordered like this

0001 anderson
0002 bentley
0003 collins

Day 2 is reordered like this

0004 anderson
0005 bentley
0006 collins

But if I add more records on day 2

0007 beardsley
0008 andrews

when you reorder these the outcome is

0009 Anderson
0010 andrews
0011 beardsley
0012 bentley
0013 collins

therefore entries 0007 and 0008 have been overwritten and no longer exist.

I was so close too

 

[WayneRyan]

G,

Upon further review ...

I think that we have to renumber them just prior to final submittal.

I need to see your table structures, but we have to go back to an earlier
post and number them based on the final recordset:

Select *
From   YourTable
Order By TheDate, SurName
[\code]

Then we'll just assign the number starting from 1.

Wayne

 

[constantG]

Here's a copy of my database. I've stripped it down to only include the details you need. Hope you can help.

 

[WayneRyan]

G,

I only have A2000 at home. Can you post something I can read?

Wayne

 

[constantG]

Here you go Wayne,

Have a look at the "flag" field and what I have attempted to do with it, (you'll understand when you see it).

Thanks,

 

[WayneRyan]

ConstantG,

Hope you and yours enjoyed the holidays.

Back to work though.

We should go back to post #5 in this thread.

Just prior to submittal, you should just run this:

Dim Counter As Long
Dim rst As DAO.Recordset

Counter = 1
Set rst = CurrentDb.OpenRecordset("Select * From FamT Order By FamDate, FamSurname")

While Not rst.EOF And Not rst.BOF
   '
   ' [B][SIZE="2"]Change the field "Flag" to "TheNumber" <-- an Integer[/SIZE][/B]
   '   
   rst.Edit
   rst!TheNumber = Counter
   rst.Update
   '
   Counter = Counter + 1
   rst.MoveNext

hth,
Wayne

 

[constantG]

Wayne,
You are an absolute genius. Thanks very much it works a treat. Merry Christmas