Number Problems

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hello

I have a serious problem

I have Command table with these fields: CommandID:
Autonumber and CommandNumber: C+Year+Month+CommandID

When I add my command to the DB I use this code:

Set rstCommande = New ADODB.Recordset
rstCommande.CursorType = adOpenKeyset
rstCommande.LockType = adLockOptimistic
rstCommande.Open "Tb_Command", cnn1, , , adCmdTable

rstCommande.AddNew
rstCommande!Description = "Import File to DB"
rstCommande!CommandeDate = Date
rstCommande!ClientID = 2
rstCommande.Update

but I still have the CommandNumber to add:
So I use this code:

sqlCommand = "SELECT Tb_Command.CommandID, Max(CommandID)
AS MaxCommandID FROM Tb_Command"
Set rsCommandID = dbs.OpenRecordset(sqlCommand)

iCommandID = rsCommandID![MaxCommandID] + 1

sNumCommand = "C" & Year(Date) & Month(Date) & iCommandID

rstCommand!NumberCommand = sNumCommand
rstCommand.Update
rstCommand.Close

But the error is in this line: iCommandID = rsCommandID!
[MaxCommandID] + 1, for the first record I add to the
table Tb_Command, it's not able to read MaxCommandID,
it's giving Null Always, I tried to copy and Paste my
query into the Query Generator, and the result is the
last CommandID, no NULL value

Any suggestions please
 
Try removing the Tb_Command.CommandID from the query.

sqlCommand = "SELECT Max(CommandID)
AS MaxCommandID FROM Tb_Command"
 
Hi

I try it and it's the same error?

any suggestions
-----Original Message-----
Try removing the Tb_Command.CommandID from the query.

sqlCommand = "SELECT Max(CommandID)
AS MaxCommandID FROM Tb_Command"


Hello

I have a serious problem

I have Command table with these fields: CommandID:
Autonumber and CommandNumber: C+Year+Month+CommandID

When I add my command to the DB I use this code:

Set rstCommande = New ADODB.Recordset
rstCommande.CursorType = adOpenKeyset
rstCommande.LockType = adLockOptimistic
rstCommande.Open "Tb_Command", cnn1, , , adCmdTable

rstCommande.AddNew
rstCommande!Description = "Import File to DB"
rstCommande!CommandeDate = Date
rstCommande!ClientID = 2
rstCommande.Update

but I still have the CommandNumber to add:
So I use this code:

sqlCommand = "SELECT Tb_Command.CommandID, Max (CommandID)
AS MaxCommandID FROM Tb_Command"
Set rsCommandID = dbs.OpenRecordset(sqlCommand)

iCommandID = rsCommandID![MaxCommandID] + 1

sNumCommand = "C" & Year(Date) & Month(Date) & iCommandID

rstCommand!NumberCommand = sNumCommand
rstCommand.Update
rstCommand.Close

But the error is in this line: iCommandID = rsCommandID!
[MaxCommandID] + 1, for the first record I add to the
table Tb_Command, it's not able to read MaxCommandID,
it's giving Null Always, I tried to copy and Paste my
query into the Query Generator, and the result is the
last CommandID, no NULL value

Any suggestions please
Click to expand...


.
Click to expand...
 
Back
Top