how to avoid generating sequential ID with before update event? (1 Viewer)

Alhakeem1977 · May 20, 2019

Hi All,

How to avoid generating sequential ID if before update event took place?
below is my codes in the Before update event and Save button:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strLinkCriteria As String
Dim strMessage As String

  If Len(Trim(Me!cboRecDept & vbNullString)) = 0 Then
    strMessage = strMessage & " You must provide the Receiver's Department!" & vbCrLf
    Me.cboRecDept.SetFocus
  End If
   
  If Len(Trim(Me!cboDescription & vbNullString)) = 0 Then
    strMessage = strMessage & " You must provide the Description!" & vbCrLf
    Me.cboDescription.SetFocus
  End If
    
  If Len(strMessage) > 0 Then
    MsgBox strMessage, vbCritical
    
    Cancel = True
     Me.Undo
      
   Else

Me.cmdSave.SetFocus

    End If
  
Cleanup:
  Exit Sub
ErrorHandler:
   MsgBox Err.Number & ": " & Err.Description
  Beep
    MsgBox "Sorry, there was an error occurred, please revert your record !.", vbOKOnly, "Error"

  Resume Cleanup

End Sub

In the save button:

Code:

Private Sub cmdSave_Click()
On Error GoTo ErrorHandler
   
       Me![PKDes] = Nz(DMax("[PKDes]", "tblDeptReg"), 0) + 1
       Me![Seq] = Nz(DMax("[Seq]", "tblDeptReg", "Year([SentOn]) = " & Year(Me.[SentOn]) & " AND " & _
       "[SentDept] = " & Me.txtDeptID & ""), 0) + 1
       Me.Ref = [txtDeptShName] & Format([SentOn], "yy") & Format([Seq], "0000")
DoCmd.RunCommand acCmdSave
Me.cmdSave.Enabled = False
Me.txtNo.Requery
Beep
Me.cmdNew.SetFocus
    
  
Cleanup:
  Exit Sub
ErrorHandler:
   MsgBox Err.Number & ": " & Err.Description
  Beep
    MsgBox "Sorry, there was an error occurred, please revert your record !.", vbOKOnly, "Error"

  Resume Cleanup

End Sub

I did my best to avoid getting the sequential Id [Seq] if a condition in the before update event took place.
By the way, the database is split (Multi-users environment) many users at the same time could use the data entry form, the sequential ID is the most important field in my project.

Thanks a lot in advance!

theDBguy · May 20, 2019

...the sequential ID is the most important field in my project.

Hi. Just curious, why is that? Consistently creating a sequential value in a multi-user environment is a little bit of a juggling act and not a simple thing to do.

Micron · May 20, 2019

Step thru your code and watch the states of variables and such as you go. Some things look suspicious:
- strMessage = strMessage infers that strMessage already holds something, thus its length would never be zero, thus no cancel; no undo
- what drives form Update? Could be just leaving the record to click the button, thus click event may not work as expected (depends on form design)
- call to save record could call form update, but if record change is cancelled, why requery txtNo?
Hopefully you can see why it's important to step through.

How to avoid generating sequential ID

If it's based on autonumber (I didn't see where you identified the method) you cannot with this method. Cancel the record, go to another and the "next" number is gone. IMHO, there is are only 1 way to have any chance of sequential autonumbers
- use a table for temp record creation, then copy only finished record to main table and delete the "working" record from the temp. Even then, only (maybe) 94.789% guarantee. Autonumbers can be haphazard.

Often, sequential numbering isn't as important as people make it out to be. I once worked at a place where they were concerned about it for PO's. I tried to convince her that it wasn't important if they knew the "missing" PO# was cancelled for whatever reason. It was still traceable. No dice there.

gemma-the-husky · May 21, 2019

Is your form bound or unbound.

You have a save button. If it's bound then what happens if the user closes the form without clicking your save button. Could that be happening?

Minty · May 21, 2019

This is so true;

Micron said:
I once worked at a place where they were concerned about it for PO's. I tried to convince her that it wasn't important if they knew the "missing" PO# was cancelled for whatever reason. It was still traceable. No dice there.

I see so many posts about this. I appreciate that sometimes say invoice numbers etc. should run concurrently, but often "It's the law!" isn't actually true, and provided you can show that "missing" numbers are actually cancelled or don't exist, you are fine.

Alhakeem1977 · May 21, 2019

gemma-the-husky said:
Is your form bound or unbound.

