Difference in Last Statement

[Guest]

I’m working on a form that will add a new record and will assign a new ID
number to the primary key. I’m using a sql insert into and that work fine,
but when I use a go to last record it’s not the same, why?

strSQL = "INSERT INTO tblEquipment ( EquipmentID ) " & _
"SELECT Last([EquipmentID]+1) " & _
"FROM tblEquipment;"
DoCmd.RunSQL strSQL

DoCmd.GoToRecord , , acLast

Any ideas on this

 

[Guest]

1. Try to refresh the form before you move to the last record
2. Use Max instead of Last to get the last Count
3. the records in the form need to be sorted by the EquipmentID field

Something like

strSQL = "INSERT INTO tblEquipment ( EquipmentID ) " & _
"SELECT Max([EquipmentID])+1 " & _
"FROM tblEquipment;"
DoCmd.RunSQL strSQL
Me.Requery
DoCmd.GoToRecord , , acLast

 

[Guest]

No luck. Refreshing the form does not work. I can’t use Max because it not
always going to be the max number.
And I'm also going to need to check for duplicated ID numbers.

--
thank You

1. Try to refresh the form before you move to the last record
2. Use Max instead of Last to get the last Count
3. the records in the form need to be sorted by the EquipmentID field

Something like

strSQL = "INSERT INTO tblEquipment ( EquipmentID ) " & _
"SELECT Max([EquipmentID])+1 " & _
"FROM tblEquipment;"
DoCmd.RunSQL strSQL
Me.Requery
DoCmd.GoToRecord , , acLast


--
Good Luck
BS"D

I’m working on a form that will add a new record and will assign a new ID
number to the primary key. I’m using a sql insert into and that work fine,
but when I use a go to last record it’s not the same, why?

strSQL = "INSERT INTO tblEquipment ( EquipmentID ) " & _
"SELECT Last([EquipmentID]+1) " & _
"FROM tblEquipment;"
DoCmd.RunSQL strSQL

DoCmd.GoToRecord , , acLast

Any ideas on this