Line numbering (on report/form?) (2000 – 2)

Topic

New Reply

I am trying to do something that is a waste of time?

Say I have two tables with a one-to-many relationship. I’d like to automatically increment the value in a number field in the ‘many’ table. However, I would like to start the numbering at 1 each time. i.e for each record in the ‘one’ table there would typically be six related records in the ‘many’ table and I would like to automatically assign them numbers 1 to 6 and for these number to be visible on a data entry form. I’m pretty certain that meddling with a proper autonumber field is not the way to go.

Maybe I don’t even need to store these numbers as what I really need is to print them next to the item on a report. I’ve seen the sample reports database which includes an example report that does this.

Would trying to do the same thing on a form be a pointless exercise??

Reply | Quote

Replies

Group your report on the one table.
In the detail section of the report, add a textbox control.
Set the control source of this textbox to =1 (include the equal sign)
Set the Running Sum property to Over Group

Reply | Quote

Thanks Francois, I think that’s how the sample report I’ve seen does it, I just haven’t had chance to look at it properly yet.

My real question is would there be any point/would it be possible to do something similar on a form?

Reply | Quote

There isn’t any point on a form because the sequence of the records can be changed by the user. In a report it at least makes marginal sense in case someone wants to refer to a particular line of a printed report. Forms are interactive and Access records don’t really have numbers. What would you be accomplishing by showing a phony number on the form?

Reply | Quote

Thanks, I didn’t really think there was any point but I’d got a bit confused.

However, on a similar note, I’ve mostly been using Autonumbers as key fields and a visible identifier to a record on forms on reports. Currently, I need to assign a sequential numeric ID value (Report_ID) to records associated with a particular client (ClientName) i.e the Report_ID is not unique in the table but would be for records with the same client name..

Any suggestions on the best approach. I was thinking of making ClientName and Report_ID both key IDs but would the user have to type the ID manually, with me writing code to check for duplication/prompting for the next number.

At the end of the day, the database doesn’t justify having a much time put into it, so if I don’t want to get into something that’ll be more complicated than I first thought.

Reply | Quote

You could use the DMax function. Something like:
DMax(“[ReportID]”, “YourTableName”, “[ClientName] = ‘Bill Gates'”) + 1

Reply | Quote

Cheers. I think this will also come in useful for another database I’m working on.

Reply | Quote