Autonumber

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

It would not be an Autonumber but a number generated automatically. You can
use a query to find the highest number for that project (If null - 0) and add
one. Use this as the default for the textbox of the form used to add records.

Search on "record numbering."

"Ivor Williams" wrote:

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

On Fri, 15 Dec 2006 21:17:25 GMT, "Ivor Williams"
wrote:

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...

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




"John Vinson" wrote in message
...
On Fri, 15 Dec 2006 21:17:25 GMT, "Ivor Williams"
wrote:

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]

On Sun, 17 Dec 2006 16:09:49 GMT, "Ivor Williams"
wrote:

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.

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtProgNo = NZ(DMax("[ProgNo]", "[tablename]", _
"[ProjNo] = " & Me.txtProjNo)
End Sub

This assumes that the table to which the subform is bound is callet
tablename - use your own table name of course - and likewise that the
table contains a Number datatype field ProjNo which is bound to
txtProjNo. If ProjNo is of Text datatype instead of number, you need
some syntactical quotemarks:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtProgNo = NZ(DMax("[ProgNo]", "[tablename]", _
"[ProjNo] = '" & Me.txtProjNo & "'")
End Sub

John W. Vinson[MVP]

On Sun, 17 Dec 2006 16:09:49 GMT, "Ivor Williams"
wrote:

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.

Ok, I see we're still not connecting!

Please tell me:

- What is the Recordsource of sfrProgresses (post the SQL, or the
tablename)
- What is the Control Source of txtProjNo
- What is the Control Source of txtProgNo
- What are the datatypes of these two fields

John W. Vinson[MVP]

John, I'm so sorry. I didn't realize when you said "Answered in the original
thread" you meant you had responded in the original thread. I took it to
mean "I already answered this. You should be able to figure it out." Again,
thanks for your help. Please see my answers next to your questions below.

Ivor


"John Vinson" wrote in message
...
On Sun, 17 Dec 2006 16:09:49 GMT, "Ivor Williams"
wrote:

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.

Ok, I see we're still not connecting!

Please tell me:

- What is the Recordsource of sfrProgresses (post the SQL, or the
tablename) Recordsource is
qryProgresses
- What is the Control Source of txtProjNo ProjNo
- What is the Control Source of txtProgNo ProgNo
- What are the datatypes of these two fields Both are text

John W. Vinson[MVP]

On Mon, 18 Dec 2006 12:28:27 GMT, "Ivor Williams"
wrote:

John, I'm so sorry. I didn't realize when you said "Answered in the original
thread" you meant you had responded in the original thread. I took it to
mean "I already answered this. You should be able to figure it out." Again,
thanks for your help. Please see my answers next to your questions below.

Ok... the first question is WHY are you using a Text datatype for a
value which will contain an incrementing numeric value? Is there any
chance you could use a Number datatype for ProgNo? As it is, if you
look for the maximum value of ProgNo, it will find that "9" is bigger
than "10", using text sort order. It makes the code a lot more
complex!

If not, try a getaround, converting the Text to number and back:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtProgNo = Format(NZ(DMax("Val([ProgNo])", "qryProgresses", _
"[ProjNo] = '" & Me.txtProjNo & "'"), 0),"#")
End Sub

I haven't tested this; it might be necessary to put a calculated field
in qryProgresses converting the text string in ProgNo to number:

ProgNoNumeric: Val([ProgNo])

and using that calculated field in the DMax() expression.

John W. Vinson[MVP]

ProgNo can be a Number datatype. ProjNo is better left as Text.
I've tried picking what I think I need from your code below and inserting it
in my database with some slight modifications, but no luck so far. I see in
your code below, you've included apostrophes on either side of &
Me.txtProjNo &. What's the reason for this?

Ivor

"John Vinson" wrote in message
...
On Mon, 18 Dec 2006 12:28:27 GMT, "Ivor Williams"
wrote:

John, I'm so sorry. I didn't realize when you said "Answered in the
original
thread" you meant you had responded in the original thread. I took it to
mean "I already answered this. You should be able to figure it out."
Again,
thanks for your help. Please see my answers next to your questions below.

Ok... the first question is WHY are you using a Text datatype for a
value which will contain an incrementing numeric value? Is there any
chance you could use a Number datatype for ProgNo? As it is, if you
look for the maximum value of ProgNo, it will find that "9" is bigger
than "10", using text sort order. It makes the code a lot more
complex!

If not, try a getaround, converting the Text to number and back:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtProgNo = Format(NZ(DMax("Val([ProgNo])", "qryProgresses", _
"[ProjNo] = '" & Me.txtProjNo & "'"), 0),"#")
End Sub

I haven't tested this; it might be necessary to put a calculated field
in qryProgresses converting the text string in ProgNo to number:

ProgNoNumeric: Val([ProgNo])

and using that calculated field in the DMax() expression.

John W. Vinson[MVP]

On Tue, 19 Dec 2006 03:08:31 GMT, "Ivor Williams"
wrote:

ProgNo can be a Number datatype. ProjNo is better left as Text.
I've tried picking what I think I need from your code below and inserting it
in my database with some slight modifications, but no luck so far. I see in
your code below, you've included apostrophes on either side of &
Me.txtProjNo &. What's the reason for this?

Ivor

Search criteria for a Text field (such as in a DMax() criteria
argument) must be delimited by either ' or " quotemarks; Date/Time
fields must be delimited by #; Number fields don't use any delimiter.

If ProgNo is numeric, your expression can be two function calls
simpler. I realized I left out a very critical + 1 - SORRY!

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtProgNo = NZ(DMax("[ProgNo]", "qryProgresses", _
"[ProjNo] = '" & Me.txtProjNo & "'")) + 1
End Sub

To break down the logic he

DMax("[ProgNo]", "qryProgresses", criteria)

will find the maximum value of ProgNo in the query qryProgresses for
the given criteria. If the criterion is

"[ProjNo] = '" & Me.txtProjNo & "'"

it will be evaluated to

[ProjNo] = 'A3123'

if txtProjNo contains A3123.

If there are no ProgNo values for this project - that is, this is the
first entry for the project - the DMax() function will return NULL.

Passing the DMax() function result to the function NZ will return 0 if
that is the case, otherwise it will return whatever number was found
by DMax.

The expression then adds 1 to that result (giving 1 for the first
entry, or 31 if there are already 30 entries) and put that incremented
value into the textbox txtProgNo.

John W. Vinson[MVP]