Autonumber

[Ivor Williams]

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

 

[John Vinson]

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]

 

[Ivor Williams]

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

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]

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]

 

[John Vinson]

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]

 

[Ivor Williams]

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]

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]

 

[Ivor Williams]

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, 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]

 

[John Vinson]

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 here:

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]

 

[Ivor Williams]

Your code works fine, except it puts "1" in the first record as expected,
but does not increment from there. Each record thereafter shows a ProgNo of
"1".
Your explanation of the logic was very helpful and easily understood.

Ivor

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 here:

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]

 

[John Vinson]

Your code works fine, except it puts "1" in the first record as expected,
but does not increment from there. Each record thereafter shows a ProgNo of
"1".

Please post your actual code. Perhaps you need to have the DMax()
function reference the actual table, rather than the query - I have no
idea what records the query returns!

John W. Vinson[MVP]

 

[Ivor Williams]

Please post your actual code. Perhaps you need to have the DMax()
function reference the actual table, rather than the query - I have no
idea what records the query returns!

John W. Vinson[MVP]

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.txtProgNo = Nz(DMax("[ProgNo]", "qryProgresses", _
"[ProjNo] = '" & Me.txtProjNo & "'")) + 1

End Sub

 

[John Vinson]

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.txtProgNo = Nz(DMax("[ProgNo]", "qryProgresses", _
"[ProjNo] = '" & Me.txtProjNo & "'")) + 1

End Sub

What is the SQL of qryProgresses?
Does it return just one record, or multiple records?
Does it filter on the ProjNo field?

John W. Vinson[MVP]

 

[Douglas J. Steele]

Please post your actual code. Perhaps you need to have the DMax()
function reference the actual table, rather than the query - I have no
idea what records the query returns!

John W. Vinson[MVP]

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.txtProgNo = Nz(DMax("[ProgNo]", "qryProgresses", _
"[ProjNo] = '" & Me.txtProjNo & "'")) + 1

End Sub

That should probably be

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.txtProgNo = Nz(DMax("[ProgNo]", "qryProgresses", _
"[ProjNo] = '" & Me.txtProgNo & "'")) + 1

End Sub

You're currently looking up the value of Me.txtProjNo, not Me.txtProgNo.

The fact that you're not getting any error implies that you don't have
Option Explicit turned on. When Option Explicit appears in a module, you
must explicitly declare all variables using the Dim, Private, Public, ReDim,
or Static statements. If you attempt to use an undeclared variable name, an
error occurs at compile time. If Me.txtProjNo doesn't actually exist, VBA
will treat it as 0, rather than raising the error it should.

To ensure that Option Explicit is added to all modules in the future, go
into Tools | Options in the VB Editor. Make sure that the "Require Variable
Declaration" box is checked. Unfortunately, you'll need to go back to all
existing modules and insert the line Option Explicit at the very beginning.

 

[Ivor Williams]

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.txtProgNo = Nz(DMax("[ProgNo]", "qryProgresses", _
"[ProjNo] = '" & Me.txtProjNo & "'")) + 1

End Sub

What is the SQL of qryProgresses?

SELECT tblProjects.ProjNo, tblProgresses.ProgressID, tblProgresses.Project,
tblProgresses.ProgNo, tblProgresses.LabPosted, tblProgresses.LastLabDate,
tblProgresses.LabSim, tblProgresses.LabNotPosted, tblProgresses.LabME,
tblProgresses.MatPosted, tblProgresses.LastMatDate, tblProgresses.MatSim,
tblProgresses.MatNotPosted, tblProgresses.MatME
FROM tblProjects INNER JOIN tblProgresses ON tblProjects.ProjNo =
tblProgresses.Project;

Does it return just one record, or multiple records? Multiple records

 

[John Vinson]

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.txtProgNo = Nz(DMax("[ProgNo]", "qryProgresses", _
"[ProjNo] = '" & Me.txtProjNo & "'")) + 1

Try basing the DMax() directly on tblProgresses:

Me.txtProgNo = Nz(DMax("[ProgNo]", "tblProgresses", _
"[ProjNo] = '" & Me.txtProjNo & "'")) + 1

John W. Vinson[MVP]