Automate LineNumbers for Sub-Form

[karl009]

Hello,

I have a database that has a sub-form, for the sub-form I would like to add line numbers for the different lines of data, this will help keep things tidy and in the correct order when referring to paper work.

My question is how would I automate something like this, I can add in the field LineNumbers and enter them with the data manually, but could it be done so the database dose it task?


Many Thanks
Karl

 

[SteveSchapel]

Karl,

In my experience, this is a very unusual requirement. If there is a "correct order" for your records, then this generally only makes sense if the "correct order" relates somehow to real-world existing data. So you therefore should be able to refer to the real data, without the need to add in some arbitrary field that does nothing except sort the records on the basis of nothing.

Hope you understand what I mean.

 

[karl009]

Hi,

I do understand I think...

Some background to why this has been asked for, we get an order off a customer we enter it into our database after we have entered a few lines we find that it uses the part number to sort its self into alphabetical order, the people working on this database would like to maintain the order that it is entered into the system so they match the paper work as this is not kept on paper but in the database, but if they where to request the paper record from a customer it would save time for cross checking.

Also we have only just turned away from doing this in Excel which kept the order of the entered data.

So if you know of any ideas on where to being for some sort of solution for this it would be great.

Many Thanks
Karl

 

[PNGBill]

Whenever you add data to a database by default, you have created some Reference number, be an Order Number or other ID of of some type.
A lot of times this is an AutoNumber.
If your record has this, which it should (somehow) then this will give you a sort field that should be chronological.

eg, a common order entry system consists of OrderHeader Table and orderdetail table. when items are added to the OrderDetail table then the OrderDetailID should supply the sort order and work just like any other such list be it excel or the fridge door shopping list.

SKU number / Part Number should not be the record ID for the order detail table as it is hardly a unique number.

You don't need to display the number in order to have it used for sorting purposes. The control can be made invisible.

 

[karl009]

Hi,

After having a talk with the people using the database they have agreed not use line numbers but will look into why the data doesn’t maintain its order of entry.

How ever I have been working on something that I found and would like some help in understanding why it isn’t working.

MyCounter = Nz(DMax("MyCounter", "OrderDetailT","OrderID=" & Forms!OrderF!OrderID)) + 1

I found this on the following site;
http://599cd.com/tips/access/incrementing-your-own-counter/?key=AllExperts

However when I implement it I get an "Syntax error (missing operator) in query expression 'OrderID=33333AB'"

So it seems to be picking up the right orderID from the form, what is it missing.

Many Thanks
Karl

 

[karl009]

Hi,

I have managed to implement auto line numbers using the above website, here is the change that was needed.

MyCounter = Nz(DMax("MyCounter", "OrderDetailT","OrderID= Forms!OrderF!OrderID")) + 1

Thanks
Karl

 

[namliam]

Hi,

I have managed to implement auto line numbers using the above website, here is the change that was needed.

MyCounter = Nz(DMax("MyCounter", "OrderDetailT","OrderID= Forms!OrderF!OrderID")) + 1

Sorry to butt in here Karl, but this is not right.... what your looking for is

MyCounter = Nz(DMax("MyCounter", "OrderDetailT","OrderID= '" & Forms!OrderF!OrderID & "'")) + 1

That will work for a text like you have.
For numbers just remove the '
For dates replace the ' by # AND make sure your date is in US (MM/DD/YYYY) or ISO (YYYY-MM-DD) formats.

 

[karl009]

Hi,

No problem, it’s always good to be shown something new...

But what I have placed in Access is working for me, the OrderID is stored as text due to its format of 111111/11 KAR maybe this is why it is working with the above code.

I have tried out the way you have put and that also works, strange one as to why the way I found works.

Thanks for the help, I will be keeping a note on how to do the others as well.

Many Thanks
Karl

 

[SteveSchapel]

Sorry to butt in here Namliam, but I think you will find that this will work fine:
MyCounter = Nz(DMax("MyCounter", "OrderDetailT","OrderID= Forms!OrderF!OrderID")) + 1

 

[SteveSchapel]

Karl,

...but will look into why the data doesn’t maintain its order of entry.

I think the thing to realise here is that data entered into Access tables has no intrinsic order. It is the "barrel of apples" concept. When you are viewing or outputting the data, to your forms ar reports, the only way to have that data in a particular order is to explicitly force it to be ordered. There are a number of ways to force the ordering, but typically this is done in forms via the sorting in the query that the form is based on, and in reports bia ther Group and Sort facility in the report design.

The point I tried to make before, and that Bill also alluded to so well, is that almost always there is some actual data in the table that can be used as the basis of that explicit sort.

But the "order of entry" by itself means nothing.

 

[namliam]

Sorry to butt in here Namliam, but I think you will find that this will work fine:
MyCounter = Nz(DMax("MyCounter", "OrderDetailT","OrderID= Forms!OrderF!OrderID")) + 1

Funny stuff, it will break in most cases but here it actually works...

 

[jcurrier421]

I was able to create auto line numbering on a subform for a Return Goods DB. I found this code from Stephen Lebans: lebans.com/rownumber.htm
From his work, create a Module and add this code:

Public Function RowNum(frm As Form) As Variant
On Error GoTo Err_RowNum
    'Purpose:   Numbering the rows on a form.
    'Usage:     Text box with ControlSource of:  =RowNum([Form])
 
    With frm.RecordsetClone
        .Bookmark = frm.Bookmark
        RowNum = .AbsolutePosition + 1
    End With
 
Exit_RowNum:
    Exit Function
 
Err_RowNum:
    If Err.Number <> 3021& Then  'Ignore "No bookmark" at new row.
        Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
    End If
    RowNum = Null
    Resume Exit_RowNum
End Function

Stephens code works as a control source, however it will not save the value to an underlying table.
So I implemented it into my subform like this:

Private Sub LineNumber_GotFocus()
Me.LineNumber = RowNum([Form])
End Sub

I am using a "Bound" text box (Me.LineNumber) so I can then store the Line Number in the underlying table...
Hope this can help offer guidance to someone else!

 

[carlo]

Although this is an old post, I'm posting my reply anyway as it might help others searching for a simpler solution.

The following works for me. I just put one line of code in the Before Insert event in the sub-form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.txtSeqNo = Me.CurrentRecord
End Sub

The line number (txtSeqNo) is populated with the current record number in the sub-form so the first one gets value 1, the second 2, etc.

 

[SteveSchapel]

The following works for me. I just put one line of code in the Before Insert event in the sub-form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.txtSeqNo = Me.CurrentRecord
End Sub

Hi Carlo

That's cool. However, I am pretty sure that this will only be a useful approach if the Record Source of the subform is the table itself - which in my experience is often not the case.