increase number

[Guest]

Hi,

I have a table where we key-in Delivery notes for customers; in my form I
use the following : =Nz(DMax("[dnnumber]","delivery note table"),0)+1 to have
continuous DN number, and its working fine.
At the end of the day, our remote office e-mails me a list of DN that we
have to key-in manually. I tried to create an append query to add these
records and have the dnnumber increase automatically, it didn't work. The
query is giving the last dnnumber +1 value to all the appended records.
Any suggestions, knowing that I am not very familiar with VBA .

Thanks in advance

 

[Michel Walsh]

Hi,


Use a function like:


--------------------------
Public Function Increment(Optional Dummy As Variant) As Long
Static i
If (IsMissing(Dummy)) Then
i = DMax("yourField", "yourTable") ' use your real names
Else
i = i + 1
End If

Increment = i
End Function
-------------------------


and, before running the query, run:


Increment()


which put the max value in the static variable, then, run the Update query
like:


---------------------------------
INSERT INTO Archive ( f1, f2 )
SELECT Increment(f1) AS Expr1, WorkingTable.f2
FROM WorkingTable;
---------------------------------




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi Micheal,

Thanks for your reply, emmm... I am a little new to these things, can you
show me how to do it please if possible.

Thanks .

Sam.

Hi,


Use a function like:


--------------------------
Public Function Increment(Optional Dummy As Variant) As Long
Static i
If (IsMissing(Dummy)) Then
i = DMax("yourField", "yourTable") ' use your real names
Else
i = i + 1
End If

Increment = i
End Function
-------------------------


and, before running the query, run:


Increment()


which put the max value in the static variable, then, run the Update query
like:


---------------------------------
INSERT INTO Archive ( f1, f2 )
SELECT Increment(f1) AS Expr1, WorkingTable.f2
FROM WorkingTable;
---------------------------------




Hoping it may help,
Vanderghast, Access MVP

Hi,

I have a table where we key-in Delivery notes for customers; in my form I
use the following : =Nz(DMax("[dnnumber]","delivery note table"),0)+1 to
have
continuous DN number, and its working fine.
At the end of the day, our remote office e-mails me a list of DN that we
have to key-in manually. I tried to create an append query to add these
records and have the dnnumber increase automatically, it didn't work. The
query is giving the last dnnumber +1 value to all the appended records.
Any suggestions, knowing that I am not very familiar with VBA .

Thanks in advance

 

[Michel Walsh]

Hi,


The function goes into a standard module (NOT in a class, neither under a
Form or under a report).

The query is as usual. Here, I typed it as it could be seen under an 'SQL
view'.


Is there any specific detail you have a problem with?


Hoping it may help,
Vanderghast, Access MVP

Hi Micheal,

Thanks for your reply, emmm... I am a little new to these things, can you
show me how to do it please if possible.

Thanks .

Sam.

Hi,


Use a function like:


--------------------------
Public Function Increment(Optional Dummy As Variant) As Long
Static i
If (IsMissing(Dummy)) Then
i = DMax("yourField", "yourTable") ' use your real names
Else
i = i + 1
End If

Increment = i
End Function
-------------------------


and, before running the query, run:


Increment()


which put the max value in the static variable, then, run the Update
query
like:


---------------------------------
INSERT INTO Archive ( f1, f2 )
SELECT Increment(f1) AS Expr1, WorkingTable.f2
FROM WorkingTable;
---------------------------------




Hoping it may help,
Vanderghast, Access MVP

Hi,

I have a table where we key-in Delivery notes for customers; in my form
I
use the following : =Nz(DMax("[dnnumber]","delivery note table"),0)+1
to
have
continuous DN number, and its working fine.
At the end of the day, our remote office e-mails me a list of DN that
we
have to key-in manually. I tried to create an append query to add these
records and have the dnnumber increase automatically, it didn't work.
The
query is giving the last dnnumber +1 value to all the appended records.
Any suggestions, knowing that I am not very familiar with VBA .

Thanks in advance