Numbering lines in a subform

[JeffreyDavid]

Let me explain, our subform records our Projects and the projects coming in on the same order need to be kept in the order they were placed. To cure this I want to add a LineNo field to my subform and each time a new line is added to the subform, add 1 to the LineNo.
Does anyone ideas?

 

[Rich]

Why not just use an Autonumber as the PK?

 

[JeffreyDavid]

Why not just use an Autonumber as the PK?

An autonumber will number the Projects in the table, but I need to number the lines in the subform. So each time I open the form, the subforms first line number starts again at 1, second line 2, third line 3, and so on. And then the next time I open the form, the subform starts at 1 again.

 

[asnyder]

This is a round about way, but I managed to do it. I created an append query which appends records to an empty database whose sort criteria is in the order I want the data and that has an autonumber field. This restarts the numbering at 1 each time I create the table. Your subform should be based on this table. When you close the form run a delete query that deletes all the records in the table created by the query. This seems like a lot to go through just to number records, but it works.

 

[Rich]

And then the next time I open the form, the subform starts at 1 again.

What is the purpose of this exercise, what happens to the existing records?

 

[Jon K]

Using DCount()+1 in the Before Insert event of the subform should do the trick.

The code in the example attached is:-
Me.LineNo = DCount("*", "tblProject", "[OrderID]=" & Forms!OrderMain!OrderID) + 1


The [OrderID] field in table [tblProject] is indexed to help DCount() run faster.

 

[JeffreyDavid]

Using DCount()+1 in the Before Insert event of the subform should do the trick.

The code in the example attached is:-
Me.LineNo = DCount("*", "tblProject", "[OrderID]=" & Forms!OrderMain!OrderID) + 1


The [OrderID] field in table [tblProject] is indexed to help DCount() run faster.

Do you have the attachment available for Access 97? I'd like to see how it works in Access 97. In 97, the Before Insert event is not available, so I'm trying it in the Before Update event.

 

[Jon K]

Database created in Access 97 SR-2. (The form has the Before Insert event. Not sure if it is available only in SR-2.)

 

[asnyder]

Where would you put this code for a regular form - not a subform?

 

[EMP]

Where would you put this code for a regular form - not a subform?

Just curious. Why do you want to have Line# in a regular form?

 

[asnyder]

I work for a bunch of idiots. The numbers will tell them what place an employee holds on a waiting list, and they don't want a report.

 

[Jon K]

Where would you put this code for a regular form - not a subform?

The Before Insert event of the regular form, just like a subform, as we can see the figure before the record is saved.