Auto Numbering in existing numbers

[Guest]

I have a database of account numbers, and wish to make the account numbers
and auto numbering field upon using a form to enter each new record. I have
gone through and created a "blank/dummy" record to eliminate any number
sequence gaps. however, what's taking place it users skipping numbers within
the sequence, so auto number will be perfect. But I can't get auto number to
line up with the current numbers used. ie...the manual acct number and the
auto number are not the same, and they need to be...1 is 1, 2 is 2, 3 is 3,
etc.

 

[BruceM]

You can't rely on autonumber creating a gapless sequence. Autonumber is
almost always used behind the scenes for the purpose of creating
relationships. There are rare cirucumstances where it may be OK to expose
it to the users, but it will almost surely develop gaps. If you wish to
have an incrementing sequence without gaps there are a number of ways to do
that. One method may be found here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

 

[Joseph Meehan]

I have a database of account numbers, and wish to make the account
numbers and auto numbering field upon using a form to enter each new
record. I have gone through and created a "blank/dummy" record to
eliminate any number sequence gaps. however, what's taking place it
users skipping numbers within the sequence, so auto number will be
perfect. But I can't get auto number to line up with the current
numbers used. ie...the manual acct number and the auto number are
not the same, and they need to be...1 is 1, 2 is 2, 3 is 3, etc.

Auto numbers are not guaranteed to be consecutive, only unique (most of
the time)

I suggest you take a look at the Dmax function for a possible solution.

 

[Guest]

Hi

I would not bother. Say you have 3 generations living at the same address.
John Smith, John Smith II and John Smith III.

Each of them have an account with you. So their account numbers 1,2 and 3.
John Smith (the elder) dies and John Smith III has a son (can you guess his
name).

This is just what the auto numbering in access is meant for. It will allow
you to differentiate between the accounts of each Mr Smith even though they
have the same address. They allow you to create relationships with other
tables so that the relationships are formed correctly.

They also stop anyone else from getting poor old John Smith the elder’s
account number by mistake – or in you case, when someone is trying to
"arrange" autonumbers ??

So “either†use an auto number or input some other number but in this case
(manually inputting) it “will†go wrong. Leave the auto number outside of
everything else you do.

You can always concencate some fields together to make an account number like
=[AutoNumber]&†“&[Surname]&†“&[DateofBirth]

Or (just in case of the John Smith scenario) you could use a lookup to see
if there are any people with the same 1st and 2nd name at the same address
and if so add A,B or C etc.

Good luck

 

[Guest]

The samples you included will work perfectly, however, my abilities as a
novice aren't quite going to be enough to help me implement this. Or I
haven't found the instructions in the samples as to how to go about using the
DMax method.

Can you help?

 

[BruceM]

In the AutoNumber problem database, if you click on Single User Example,
then click the Explanation button, you will see a description of how it
works. DMax looks for the highest value for a field.
DMax("ProductID","Product") finds the highest value for the ProductID field
in the Product table. The database window is the window that has Tables,
Queries, etc. (known in Access as Objects) on the left side and a listing of
the objects on the right side. Click Tables, click Product, and click
Design on the toolbar. This will open the table in design view. Click Yes
if it tells you that you can't open the table for modifications, since you
are only looking. Note that ProductID is a Number field.
Back to the Single User form. Click the form to select it, then click View

Design View (or click the Design View icon). Click View > Properties

