Autonumber

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

"John Vinson" wrote in message
...
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]

On Tue, 19 Dec 2006 12:05:40 GMT, "Ivor Williams"
wrote:

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]

"John Vinson" wrote in message
...
On Tue, 19 Dec 2006 12:05:40 GMT, "Ivor Williams"
wrote:

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]

Private Sub Form_BeforeInsert(Cancel As Integer)

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

End Sub

On Wed, 20 Dec 2006 01:11:44 GMT, "Ivor Williams"
wrote:

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]

"Ivor Williams" wrote in message
news:k50ih.508560$R63.501912@pd7urf1no...



"John Vinson" wrote in message
...
On Tue, 19 Dec 2006 12:05:40 GMT, "Ivor Williams"
wrote:

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]

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"John Vinson" wrote in message
...
On Wed, 20 Dec 2006 01:11:44 GMT, "Ivor Williams"
wrote:

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

Does it filter on the ProjNo field? No

John W. Vinson[MVP]

On Fri, 22 Dec 2006 01:24:20 GMT, "Ivor Williams"
wrote:


"John Vinson" wrote in message
.. .
On Wed, 20 Dec 2006 01:11:44 GMT, "Ivor Williams"
wrote:

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]