John Vinson

[Ivor Williams]

John...

I hope it wasn't inappropriate to use your name as a subject, I wanted to
get your attention and thought I may not since you already responded to my
post.

My apologies, I should have provided details of the controls I have. I'm
sure what you suggested is correct, I just can't relate it to what I have to
work with.
On the primary form frmProjects, I have a text box named txtProjNo. This
displays the active Project Number which is used as a link to the
sfrProgresses subform.

On the sfrProgresses subform, are the following controls: txtProjNo (the
link), and txtProgNo (the control in which I want to have incremental
numbering) as well as others. Would you be so kind as to write your code
using these control names so I can make some sense of it. Many thanks for
your help.

Ivor

I have a database in which I want to track progress payments on projects
we
work on. In the database is a form in which I choose the project. A
subform
is used to view/enter data for each progress. I would like each record in
the subform to be numbered with an autonumber, but I want the autonumber
to
start at "1" for each project. is there a way to do this?

Ivor

Not using the Access Autonumber datatype; that gives a unique value
for every record in the table, and doesn't let you start over with a
new project.

What you can do instead is put code in the Subform's Beforeinsert
event to generate a new sequential number for that project. The code
might be something like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtSeqNo = NZ(DMax("[SeqNo]", "[Payments]", "[ProjectID] = " _
& Me.txtProjectID)) + 1
End Sub

using your own field and tablenames of course.

John W. Vinson[MVP]

 

[John Vinson]

John...

I hope it wasn't inappropriate to use your name as a subject, I wanted to
get your attention and thought I may not since you already responded to my
post.

Answered in the original thread.

John W. Vinson[MVP]

 

[Ivor Williams]

I'm sure it was, but is an answer that can't be understood an answer?
I do mean that in the nicest possible way. You've helped me many times in
the past, and I do appreciate it.

I've tried substituting my own table and field names, but obviously I'm not
getting the right combination, nor am I completely understanding the intent
of each part of the code.Consequently, I've not been able to make it work.

Ivor