If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filling in voids in the id number
My first column is the key and ID number for each record. One of my users
(before I locked the table) voided several of the records, and now the record number and Id number no longer match. Is there a way to back fill the key-id number with dummy records so the rows line up. Thanks…… |
#2
|
|||
|
|||
Filling in voids in the id number
hi Tes,
On 11.02.2010 21:33, TES wrote: My first column is the key and ID number for each record. One of my users (before I locked the table) voided several of the records, and now the record number and Id number no longer match. Is there a way to back fill the key-id number with dummy records so the rows line up. If you need a continuous number without gaps, then you must calculated it before storing the records. You cannot use an auto number for that purpose. Use Nz(DMax("number", "tableName"), 0) + 1 to calculate it. E.g. in the before insert event of a form: Private Sub Form_BeforeInsert(Cancel As Integer) Me![number] = Nz(DMax("number", "tableName"), 0) + 1 End Sub mfG -- stefan -- |
#3
|
|||
|
|||
Filling in voids in the id number
If that field is an autonumber you can Compact & Repair, close Access, open
Access, and append missing records. -- Build a little, test a little. "TES" wrote: My first column is the key and ID number for each record. One of my users (before I locked the table) voided several of the records, and now the record number and Id number no longer match. Is there a way to back fill the key-id number with dummy records so the rows line up. Thanks…… |
#4
|
|||
|
|||
Filling in voids in the id number
On Thu, 11 Feb 2010 12:33:01 -0800, TES wrote:
My first column is the key and ID number for each record. One of my users (before I locked the table) voided several of the records, and now the record number and Id number no longer match. Is there a way to back fill the key-id number with dummy records so the rows line up. Thanks…… Ummmm... You don't want to do that. An Autonumber has one purpose, and one purpose ONLY: to provide a meaningless unique identifier. They are *NOT* "record numbers". They will always have gaps; not only will deleting a record leave a permanent gap, so will starting to enter a record and then hitting ESC twice to cancel the entry. If you want a gapless, sequential number, don't use an Autonumber field at all; use a "custom counter" (a long integer field with VBA code to assign the value). But think about it: do you really want meaningless, dummy records with no data inserted into your table, just to keep the ID sequential? What benefit does it serve to do so? -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Filling in voids in the id number
In addition to advice offered else-thread, please be aware that the number
in the bottom left (I believe this is what you mean by "record number") is NOT a record number, it's merely a count of records. If you change the sort order, you'll still have the same number of records, but what you THOUGHT was #1 could be #19 or #99 or #12345. Does this mean you are trying to do this directly in the table? If so, STOP NOW! Table store data. Forms display data. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "TES" wrote in message ... My first column is the key and ID number for each record. One of my users (before I locked the table) voided several of the records, and now the record number and Id number no longer match. Is there a way to back fill the key-id number with dummy records so the rows line up. Thanks.. |
Thread Tools | |
Display Modes | |
|
|