I have hit a barrier while using access as a front end to my database in that there are now more than 32768 records in my ITEMS table
Most of the time I can get around this issue however I'm INSERTing into the SQL table via a button on an Access form which works ok but I then need to get the ID of the item I have inserted. I'm doing this with a DLOOKUP but getting an Overflow error.
I tried limiting the records by creating a query which only pulls the most recent records but again I get the Overflow error.
Could anyone suggest how I could get the Item.ID as I'm pulling my limited hair out?
FWIW here's the VBA
Thanks
Most of the time I can get around this issue however I'm INSERTing into the SQL table via a button on an Access form which works ok but I then need to get the ID of the item I have inserted. I'm doing this with a DLOOKUP but getting an Overflow error.
I tried limiting the records by creating a query which only pulls the most recent records but again I get the Overflow error.
Could anyone suggest how I could get the Item.ID as I'm pulling my limited hair out?

FWIW here's the VBA
Code:
Private Sub cmdAddCS_Click()
Dim strSQL1 As String
Dim NewItem As Integer
'' Insert an item into the Item table
strSQL1 = "INSERT INTO ITEM (JobID, PalletID, MakeAndModelID, StockStatusID, IsPicked, OrderID) VALUES (" & Me.cboCSjob & ", 35310," & Me.cboCS & ",1,-1," & Me.OrderID & ")"
CurrentDb.Execute strSQL1, dbSeeChanges
'' Use a lookup to find the ID of the newly inserted line in the item table
NewItem = DLookup("ID", "Q_ChargeableServicesItems", "OrderID =" & Me.OrderID)
'' Overflow error occurs
MsgBox NewItem
End Sub
Thanks
Last edited: