Reporting an incremented "ID" with a sorting requirement

[LianeMT]

I'm working on a document control database (assigns document numbers and
tracks a document from inception to release and through revisions and
signature approval process). Thanks to help from this forum, I've worked out
a multi-field "ID" system to meet the requirements for the document numbering
system and set up a query to display the document number with the multiple
fields concatenated. Essentially:

FORMAT: XXXYYZZZZRR-WWW
Examples: 301AA2112B-000, 301D1492BB-002, 301TR1973A-003

Each set of letters is its own field (XXX is the main designator, YY is the
document type, ZZZZ is the numeric series for the document, RR is the
revision increment, and -WWW is essentially identifying multiple parts in an
assembly).

For sorting and assigning new numbers, I'm only concerned with the XXXYYZZZZ
section and my query fills a table with the document numbers' individual
fields and a separate index with the concatenated "ID" XXXYYZZZZ. I've set
this to presort based on ascending values for these three pieces.

I would like to set up another query or form that increments to tell me the
next available document number in the ZZZZ series, based on which YY I select
(for now, all the XXX indicators are the same, i.e. "301"). This will allow
me to issue new numbers for documents without having to run a report to scan
through all the numbers or manually go through the table. That's the goal
anyway.

I appreciate any insight offered! Thank you!
Liane

 

[Klatuu]

You don't need a query or a form. A DMax function will do it for you.

It would be something like:

