Find the last number of a record

[NV]

I've a table called GReg with the fields GNumber, ControlNumber,
ArticleCode, ProdNumber and Date.

In a form I select the ArticleCode from one combo box, and I define the
number of records to create.

Then for each record to be created I need to find the last
ControlNumber used within the selected GNumber (one GNumber may
correspond to several ArticleCode), and increment it by one. Is this
possible ?

Thank you all in advance

Nuno

[BruceM]

Because you say that one GNumber may correspond to several Article Code (and
for other reasons), it sounds like you should have more than one table, but
without knowing anything about the real-world situation to which your
database applies it is difficult to tell. Especially puzzling is your
saying you define the number of records to create.
Having said that, here is a sample database with a way of automatically
incrementing a number:
http://www.rogersaccesslibrary.com/d...berProblem.mdb

"NV" <nu*********@potain.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

I've a table called GReg with the fields GNumber, ControlNumber,
ArticleCode, ProdNumber and Date.

In a form I select the ArticleCode from one combo box, and I define the
number of records to create.

Then for each record to be created I need to find the last
ControlNumber used within the selected GNumber (one GNumber may
correspond to several ArticleCode), and increment it by one. Is this
possible ?

Thank you all in advance

Nuno

[NV]

This is the code I'm using :

strSQL = _
"INSERT INTO RegistoGamas(NumeroGama, NumeroControlo,
CodigoArtigo) " & _
"VALUES('" & Me.NumeroGama & "', #, '" & Me.CodigoArtigo & "')"
lngLastUsedID = Nz(DMax("[NumeroControlo]", "[RegistoGamas]",
"[NumeroGama]=" &_ [txtNumeroGama]), 0)
Set db = CurrentDb
For lngID = (lngLastUsedID + 1) To (lngLastUsedID + lngAddCount)

db.Execute Replace(strSQL, "#", Str(lngID)), dbFailOnError
Next lngID

and it works fine if I use always the same ArticleCode, if I chose
other ArticleCode it will change the ControlNumber of previous records
and will mess it all up

I've looked and looked again but I can't guess what's wrong with this
code

[BruceM]

You asked if it is possible to increment by one. I suggested a solution (by
means of the link). Now I learn that you already had code, but it is not
working. Had you mentioned the code in the first place I would not have
responded, as I do not quite understand what you are doing.
Anybody attempting to respond is probably unfamiliar with your database, so
may not know what to make of your remarks about ArticleCode and
ControlNumber. If these are CodigoArtigo and NumeroControlo, it would be
best to just say so and not leave it to potential responders to translate.
I recall that there were a few details in your original post, which brings
me to another point, which is that you should include the text of previous
messages in you reply so that it is not necessary to flip back and forth
between messages.

"NV" <nu*********@potain.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

This is the code I'm using :

strSQL = _
"INSERT INTO RegistoGamas(NumeroGama, NumeroControlo,
CodigoArtigo) " & _
"VALUES('" & Me.NumeroGama & "', #, '" & Me.CodigoArtigo & "')"
lngLastUsedID = Nz(DMax("[NumeroControlo]", "[RegistoGamas]",
"[NumeroGama]=" &_ [txtNumeroGama]), 0)
Set db = CurrentDb
For lngID = (lngLastUsedID + 1) To (lngLastUsedID + lngAddCount)

db.Execute Replace(strSQL, "#", Str(lngID)), dbFailOnError
Next lngID

and it works fine if I use always the same ArticleCode, if I chose
other ArticleCode it will change the ControlNumber of previous records
and will mess it all up

I've looked and looked again but I can't guess what's wrong with this
code