Function

  • Thread starter Thread starter Rolando São Marcos
  • Start date Start date
R

Rolando São Marcos

I have a form to register partners, and i want the number of the partner i
am registering in the data base to be automatically inputed. This number
will be tha last number generated plus 1, gives the present number of the
partner i am introducing.

How can I use the function DMAX to preform this action?

Thanks
 
You will have difficulties to use the DMAX function for this because
SQL-Server is a multi-process system; so unless you are only one single
person accessing your database, there is a possibility of having two
processes trying to increase the number as the same, which will give you
duplicates. And if a process abort, you can find yourself with holes in
your sequence.

The easiest way that you are searching is probably to use the DMAX value in
the BeforeInsert event. The new value will be entered as soon as you try to
enter a value in any other field of your form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.IdTable = DMax("IdTable", "dbo.MyTable") + 1
End Sub

However, I wouldn't use this method in a professional application. One
another possibility would be to use a stored procedure on the SQL-Server to
create the new record and increase the number; something like:

CREATE PROCEDURE dbo.Insertion_MaxID
as
INSERT INTO Table_for_Insertion_MaxID (Id, T)
SELECT COALESCE (MAX (Id), 0) + 1, 'Hello!'
FROM Table_for_Insertion_MaxID With (UPDLOCK)
GO

If there is an identity column in your table, then you can use the
@@identity to return its value for the latest inserted record in the current
transaction. If you want to have more information about stored procedure,
please make a visit to the microsoft.public.sqlserver.programming newsgroup.
 
Rolando,
Parece que posso escrever nesta língua. será? lol
Tenho esta instrução para preencher um campo de nº de informação.

Private Sub Form_Current()
Dim varNUM, varZeros, mask, VarAno As String
Dim StringAno, str
Dim Mystr As String
Dim Cancel As Integer
VarAno = Year(Now())

If NewRecord Then

varNUM = DMax("NINFORM", "infor", "[ano]=" & VarAno) 'É a instrução que
procuras
If IsNull(varNUM) Then 'Serve para iniciar a variável em cada ano
varNUM = 0
End If
NINFORM = varNUM + 1
varZeros = Len([NINFORM]) 'Vai servir para colocar zeros antes do nº da
informação
Select Case varZeros
Case 1
mask = "000"
Case 2
mask = "00"
Case 3
mask = "0"
End Select

NUM_ANO = VarAno & "/" & mask & NINFORM 'Preenche o campo num_ano com o
valor para formar ano/nºinformação
 
Back
Top