Overflow problem Access 2010 and SQL

G1ZmO

Registered User.
Local time
Today, 17:22
Joined
May 4, 2006
Messages
133
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
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:
You are using Integer as your data type - change it to Long

Dim NewItem As Long
 
Thanks Minty. :)
That worked, but why?
I've always used Integer when referencing ID fields.
 
Integer values in Access are only able to hold values between +32,768 to - 32,767 because they are only two-bytes - This list from our friends at MS explains all;
Code:
Number

Select one of the following:

Byte — For integers that range from 0 to 255. Storage requirement is a single byte.

Integer — For integers that range from -32,768 to +32,767. Storage requirement is two bytes.

Long Integer — For integers that range from -2,147,483,648 to +2,147,483,647. Storage requirement is four bytes.

[COLOR="Red"]TIP: Use the Long Integer data type when you create a foreign key to relate a field to another table's AutoNumber primary key field.[/COLOR]

Single — For numeric floating point values that range from -3.4 x 1038 to +3.4 x 1038 and up to seven significant digits. Storage requirement is four bytes.

Double — For numeric floating point values that range from -1.797 x 10308 to +1.797 x 10308 and up to 15 significant digits. Storage requirement is eight bytes.

Replication ID — For storing a globally unique identifier that is required for replication. Storage requirement is 16 bytes. Note that replication is not supported using the .accdb file format.

Decimal — For numeric values that range from -9.999... x 1027 to +9.999... x 1027. Storage requirement is 12 bytes.

The red bit is pretty important ;)
 
Ahhhh
Ok, this gives me a solution to an existing problem also
Brilliant, Thanks Minty
 

Users who are viewing this thread

Back
Top Bottom