runtime error 6 overflow 1

[dmhayes]

I have this happening in my access database . i am new to access and have aviheive alot from errors... one more thing the record id which is a autonumber has now given me (335648925) not smaller numbers.


Private Sub cmdPickPart_Click()
Dim strPart As String

strPart = InputBox("Enter a Part Number - i.e., 206-160-253-001")
If strPart <> "" Then
lstParts.RowSource = "SELECT IDRECORD, tblPart.PARTNUMBER, [IDTRACKING], [SERIAL NUMBER], Description FROM tblPartCategory INNER JOIN TBLPART ON tblPartCategory.PartNumber = TBLPART.PARTNUMBER WHERE (((tblPartCategory.QTYONHAND) > 1)) AND tblPart.PARTNUMBER = '" & strPart & "' ORDER BY TBLPART.PARTNUMBER"
'lstParts.Requery
End If
End Sub


Private Sub cmdUsePart_Click()
Dim RecordNo As Integer
Dim strPart As String
Dim strtracking As String
Dim strSerial As String
Dim strDesc As String

RecordNo = lstParts.Column(0) (stops here)
strPart = lstParts.Column(1)
strtracking = lstParts.Column(2)
strSerial = lstParts.Column(3)
strDesc = lstParts.Column(4)

[frmSaleDet2].Form![PartNo] = RecordNo
[frmSaleDet2].Form![PartNumber] = strPart
[frmSaleDet2].Form![IDTRACKING] = strtracking
[frmSaleDet2].Form![Description] = strDesc
[frmSaleDet2].Form![Qty].SetFocus
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub

 

[RoyVidar]

If the number in the first column of the listbox, is the id you refer to, then the overflow is probably related to trying to "stuff" more into an integer, than it accepts.

If you need to use variables, declare the RecordNo as long in stead of integer.

Autonumbers are just a means of identifying a record uniquely, and what number it is, shouldn't have any meaning - and yes - for some reasons they may "jump" a little. One shouldn't even let the users see the autonumber, in my opinion.

Roy-Vidar

 

[dmhayes]

Thank you very much. Is there a way in which to put letters in front of autonumbers or automate that feild better.

regards donna

 

[RoyVidar]

Yes, one can "put letters in front" of an autonumber by using the fields format property, "Test"00000000 would format the field to start with Test, and continue to show numerics in the rest of the positions. But - it is only a format, what is stored, is the number.

This implies you are assigning a business meaning to the autonumber, which isn't exactly encouraged by most Access developers. The usual perspective on Autonumbers are:
[ul][li]use it for referential integrity only (PK - FK)[/li]
[li]don't show it to the user[/li]
[li]don't assign any business meaning to it[/li][/ul]
Some of the reasons for this, is that
[ul][li]when cancelling a record you've started editing - the number is lost, it will give you the next number in the sequence[/li]
[li]when deleting a record - the number is lost, you can't use it again[/li]
[li]sometimes it "jumps" to a significantly higher number without the developer being able to figure out why (well - it sometimes can be related to doing mass insert/deletes, or users hitting cancel lots of times when creating new records, but I'm inclined to believe some of the "jumps" are due to something not necessarily related to user activity)[/li][/ul]
Which again points back to - do not assign any business meaning to a field of type autonumber. As long as you only use it for identifying the record, and in relationship between this table and child tables, and don't even show it to the users, it should be OK, and it doesn't matte what number Access is assigning.

If you really need a sequental number, you've got several possibilities.

One is keepig the autonumber field, and have another field where you maintain your sequence. It should probably be indexed uniquely.

Or you could go in another direction. Add another field as primary key, numeric if only numbers are interesint, or text, if you need also letters in front. For single user apps, the approach of using for instance the DMax domain aggregate function in a relevant form event, should be sufficient. Sometimes it will also suffice in a multiuser setup, provided the retrieval of the next number is performed in the before update event of the form, but it may sooner or later give a "duplicate primary key" situation.

In a multiuser setup, something like what MichaelRed presents in this faq faq700-184, is something to be considered.

Lastly, if you do a search here in the Access fora (there's seven of them), you'll find numerous threads dealing with these issues, giving lot of different advice. MichaelRed's faq is mentioned in a lot of them, so is also the DMax thingie. I would suggest a read of returned search results, use some time to figure out the pros and cons of different approaches to sequental numbering, autonumbers... then deside what approach best fit your requirements.

Roy-Vidar