How do I set up an increment field base on another field?

[Guest]

I have a table with the fields COUNTRY and DESP NO.
I want every time a new country is enter in the field COUNTRY the DESP.NO to
be increased by 1.

Example:

COUNTRY DESP.NO
UK 1
GREECE 1
GREECE 2
ITALY 1
UK 2

 

[peregenem]

I have a table with the fields COUNTRY and DESP NO.
I want every time a new country is enter in the field COUNTRY the DESP.NO to
be increased by 1.

To determine the (relative) *time* you need a DATETIME column.

 

[Rick B]

What on earth does this have to do withthe questione that was asked? The
user wants to increment a field based on another field as he demonstrated in
the example.

 

[Tim Ferguson]

I have a table with the fields COUNTRY and DESP NO.
I want every time a new country is enter in the field COUNTRY the
DESP.NO to be increased by 1.

Look up Google for "Access Custom Autonumber" for details.

In essence, you can only do this using coding behind a form: you have to
prevent your users from creating records using table datasheets, Excel,
queries etc etc. You can find the next number to given out with code
like:

' make the criterion first
sqlWhere = "Country = """ & txtCountryCode & """"

' look up any existing record: use a variant because
' DMax returns a NULL if there is no result
varHighestSoFar = DMax("DespNo", "MyTable", sqlWhere)

' now return the next value:
If IsNull(varHighestSoFar) Then
NextDespNumber = 1

Else
NextDespNumber = varHighestSoFar + 1

End If



Note that this may not be safe in a multi-user setting, where you may
need something a little more sophisticated.

Hope that helps


Tim F

 

[John Vinson]

I have a table with the fields COUNTRY and DESP NO.
I want every time a new country is enter in the field COUNTRY the DESP.NO to
be increased by 1.

Example:

COUNTRY DESP.NO
UK 1
GREECE 1
GREECE 2
ITALY 1
UK 2

Use a Form to enter data into the table. In the AfterUpdate event of
the Country control (I'd suggest using a combo box so you don't have
to type out each country name) put code like

Private Sub Country_AfterUpdate()
Me![Desp.No] = NZ(DMax("[Desp.No]", "[tablename]", _
[Country] = '" & Me![Country] & "'"
End Sub

There is no way to do this in a Table or a table's properties.

John W. Vinson[MVP]

 

[Guest]

Thanks for your help but I dont know why I get error messages.
I copy the code excactly what you write I only change the table name because
the name o my table is table1. I dont know why and I dont know what else to
do. I really need it. I have to finished by project until Monday.
Thank you anyway!

I have a table with the fields COUNTRY and DESP NO.
I want every time a new country is enter in the field COUNTRY the DESP.NO to
be increased by 1.

Example:

COUNTRY DESP.NO
UK 1
GREECE 1
GREECE 2
ITALY 1
UK 2

Use a Form to enter data into the table. In the AfterUpdate event of
the Country control (I'd suggest using a combo box so you don't have
to type out each country name) put code like

Private Sub Country_AfterUpdate()
Me![Desp.No] = NZ(DMax("[Desp.No]", "[tablename]", _
[Country] = '" & Me![Country] & "'"
End Sub

There is no way to do this in a Table or a table's properties.

John W. Vinson[MVP]

 

[Guest]

Dear Tim Thank you but I have problem.
First of all where I have to write your script.
I wrote it to the AfterUpdate event of the country field. But I am getting
error messages.

I dont know what else to do and I have to finished my project until Monday.

Thank you anyway for your help.

 

[peregenem]

What on earth does this have to do withthe questione that was asked? The
user wants to increment a field based on another field as he demonstrated in
the example.

So do you agree this "DESP NO" is a calculated column? But how do we
know how to rank the "DESP NO" against each occurrence of country name?


One solution would be this

CREATE TABLE Countries (
ID COUNTER NOT NULL,
COUNTRY TEXT(30) NOT NULL,
CONSTRAINT pk__Countries PRIMARY KEY (ID));

INSERT INTO Countries (COUNTRY) VALUES ('UK')
INSERT INTO Countries (COUNTRY) VALUES ('GREECE');
INSERT INTO Countries (COUNTRY) VALUES ('GREECE');
INSERT INTO Countries (COUNTRY) VALUES ('ITALY');
INSERT INTO Countries (COUNTRY) VALUES ('UK');

SELECT T1.COUNTRY, (
SELECT COUNT(*)
FROM Countries
WHERE T1.COUNTRY = COUNTRY
AND ID <= T1.ID
) AS [DESP NO]
FROM Countries AS T1;

The nature of the autonumber (COUNTER) column give us the order.
Non-maintained clustered index, monotonic integer, exposed physical
locator - call it what you like but what we are talking about is
date/time order it was entered into the system and has nothing to do
with the data model. As soon as the system becomes part of the model
you know you have problems e.g. what if a row was added to the system
'late'?

In my experience there is something in the model to give such order and
it is usually a data element of DATETIME type. For example

CREATE TABLE Orders (
order_nbr TEXT(10) NOT NULL,
dispatch_date DATETIME NOT NULL,
dispatch_country TEXT(30) NOT NULL,
CONSTRAINT pk__Orders PRIMARY KEY (order_nbr));

INSERT INTO Orders VALUES
('4548181814', #2005-07-21#, 'UK');

INSERT INTO Orders VALUES
('7055727553', #2005-07-22#, 'GREECE');

INSERT INTO Orders VALUES
('2300007860', #2005-07-20#, 'GREECE');

INSERT INTO Orders VALUES
('9377223113', #2005-07-20#, 'UK');

SELECT T1.dispatch_country, (
SELECT COUNT(*)
FROM Orders
WHERE T1.dispatch_country = dispatch_country
AND dispatch_date <= T1.dispatch_date
) AS [DESP NO]
FROM Orders AS T1;

 

[Tim Ferguson]

First of all where I have to write your script.
I wrote it to the AfterUpdate event of the country field. But I am
getting error messages.

Yuu need to call it when you are creating a new record: this depends
entirely on your application, work flow analysis etc etc. A few examples:

You could have a switchboard with a button called "Enter new Desp";

You could intercept the BeforeInsert event on the form;

You could intercept the Form Error event when the form attempts to save a
record with an illegal value for [Desp No];

etc.

Hope that helps


Tim F

 

[peregenem]

Yuu need to call it when you are creating a new record: this depends
entirely on your application, work flow analysis etc etc. A few examples:

You could have a switchboard with a button called "Enter new Desp";

You could intercept the BeforeInsert event on the form;

You could intercept the Form Error event when the form attempts to save a
record with an illegal value for [Desp No];

For me this is a calculated column e.g. relative date/time entered
order, so there should be something in the database (e.g. a
entered_date column) to enable the relative order to be calculated on
demand.

 

[Guest]

Dear Tim,
Sorry for the annoyance.
I would like to ask you something else about the code you sent me.

About the sqlWhere criteria - I didnt understand what that criteria means.
I only have two fields on my form Country and DespNo. What is the
txtCountryCode that you wrote in the crieteria sqlWhere?

I dont know why it doesnt work again and I am run out of time I dont know
what to do. Please help me!

First of all where I have to write your script.
I wrote it to the AfterUpdate event of the country field. But I am
getting error messages.

Yuu need to call it when you are creating a new record: this depends
entirely on your application, work flow analysis etc etc. A few examples:

You could have a switchboard with a button called "Enter new Desp";

You could intercept the BeforeInsert event on the form;

You could intercept the Form Error event when the form attempts to save a
record with an illegal value for [Desp No];

etc.

Hope that helps


Tim F

 

[Guest]

Dear John can you please check again your code because everytime I used it I
get errors messages.

My table name is table1, and I have two fields Country and DespNo. Please
check it again if it is possible.

I am run out of time and I dont know what to do. I have to deliver my
project on Monday and I dont know to do.

Please Help Me!

I have a table with the fields COUNTRY and DESP NO.
I want every time a new country is enter in the field COUNTRY the DESP.NO to
be increased by 1.

Example:

COUNTRY DESP.NO
UK 1
GREECE 1
GREECE 2
ITALY 1
UK 2

Use a Form to enter data into the table. In the AfterUpdate event of
the Country control (I'd suggest using a combo box so you don't have
to type out each country name) put code like

Private Sub Country_AfterUpdate()
Me![Desp.No] = NZ(DMax("[Desp.No]", "[tablename]", _
[Country] = '" & Me![Country] & "'"
End Sub

There is no way to do this in a Table or a table's properties.

John W. Vinson[MVP]

 

[Tim Ferguson]

About the sqlWhere criteria - I didnt understand what that criteria
means. I only have two fields on my form Country and DespNo. What is
the txtCountryCode that you wrote in the crieteria sqlWhere?

It was meant to be fairly generalised code. The txtCountryCode refers to
the control (or string, or whatever) that contains the country code you
want to look up. In general, it's a good idea to name controls separately
from the fields they are bound to -- most of the time it doesn't matter
much but there are circumstances that lead to really hard-to-track bugs.

If your text box really is called [Country] then the code would look like

sqlWhere = "Country = """ & Me!Country & """"

but you can see why this starts to get confusing.

All the best


Tim F

 

[John Vinson]

Dear John can you please check again your code because everytime I used it I
get errors messages.

It's considered polite to actually *tell us* the error messages. I
have no trace of an idea what might be wrong with the code, since I
don't know WHAT error you're getting. Remember: you can see your
computer screen. I cannot.

My table name is table1, and I have two fields Country and DespNo. Please
check it again if it is possible.

Just guessing:

Private Sub Country_AfterUpdate()
Me![DespNo] = NZ(DMax("[DespNo]", "[table1]", _
[Country] = '" & Me![Country] & "'"
End Sub

I was using Desp.No because that was what you had in your original
post, and I was using tablename as the name of the table because you
had not mentioned the actual name of your table. Again.... *I cannot
see your screen*, and I cannot read your mind; I was making
suggestions for you to modify, not providing a magical blackbox
solution!

John W. Vinson[MVP]