Assign numbers from 1 thru 9 to a set of records

[lala]

How do i do that? i have 9 fields that i need to be able to number everytime i run a function and they MUST not skip and they MUST start at 1

i've been looking at autonumber function all day and i haven't found one that does it, maybe i'm looking for the wrong thing? it's not autonumber but called something else?


EDIT: i have 9 ROWS, not fields

 

[lala]

i found an ugly solution but it works

Dim CountOfNulls As String
Dim UpdateTo As Long
UpdateTo = 1
CountOfNulls = DCount("MatchingField", "mtmatching", "matchid is null")

Do Until CountOfNulls = 0

    Dim UpdateID As String
    
    UpdateID = "UPDATE mtMatching SET mtMatching.MatchID = " & 
UpdateTo & " WHERE mtMatching.MatchingField=DMax
('matchingfield','mtmatching','matchid is null')  " & _

    "AND mtMatching.MatchID Is Null"
    
    DoCmd.RunSQL UpdateID
    
    CountOfNulls = DCount("MatchingField", "mtmatching", "matchid is null")
    UpdateTo = UpdateTo + 1

Loop

 

[DCrake]

If you have 9 records in a table that you want to assign the numbers 1 - 9 to then this is far more efficient

Public Function 1To9()

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordSet("MyTable")

For n = 1 To 9
    Rs.Edit
    Rs("YourField") = n
    Rs.Update
    Rs.MoveNext
Next

Rs.Close
Set Rs = Nothing

End Function

 

[lala]

using yours of course))))))))))) thank you!!

 

[lala]

also, it's not always 9 records, that's why i had some other code in there but yours looks neater anyway, so i'm editing yours instead

 

[DCrake]

If you can determine then number of loops you need prior to running the function you could pass the number as an parameter to the function


Public 1ToN(Nth As Integer)

...

For n = 1 To Nth

....

Next

 

[lala]

did exactly that))))))))) i learned a few things here. yours beats mine any time, that's why i said right away that my solution was ugly. thank you very much!!

 

[lala]

also, i always do that, don't know if it's the right thing to do or not. when i find a function online i don't keep it as a function, i take off the first and last line and put the code inside my code. should i have kept yours the way it was and called it instead?

 

[DCrake]

If you are going to use it in more that one place in your application it is best to keep it as is. However if this is the one and only place you ar going to use it then it does no harm to do what you are doing.