NextNumber = Nz(DMax("[series]","MyTable", "[MainDoc] = " & Me.txtMainDoc &
" And [DocType] = """ & Me.txtDocType & """"),0) + 1

The DMax finds the current highest number filtered by the other two
designatores. If no match is found (it is the first doc for this
combination), the Nz function converts the Null returned by DMax to 0. Then
1 is added to the results to give you the next number.

If you want it formatted for 4 zeros, then
=Format(NextNumber,"0000")

 

[LianeMT]

Thanks, Dave! Here's what I've interpreted from your note. I apologize that
my variable names are cumbersome, but they fit a scheme I inherited.

From your example:
NextNumber = Nz(DMax("[series]","MyTable", "[MainDoc] = " & Me.txtMainDoc &
" And [DocType] = """ & Me.txtDocType & """"),0) + 1

I substituted my variables:
NextNumber = Nz(DMax("[NumberBlock]", "DocNumber_Qry", "[ProgramDesignator]
= " & Me.txtProgramDesignator & " And [GroupDesignator] = """ &
Me.txtGroupDesignator & """"),0) +1

based on:

XXX = Main Designator = ProgramDesignator - MainDoc
YY = Document Type = GroupDesignator - DocType
ZZZZ = Numeric Series = NumberBlock - series
Table/Query = DocNumber_Qry - MyTable

I've put this DMax function into an existing form where the administrator
inputs the document numbers (populating the table/query for tracking the
numbers) hoping to facilitate grabbing the next number being issued. Honestly
I didn't know where else to put it. Essentially I would like the
administrator to be able to enter the Program Designator (currently 301), and
select the Group Designator to kick off the NextNumber being reported in a
text field on the form where I've put the DMax function. Then the
administrator can use the NextNumber to input the NumberBlock (series)
identifier) and the other two fields to add this new number to the database.

Unfortunately, I haven't linked the DMax function correctly, because I get
the #Name? returned in the NextNumber field instead of the NextNumber I'm
trying to report.

I'm sure there's a more elegant way to do this, so again, I appreciate any
input to either do this better or simply to fix the disconnect I have in the
current implementation.

Thanks much!

 

[Klatuu]

I would have a text box on the form for the Number Series.
I would also have a command button positioned next to it to retrieve the
next number. In Design view, set the command button's Enabled Property to
No. You only want it to work for new records.

Then in the form Current event enable the button for new records and disable
it for existing records:

Me.cmdGetNewNumber.Enabled = Me.NewRecord


The put the code in the Click event of the command button.

 

[LianeMT]

I would have a text box on the form for the Number Series.

I have this.

I would also have a command button positioned next to it to retrieve the
next number.

I added this.

In Design view, set the command button's Enabled Property to
No. You only want it to work for new records.

This makes sense. I did that.

Then in the form Current event enable the button for new records and
disable it for existing records:

Me.cmdGetNewNumber.Enabled = Me.NewRecord

After Access complained about “Me†I guessed that I needed to replace “Meâ€
with the name of my form, thusly:

DocNumber_Form.cmdNextNumber_Cmd.Enabled = DocNumber_Form.NewRecord

Then put the code in the Click event of the command button.

Okay, I did this, too (placed the DMax command syntax from the previous
response here). I also then assigned the control source for my Next Document
Number text box to =NextNumber_Cmd.OnClick, hoping the value that the command
button summons would get reported here.

I realize it may seem redundant to have both this “Next Document Numberâ€
text box and the “Number Series†text box, but here’s my rationale: The
purpose of this form is to populate the list of document numbers as they are
being issued. I need to know the Next number in the series when I have to
issue a brand new number and I’m only concerned with the Number Base of
XXXYYZZZZ. But I also need the ability to add the revision and sequence
numbers when existing documents get revised or supplemental pieces are added
to them (without issuing entirely new numbers). The form has fields for these
two pieces of information; I’m just not using them to generate the Next
Document Number in the series as they’re not relevant.

Anyway. This form/command to get the next number is still broken and Access
gives me the following error:

“…can’t find the macro ‘DocNumber_Form.’ The macro (or its macro group)
doesn’t exist, or the macro is new but hasn’t been saved. Note that when you
enter the macrogroupname.macroname syntax in an argument, you must specify
the name the macro’s macro group was last saved under.â€

Huh? I recognize that I’m now beginning to use VBA commands (?) but I
haven’t created any macros. Should I? What should they be? This is definitely
beyond the realm of my familiarity with Access…

Again, my thanks for your help, Dave!

 

[Klatuu]

Me.cmdGetNewNumber.Enabled = Me.NewRecord
Doe not go in the properties dialog text box for the current event.
You select Code Builder and put it in there.
Me. is shortcut for the form name and the correct syntax when using the form
name would be:
Forms!FormName!ControlName

Okay, I did this, too (placed the DMax command syntax from the previous
response here). I also then assigned the control source for my Next Document
Number text box to =NextNumber_Cmd.OnClick, hoping the value that the command
button summons would get reported here.

This is contridictory. If you put an expression in the control source of a
control, it is not bound to your table and will not update anything.
If you want to see what the next number would be, it needs to be in a
separate unbound control. But, this could be misleading in a multi user
environment. If your have the value stored in a control on your form and
another user creates a new record, your number is now incorrect.

 

[LianeMT]

All right, I found my way into code builder and tried again. I put the
recommended command

Me.cmdNextNumber_Cmd.Enabled = Me.NewRecord

into the Form_Current section.

I put the command

NextNumber = Nz(DMax("[NumberBlock]", "DocNumber_Qry",
"[ProgramDesignator] = " & Me.txtProgramDesignator & " And [GroupDesignator]
= """ & Me.txtGroupDesignator & """"), 0) + 1

into the NextNumber_Cmd_Click() section (after Dim NextNumber As String)

I'm still not getting the command button enabled when activating a new
record and don't understand how to report the value for NextNumber. How does
one use a separate unbound control to show the value?

Thanks.

 

[Michael Gramelspacher]

Thanks, Dave! Here's what I've interpreted from your note. I apologize that
my variable names are cumbersome, but they fit a scheme I inherited.

From your example:
NextNumber = Nz(DMax("[series]","MyTable", "[MainDoc] = " & Me.txtMainDoc &
" And [DocType] = """ & Me.txtDocType & """"),0) + 1

I substituted my variables:
NextNumber = Nz(DMax("[NumberBlock]", "DocNumber_Qry", "[ProgramDesignator]
= " & Me.txtProgramDesignator & " And [GroupDesignator] = """ &
Me.txtGroupDesignator & """"),0) +1

based on:

XXX = Main Designator = ProgramDesignator - MainDoc
YY = Document Type = GroupDesignator - DocType
ZZZZ = Numeric Series = NumberBlock - series
Table/Query = DocNumber_Qry - MyTable

I've put this DMax function into an existing form where the administrator
inputs the document numbers (populating the table/query for tracking the
numbers) hoping to facilitate grabbing the next number being issued. Honestly
I didn't know where else to put it. Essentially I would like the
administrator to be able to enter the Program Designator (currently 301), and
select the Group Designator to kick off the NextNumber being reported in a
text field on the form where I've put the DMax function. Then the
administrator can use the NextNumber to input the NumberBlock (series)
identifier) and the other two fields to add this new number to the database.

Unfortunately, I haven't linked the DMax function correctly, because I get
the #Name? returned in the NextNumber field instead of the NextNumber I'm
trying to report.

I'm sure there's a more elegant way to do this, so again, I appreciate any
input to either do this better or simply to fix the disconnect I have in the
current implementation.

Thanks much!

I saw this technique mentioned somewhere. Maybe it is something to consider,
maybe not:

Create a table called DocumentKeys with the fields that make up your key. As an
example:

CREATE TABLE DocumentKeys (
ProgramDesignator INTEGER NOT NULL,
GroupDesignator CHAR(2) NOT NULL,
NumberBlock INTEGER NOT NULL,
PRIMARY KEY (ProgramDesignator,GroupDesignator));

Function GetDocumentKey(Pdesig As Integer, _
Gdesig As String) As Integer

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim n As Integer
Dim s As String

On Error GoTo ErrorHandler

Set db = DBEngine(0)(0)

s = "SELECT NumberBlock FROM DocumentKeys " & _
"WHERE ProgramDesignator = " & Pdesig & " AND " & _
" GroupDEsignator = '" & Gdesig & "';"

Set rst = db.OpenRecordset(s)

With rst

.MoveFirst
n = .Fields(0)
.Edit
.Fields(0) = n + 1
.Update

End With
GetDocumentKey = n

ExitProcedure:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

ErrorHandler:
GetDocumentKey = 0
Resume ExitProcedure

End Function

 

[Klatuu]

The names I used were made up. You need to make the names in the code match
the names of the objects in your form. For example,
NextNumber_Cmd appears to be the name of your command button, but the code
is:
Me.cmdNextNumber_Cmd.Enabled = Me.NewRecord

It probably should be:
Me.NextNumber_Cmd.Enabled = Me.NewRecord

Check your other names.
--
Dave Hargis, Microsoft Access MVP

All right, I found my way into code builder and tried again. I put the
recommended command

Me.cmdNextNumber_Cmd.Enabled = Me.NewRecord

into the Form_Current section.

I put the command

NextNumber = Nz(DMax("[NumberBlock]", "DocNumber_Qry",
"[ProgramDesignator] = " & Me.txtProgramDesignator & " And [GroupDesignator]
= """ & Me.txtGroupDesignator & """"), 0) + 1

into the NextNumber_Cmd_Click() section (after Dim NextNumber As String)

I'm still not getting the command button enabled when activating a new
record and don't understand how to report the value for NextNumber. How does
one use a separate unbound control to show the value?

Thanks.

Doe not go in the properties dialog text box for the current event.
You select Code Builder and put it in there.
Me. is shortcut for the form name and the correct syntax when using the form
name would be:
Forms!FormName!ControlName


This is contridictory. If you put an expression in the control source of a
control, it is not bound to your table and will not update anything.
If you want to see what the next number would be, it needs to be in a
separate unbound control. But, this could be misleading in a multi user
environment. If your have the value stored in a control on your form and
another user creates a new record, your number is now incorrect.

 

[Klatuu]

That is not an unreasonable technique. I have seen it used also; however, it
all could be done with a DLookup as I do it to get the current high number
and an SQL Update query to save the new number back to the table.

Any time you can avoid using a recordset, you should. Not that recordsets
are bad, it is just a performance issue.

Also, when using Action Queries, the Currentdb.Execute method is the fastest
way to do it. It bypasses the Access UI and doesn't trigger warning
messages. But, because it doesn't go through Access, but directly to Jet,
you need to use th dbFailOnError option to be sure you get an error returned
if it fails.

But, there is one problem with your code. You are assuming the record will
be found. It will not always be, so you have to plan for that. It will not
be found for the first occurance of the maing designator and document type
combination.
--
Dave Hargis, Microsoft Access MVP

Thanks, Dave! Here's what I've interpreted from your note. I apologize that
my variable names are cumbersome, but they fit a scheme I inherited.

From your example:
NextNumber = Nz(DMax("[series]","MyTable", "[MainDoc] = " & Me.txtMainDoc &
" And [DocType] = """ & Me.txtDocType & """"),0) + 1

I substituted my variables:
NextNumber = Nz(DMax("[NumberBlock]", "DocNumber_Qry", "[ProgramDesignator]
= " & Me.txtProgramDesignator & " And [GroupDesignator] = """ &
Me.txtGroupDesignator & """"),0) +1

based on:

XXX = Main Designator = ProgramDesignator - MainDoc
YY = Document Type = GroupDesignator - DocType
ZZZZ = Numeric Series = NumberBlock - series
Table/Query = DocNumber_Qry - MyTable

I've put this DMax function into an existing form where the administrator
inputs the document numbers (populating the table/query for tracking the
numbers) hoping to facilitate grabbing the next number being issued. Honestly
I didn't know where else to put it. Essentially I would like the
administrator to be able to enter the Program Designator (currently 301), and
select the Group Designator to kick off the NextNumber being reported in a
text field on the form where I've put the DMax function. Then the
administrator can use the NextNumber to input the NumberBlock (series)
identifier) and the other two fields to add this new number to the database.

Unfortunately, I haven't linked the DMax function correctly, because I get
the #Name? returned in the NextNumber field instead of the NextNumber I'm
trying to report.

I'm sure there's a more elegant way to do this, so again, I appreciate any
input to either do this better or simply to fix the disconnect I have in the
current implementation.

Thanks much!

I saw this technique mentioned somewhere. Maybe it is something to consider,
maybe not:

Create a table called DocumentKeys with the fields that make up your key. As an
example:

CREATE TABLE DocumentKeys (
ProgramDesignator INTEGER NOT NULL,
GroupDesignator CHAR(2) NOT NULL,
NumberBlock INTEGER NOT NULL,
PRIMARY KEY (ProgramDesignator,GroupDesignator));

Function GetDocumentKey(Pdesig As Integer, _
Gdesig As String) As Integer

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim n As Integer
Dim s As String

On Error GoTo ErrorHandler

Set db = DBEngine(0)(0)

s = "SELECT NumberBlock FROM DocumentKeys " & _
"WHERE ProgramDesignator = " & Pdesig & " AND " & _
" GroupDEsignator = '" & Gdesig & "';"

Set rst = db.OpenRecordset(s)

With rst

.MoveFirst
n = .Fields(0)
.Edit
.Fields(0) = n + 1
.Update

End With
GetDocumentKey = n

ExitProcedure:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

ErrorHandler:
GetDocumentKey = 0
Resume ExitProcedure

End Function

 

[Michael Gramelspacher]

That is not an unreasonable technique. I have seen it used also; however, it
all could be done with a DLookup as I do it to get the current high number
and an SQL Update query to save the new number back to the table.

Any time you can avoid using a recordset, you should. Not that recordsets
are bad, it is just a performance issue.

Also, when using Action Queries, the Currentdb.Execute method is the fastest
way to do it. It bypasses the Access UI and doesn't trigger warning
messages. But, because it doesn't go through Access, but directly to Jet,
you need to use th dbFailOnError option to be sure you get an error returned
if it fails.

But, there is one problem with your code. You are assuming the record will
be found. It will not always be, so you have to plan for that. It will not
be found for the first occurance of the maing designator and document type
combination.

I saw the technique recommended for a multi-user enviornment, as a way to incure
every user would get a unique number. It is true that the first number needs to
be inserted somehow. Typically DMax is the method to use. No argument there.

 

[Klatuu]

You are correct. It will ensure each user gets a unique number. The only
problem is that a gap in numbering could happen if a user starts a new
record, then does not save the record, the number is discarded. But, that
may not be an issue.