Results 1 to 6 of 6
  1. #1
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145

    Code to replace deleted record

    Hi Forum,



    I am trying to find a way to rewrite my code in the event that one of my record gets deleted, can the next New Record I create replace that CPCNumber... For example: I have records of CPCNumbering 1-5; I delete CPCNumber 4; the next new record will replace CPCNumber 4.


    CPC Numbers are my sequential numbering system for each record created. My code writes as follows:

    If Me.NewRecord = True Then
    Dim CPCNumber As String
    Me.CPCNumber = Nz(DMax("[CPCNumber]", "tblConcretePour"), 0) + 1
    End If

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If CPCNumber (4) is an autonumber, then no, you cant.

    Even if it is not autonum, you cannot use DMAX, because if 4 is in the middle, and dmax = 9 ,then you wont find 4.

    If you just use AUTONUM , you dont have to worry about numbering,
    you dont have to program anything,
    just accept the next # given.


  3. #3
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    It is not a AUTONumber, just a NUMBER

    Is there a code where it can find 4 first then DMAX to 9? The problem with auto number would be the exact same thing. I don't want any gaps in between my numbers. If i delete number 4 out of 1-5; Number 4 is lost.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If the # is lost, then move to the next one. There are plenty. 2 to 9th power and change.

    The code must search 1 by 1 to find the missing, wasting time.
    Just grab a new #. The system doesnt care.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do not allow records to be deleted.

    I have a db which creates sequential unique identifier. I cannot allow gaps as these identifiers must be accounted for. Once a DocID is created the record must remain even if user decides they do not want to continue with data entry. I have an 'abort' procedure. This procedure deletes all data input except the DocID. Next time a document is logged in user will get this existing 'blank' record to input data into. The record is easily located by searching for record with a Null date field. I also have a 'void' procedure.

    'Finding' your number gap is tricky, not impossible; however, agree with ranman256 and do not recommend implementing.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Thanks guy! Appreciate the feedback.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 07-23-2017, 02:04 PM
  2. Record is Deleted
    By Middlemarch in forum Access
    Replies: 3
    Last Post: 07-28-2016, 06:50 AM
  3. Replies: 4
    Last Post: 04-20-2015, 12:36 PM
  4. Why am I still being prompted after I deleted the code?
    By Access_Novice in forum Programming
    Replies: 5
    Last Post: 01-06-2014, 07:28 PM
  5. record deleted
    By akram in forum Access
    Replies: 12
    Last Post: 03-11-2011, 09:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums