Incremental line number field

[ben.passmore]

Hello all.


I hope some of you can help me.

I have a table that looks like this

Code line number
100
100
100
222
222
222
222
222
333
444
444
555

I am trying to build a query that will add an incrementing line number for each code and start the count again from 1 when it meets a new code. The resulting table would look like this.

Code line number
100 1
100 2
100 3
222 1
222 2
222 3
222 4
222 5
333 1
444 1
444 2

The number of matching values in Code is variable.

I hope you can help me.

Regards,
Ben.

 

[namliam]

Do you have some sort of PK/Autonumber thing going on in the table as well?

Regards

 

[ben.passmore]

No.

This is the whole table. Although I could add an autonumber field if required.

Ben.

 

[Rich]

Why not just use a Report?

 

[ben.passmore]

A report is no good.

I'm producing data files for upload to an old database system. The purpose of the numbers is to show line numbers on orders for a perticular customer code.

So no a report is no good to me.

Ben.

 

[namliam]

If all you want to do is add it to the table (staticaly) try this on for size:

Sub CountingFromTheMailman()
    Dim MyCounter As Integer
    Dim myCode As Integer
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select * From YourTable Order by Code")
    myCode = 0
    Do While Not rs.EOF
        If myCode <> rs!code Then MyCounter = 1: myCode = rs!code
        rs.Edit
        rs!LineNumber = MyCounter
        rs.Update
        MyCounter = MyCounter + 1
        rs.MoveNext
    Loop
    Set rs = Nothing
End Sub

Regards & GL

The Mailman

 

[ben.passmore]

Thanks for this but.

I get a error message on clicking the run button.

Compile error:

User defined type not defined.

Any ideas?

Ben.

 

[st3ve]

DAO.Recordset?

I saw this post from Mailman, and thought i'd give it a go, as it seemed useful.

Ben, If you remove or comment out this line

Dim rs As DAO.Recordset

It will work. Though i don't know why/what the DAO bit is about.

One thing i noticed, for some reason the line numbers are allocated out of order (Anyone have any idea why?), but at least they are unique and contiguous.

HTH

 

[namliam]

When using a2k or above you also have an option of working with ado instead of dao. Mosttimes people work with DAO since that is used in earlier versions. If it works without the dim line then your not declaring it. Bad practice... also you dont have option explicit on... demanding the declaration of all variables...

The random commes from the select statement. It does not have any order command in it, thus collecting the records at random...

Regards

 

[st3ve]

All luck - no design!

Mosttimes people work with DAO....

..Namlian

Thanks for your reply, most interesting. I've never (knowingly!) used DAO (or ADO). What are they all about and why would I need to use them? (The reason I removed this line was pure 'luck', as I had been getting a ' Compile error: User defined type not defined' report, until then, which pointed at that particular line of code.

As you may have guessed by now, I tend to fumble about till it works when it comes to code!

Also, if I may, ........

If I want to use your code in, say an update query, do i just change the Sub 'header and footer' to Function in the module and then add the function name to my query in some way?

 

[namliam]

Re: All luck - no design!

If I want to use your code in, say an update query, do i just change the Sub 'header and footer' to Function in the module and then add the function name to my query in some way?

NO ...

This function handles a table directly which is just what DAO and ADO are all about. Handling tables in VB(A), not sure about the differences and the how when why what of it. But point is they are basicaly different. For instance Recordset, Database and others are used in both. You can check your references to see if your using DAO or ADO

So DONT use this sub/function in an update query or something or rather. Changing it to a function tho will enable you to run in more easily from a switchboard or macro... But that is not really what a function is used for. A function is/should be used for returning some value, most likely based upon some intreget logic (if then else then if bla bla ....) or some hard calculation. Even simpler (one line) calculations that might be used all over the place (centralising the calculation, making it easier to update your app should the calc be changed).

Anyway... that a short story maid long...

Regards

THE MAILMAN

 

[st3ve]

Half full, half empty

Anyway... that a short story maid long...

Or ... A (probably, very) long story kept (sensibly) short...

Either way, thanks for your help.