Add new record problem... 1

[atmospherik]

If anyone could advise I'd be very grateful -

I've just replaced an autonumber field with one that uses a script to increment and keep the field [called ID_ItemAuto in the code below] sequential with no gaps (code is below) and it seems to have sorted out the missing autonumbers problem ok.

Unfortunately, I now have another problem - when I add a new record, shut the db down and reopen the form, the record has been added to the *start* of the form ie. Record 1 in the navigate records box - this makes it messy to navigate - does anyone have any ideas why this might be??? I'm really struggling to work this one out. BTW the new record still gets added to the *end* of the underlying table.

ie: before adding record....

Record num: 1 Item_IDAuto: 1
Record num: 2 Item_IDAuto: 2
Record num: 3 Item_IDAuto: 3
Record num: 4 Item_IDAuto: 4
Record num: 5 Item_IDAuto: 5
...etc

after adding the new record....
Record num: 1 Item_IDAuto: 6
Record num: 2 Item_IDAuto: 1
Record num: 3 Item_IDAuto: 2
Record num: 4 Item_IDAuto: 3
Record num: 5 Item_IDAuto: 4
Record num: 6 Item_IDAuto: 5
...etc


Would be very appreciative of any help on this.

Script for providing the sequential Item_IDAuto field:

Function NewItemIDAutoNbr() As Long

Dim db As Database
Dim LSQL As String
Dim LUpdate As String
Dim Lrs As DAO.Recordset
Dim LNewItemIDAutoNbr As Long

On Error GoTo Err_Execute

Set db = CurrentDb()

'Retrieve last number assigned for Journal Number
LSQL = "Select Last_Nbr_Assigned from Codes"
LSQL = LSQL & " where Code_Desc = 'Journal Number'"

Set Lrs = db.OpenRecordset(LSQL)

'If no records were found, return an error
If Lrs.EOF = True Then
LNewItemIDAutoNbr = 0
MsgBox "There was no entry found in the Codes table for Journal Number."

Else
'Determine new Journal Number
LNewItemIDAutoNbr = Lrs("Last_Nbr_Assigned") + 1

'Increment Journal Number in Codes table by 1
LUpdate = "Update Codes"
LUpdate = LUpdate & " set Last_Nbr_Assigned = " & LNewItemIDAutoNbr
LUpdate = LUpdate & " where Code_Desc = 'Journal Number'"

db.Execute LUpdate, dbFailOnError

End If

Lrs.Close
Set Lrs = Nothing
Set db = Nothing

NewItemIDAutoNbr = LNewItemIDAutoNbr

Exit Function

Err_Execute:
'An error occurred, return 0
NewJournalNbr = 0
MsgBox "An error occurred while trying to determine the next Journal Number to assign."

End Function

 

[PHV]

What is the RecordSource of the form ?
Hopefully a query you can modify adding an ORDER BY Item_IDAuto clause ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[atmospherik]

Forgive my ignorance (taught myself Access..) but it's not from a query but was created using the form wizard and grabbing the table fields.

I've also just realised that it looks like the original autonumber (ItemID) was keeping the ordering - could it be that now I've changed the primary key to Item_IDAuto the form doesn't know how to keep the ordering??

 

[PHV]

When in design view open the form's properties sheet, in the Data tab populate the Order by cell.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[atmospherik]

****Thanks**** PH.

That was the problem! I'll remember that in future.

Thanks for replying so quickly aswell... you've saved me from a very late night.

Paul