You have a save button. If it's bound then what happens if the user closes the form without clicking your save button. Could that be happening?

Thanks for your response it's a bound form, how can U amend my code to achive generating the sequential ID?

Thanks a lot in advance!

Sent from my HUAWEI NXT-L29 using Tapatalk

CJ_London · May 21, 2019

this link provides a good example which handles the problems you will get in a multi user environment

http://rogersaccessblog.blogspot.com/2010/12/domain-function-example-simulate.html

gemma-the-husky · May 21, 2019

Actually, on reflection, I don't understand what you are doing. Are you ttrying to ONLY save the record IF you click the save button. If so, I think you need to use an unbound form.

There are many ways to save a record - in fact it's actually pretty hard to prevent a record saving. Among others, you can save the record by closing the from, or moving to a new record, or clicking the pencil in the record selector, or choosing save record from one of the menus.

I would not have the save button. I would just put the reference lookups in the before_update event. (assuming the reference lookups do work correctly).

I would also add code to only do the lookups witihn the before update if you are on a new record, othgerwise you weill change the lokkups if you edit an existing record

Code:

if me.newrecord then
   set the lookups
end if

Minty · May 21, 2019

In a multi user environment as theDbGuy and Micron stated this is not actually very straightforward.

In fact unless you absolutely, positively, definitely HAVE to have it it's a real pain in the backside.

gemma-the-husky · May 21, 2019

If multiple users are all trying to get "next" references at the same time, it's an issue. You have to prevent 2 users getting the same reference.

Access gives you the autonumber immediately it inserts the new record. Consequently a second user will then get the next autonumber. If you then scrap yorr entry, your autonumber is lost. This is "safe", but explains why you can't rely on an autonumber to maintain an intact sequence.

now if you do the same thing manually - set the next sequence number using dmax technoiogy, at an early stage in the edit process, you get the opposite problem. A second user is quite likely to retrieve the same "next" number that you did, which you don't want.

So if you want to get the "next" number using dmax, you MUST do this at the latest possible moment, whioch is why it belongs in the before update event, AFTER all validity tests have succceded.

If you want to be really anal, you get the next number from a next number table that you lock before reading and writing, to categorically ensure you are the only user with that number. It IS barely possible that 2 users could get the same "next" number by using dmax if they happened to do the dmax at the same micro second - but unlikely in practice.

On further thought, maybe your issue was that you were re-setting the references when you were editing an existing record - and you can avoid that by testing for "new record".

Micron · May 21, 2019

Taken together, many of the responses reflect the reasons why I like the temp table approach for this. NOTE - I do not mean repeatedly creating/deleting some table when I use that term. With the temp method

- user action to create a new record sets the mode (pass it via OpenArgs). Any default values go right into the temp table (tt). Code can validate fields based on mode if req'd. User cancels, tt gets wiped, no number is calculated - end of story.

- user needs to edit; mode is now "edit", some fields can be locked if req'd (certainly lock the sequence number); user edits and saves; update query runs. No number is calculated whether or not user saves.

- if user saves record from 1st scenario, DMax of sequential number field gets next sequential number. There is virtually zero chance that 2 users would do this at the exact same micro-second. It stands to reason that regardless of how close they are, one of them would commit the number before the other can DMax it. In this design, the form SHOULD have combined buttons to facilitate the process; i.e. Save/Close and Cancel/Close. You don't want to leave the form open after a save or cancel as the logic gets too complex trying to figure out what mode they're in then, how the process might go from there and changing buttons to reflect that a save is no longer possible. If necessary to leave form open for some reason then save/close capability needs to be suppressed and only close provided. Much simpler to just close and do your reviewing before a save.

Alhakeem1977 · May 21, 2019

CJ_London said:
this link provides a good example which handles the problems you will get in a multi user environment

http://rogersaccessblog.blogspot.com/2010/12/domain-function-example-simulate.html

I have tried this function it did not solve the issue.

Sent from my HUAWEI NXT-L29 using Tapatalk

how to avoid generating sequential ID with before update event? (1 Viewer)

Alhakeem1977

Registered User.

theDBguy

I’m here to help

Micron

AWF VIP

gemma-the-husky

Super Moderator

Minty

AWF VIP

Alhakeem1977

Registered User.

CJ_London

Super Moderator

gemma-the-husky

Super Moderator

Minty

AWF VIP

gemma-the-husky

Super Moderator

Micron

AWF VIP

Alhakeem1977

Registered User.

Similar threads

Users who are viewing this thread