(this opens what is known as the Property Sheet), click the Data tab, and
note that the form's Record Source is Product (the table). Click the
ProductID text box. Click View > Properties again if you don't see the
Property Sheet. Click the Data tab, and note that the Control Source is
Product (from the form's Record Source table), and note the Default Value
expression. If the highest number for ProductID is 6, the DMax expression
finds that value, and you then add one to it so that the new record is 7.
The Default Value applies only if the record is new.
The Single User example is a good place to become familiar with some of this
stuff, but if there is to be more than one user at a time you should look at
the Multi-User example.
If the methods given in the examples do not meet your needs, there are
alternatives, but take a look at the examples to start.

 

[Guest]

The DMax function is exactly what I want to happen, however when I try to put
the code in for the function in the default value i get a message saying
"Unknown function 'DMax' in validation expression or default value on
'Business License Table.ACCTID"

so where do you think I went wrong?

 

[BruceM]

You may have a references problem. See this link for more information (note
the other links on the page if you need more information):
http://allenbrowne.com/ser-38.html

If no luck, post the DMax expression exactly as you have entered it. It
looks to me as if you may have the table and the field and the field run
together. The expression needs quote marks. Substitute your field name for
"ProductID", and your table name for "Product". If the table name or field
name has spaces, try enclosing it in square brackets:
DMax("[Field Name]","[Table Name]")

 

[John W. Vinson]

The DMax function is exactly what I want to happen, however when I try to put
the code in for the function in the default value i get a message saying
"Unknown function 'DMax' in validation expression or default value on
'Business License Table.ACCTID"

so where do you think I went wrong?

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

John W. Vinson [MVP]

 

[Guest]

=DMax("[ACCTID]","[Business License Table]")+1

This is a copy of what i have entered.

You may have a references problem. See this link for more information (note
the other links on the page if you need more information):
http://allenbrowne.com/ser-38.html

If no luck, post the DMax expression exactly as you have entered it. It
looks to me as if you may have the table and the field and the field run
together. The expression needs quote marks. Substitute your field name for
"ProductID", and your table name for "Product". If the table name or field
name has spaces, try enclosing it in square brackets:
DMax("[Field Name]","[Table Name]")

The DMax function is exactly what I want to happen, however when I try to
put
the code in for the function in the default value i get a message saying
"Unknown function 'DMax' in validation expression or default value on
'Business License Table.ACCTID"

so where do you think I went wrong?

 

[Guest]

Actually, i'm the biggest dummie in the world. I was trying to put this
function in the default value within the "Table" rather then the default
value of the form that updates the table.

Now I just need to figure out how to insert a code giving the current date
and a function at the bottom of my form to print and open a page to print my
report

 

[Jamie Collins]

You may have a references problem. See this link for more information (note
the other links on the page if you need more information):http://allenbrowne.com/ser-38.html

FWIW I have no references issue and I get the same error when I use
the expression as the DEFAULT for a column e.g.

CREATE TABLE [Business License Table]
(
[ACCTID] INTEGER DEFAULT (DMax("[ACCTID]","[Business License
Table]")+1),
col2 INTEGER
);

Jamie.

--

 

[BruceM]

The example in the link I posted put the expression into the default value
of the text box bound to the field. The OP explained that he had mistakenly
put the expression into the default value of the table, where it would
generate an error. Based on the reasonable assumption that he had put the
expression into the text box default value as indicated by the sample
database, it sounded like a references issue.

You may have a references problem. See this link for more information
(note
the other links on the page if you need more
information):http://allenbrowne.com/ser-38.html

FWIW I have no references issue and I get the same error when I use
the expression as the DEFAULT for a column e.g.

CREATE TABLE [Business License Table]
(
[ACCTID] INTEGER DEFAULT (DMax("[ACCTID]","[Business License
Table]")+1),
col2 INTEGER
);

Jamie.

 

[BruceM]

Actually, the default value of the text box bound to the incrementing field.
To insert the current date you could put =Date() as the Control Source of an
unbound text box. This would be fine if you wish to show the day a report
was printed. If you wish to store the value you could use the Date function
to insert today's date into a table field.
It's not clear what you mean by "a function at the bottom of my form to
print and open a page to print my report". Do you intend to print the form?
What is the page you want to open.
To print a report you can use the command button wizard to get yourself
headed in the right direction. Open the toolbox, and be sure the magic wand
icon is highlighted (click it if not). Click the command button icon, draw
a command button on your form, and follow the prompts.
You can also add a command button without using the wizard. In its Click
event you would have something like:
DoCmd.OpenReport "rptYourReport", acViewPreview

Substitute your report name for rptYourReport. acViewPreview lets you look
at the report; otherwise it is just printed (if I recall correctly what
happens if acViewPreview is left out).

 

[Guest]

What I am looking to create is a print button at the bottom of my form to
print a report containing some of this just entered information. However, I
need it to only print this one individual copy of the report rather then
printing all 4000+ everytime.

 

[John W. Vinson]

FWIW I have no references issue and I get the same error when I use
the expression as the DEFAULT for a column e.g.

You can't use functions which refer to the table (or any other table) in a
Table default value - and you can't use user-defined functions there at all;
only in Form control defaults.

John W. Vinson [MVP]

 

[BruceM]

To print a report for just the current record you can use something like
this in the command button's Click event (FieldID is the name of the primary
key or other unique field in the Record Source):

Dim strDoc As String
Dim strCriteria As String

strCriteria = "FieldID = " & Me.FieldID
strDoc = "YourReportName"

DoCmd.OpenReport strDoc, acPreview, , strCriteria

The longhand view of that line of code is:
DoCmd.OpenReport "YourReportName", acViewPreview, , "FieldID = " &
Me.FieldID

By defining the variables strDoc and strCriteria you can use those instead
of the longer expressions they represent.

Note that the above assumes FieldID is a numeric field. If it is a text
field:
strCriteria = "FieldID = """ & Me.FieldID & """"
Expanded for clarity:
strCriteria = "FieldID = " " " & Me.FieldID & " " " "

All of this assumes that the report is based on a table or query that
includes FieldID as one of its fields.

 

[Guest]

This makes some sense to me, however, I'm confused as where I insert the
lines of code. When I go to the Event for the print button function and
click the "..." to edit i go into visual basic editor. There are many many
lines of code there, how do I know where to put this line of code you gave me?

What is there now:

Option Compare Database

Private Sub Print_Official_License_Click()
On Error GoTo Err_Print_Official_License_Click

Dim stDocName As String

stDocName = "Official License"
DoCmd.OpenReport stDocName, acNormal

Exit_Print_Official_License_Click:
Exit Sub

Err_Print_Official_License_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Print_Official_License_Click

End Sub
Private Sub Print_Business_License_Click()
On Error GoTo Err_Print_Business_License_Click

Dim stDocName As String

stDocName = "Official License"
DoCmd.OpenReport stDocName, acPreview

Exit_Print_Business_License_Click:
Exit Sub

Err_Print_Business_License_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Print_Business_License_Click

End Sub

 

[BruceM]

In form design view, click the command button to select it, then click View

Properties. This opens what is known as the Property Sheet. Click the

Event tab. You will see, among other things, "On Click". Click the row (it
may already contain the words [Event Procedure], then click the three dots.
If it says [Event Procedure] the Visual Basic editor will appear. If not,
you would choose Code Builder, then OK to open the VB editor.
I think this is what you have already described.
You have a command button called Print_Official_License. It's Click event
starts with:

Private Sub Print_Official_License_Click()

The On Error line of code is part of the error handling. If there is an
error, the code will shoot down to the Err_Print_Official_License_Click line
(which is what the OnError line specified). After that is the declaration
of the variable stDocName, and its definition as "Official License", which I
assume is the name of the report you wish to print. Replace acNormal with
acViewPreview, and the report will open in print preview so that you can
view it before deciding to print it. With acNormal, as I recall, it just
prints immediately. Add a comma, then another, the put it the Where
condition as I have written it, except use the relevant field name from your
own database. If there is a primary key field you can use that in the
expression. Your line of code may end up looking something like:
DoCmd.OpenReport stDocName, acViewPreview, , "LicenseID = " & Me.LicenseID
Note the double comma. If there is to be a named filter it would go between
those commas. If there is no filter, the comma is still needed as a
placeholder.
What you are saying here is "Open the report to the record in which the
field LicenseID is the same as LicenseID on this form."

The next bit of code that starts with Private Sub
Print_Business_License_Click() seems to be for another command button named
Print_Business_License, and is the same except that you are opening the form
in preview mode (that is, it is displayed on the screen rather than being
printed immediately).

If you have two command buttons, one to print the form and the other to
preview it first, you will need both Click events, one for each button.

You may do well to use some more helpful error handling code. Instead of:
MsgBox Err.DESCRIPTION

you could have something like:
MsgBox "Error #" & Err.Number & " (" & Err.Description & ") in
Print_Business_License_Click"

 

[Guest]

OK, this works, now what type of code do I insert to have the form "close"
when I press the print button. I know it has to be in the print button code
somewhere, just not sure where to insert it, or what the code is.

In form design view, click the command button to select it, then click View

Properties. This opens what is known as the Property Sheet. Click the

Event tab. You will see, among other things, "On Click". Click the row (it
may already contain the words [Event Procedure], then click the three dots.
If it says [Event Procedure] the Visual Basic editor will appear. If not,
you would choose Code Builder, then OK to open the VB editor.
I think this is what you have already described.
You have a command button called Print_Official_License. It's Click event
starts with:

Private Sub Print_Official_License_Click()

The On Error line of code is part of the error handling. If there is an
error, the code will shoot down to the Err_Print_Official_License_Click line
(which is what the OnError line specified). After that is the declaration
of the variable stDocName, and its definition as "Official License", which I
assume is the name of the report you wish to print. Replace acNormal with
acViewPreview, and the report will open in print preview so that you can
view it before deciding to print it. With acNormal, as I recall, it just
prints immediately. Add a comma, then another, the put it the Where
condition as I have written it, except use the relevant field name from your
own database. If there is a primary key field you can use that in the
expression. Your line of code may end up looking something like:
DoCmd.OpenReport stDocName, acViewPreview, , "LicenseID = " & Me.LicenseID
Note the double comma. If there is to be a named filter it would go between
those commas. If there is no filter, the comma is still needed as a
placeholder.
What you are saying here is "Open the report to the record in which the
field LicenseID is the same as LicenseID on this form."

The next bit of code that starts with Private Sub
Print_Business_License_Click() seems to be for another command button named
Print_Business_License, and is the same except that you are opening the form
in preview mode (that is, it is displayed on the screen rather than being
printed immediately).

If you have two command buttons, one to print the form and the other to
preview it first, you will need both Click events, one for each button.

You may do well to use some more helpful error handling code. Instead of:
MsgBox Err.DESCRIPTION

you could have something like:
MsgBox "Error #" & Err.Number & " (" & Err.Description & ") in
Print_Business_License_Click"

This makes some sense to me, however, I'm confused as where I insert the
lines of code. When I go to the Event for the print button function and
click the "..." to edit i go into visual basic editor. There are many
many
lines of code there, how do I know where to put this line of code you gave
me?

What is there now:

Option Compare Database

Private Sub Print_Official_License_Click()
On Error GoTo Err_Print_Official_License_Click

Dim stDocName As String

stDocName = "Official License"
DoCmd.OpenReport stDocName, acNormal

Exit_Print_Official_License_Click:
Exit Sub

Err_Print_Official_License_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Print_Official_License_Click

End Sub
Private Sub Print_Business_License_Click()
On Error GoTo Err_Print_Business_License_Click

Dim stDocName As String

stDocName = "Official License"
DoCmd.OpenReport stDocName, acPreview

Exit_Print_Business_License_Click:
Exit Sub

Err_Print_Business_License_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Print_Business_License_Click

End Sub