Possible duplicate issue

[MrMitch]

I have a form that users fill out for data entry, and I need the form to show the users an ID# for their reference. I have it set up now so that this number is generated on the forms "before insert" command, and then displays for them on the form. However, it is possible that if 2 users are working on the form at the same time, that it can duplicate this ID since the dmax command is looking at the last entry, and since the numbering system restarts every year, I must allow duplicates in that field. The number is generated by 2 fields (year, and ID) to generate the ID's as 2007-0001, 2007-0002, etc.

What would be the best way to resolve this?

I was thinking maybe, if there was a way to generate the ID after I have them submit the data on a seperate form that I could have pop up for them, but I am unsure how to do this. Is there a way to reference the records they just submitted somehow?

If more details are needed to help answer this, let me know. Thanks!

 

[MrMitch]

I prefer to put the sequence number generation code in the BeforeUpdate event because that puts it closer to the record save and therefore minimizes the potential for duplicates but does not eliminate the issue.

This is exactly what I would like to do, however, my issue would be that I need the person entering the data to be able to see this number for their reference as well. Is there a way to show them this number through a different form or something that could be triggered though the button I have them select to save the record?

 

[GAB]

one possible thing you might try that worked for me is to to make a macro that causes the form to refresh and in the first field your user fills out in the properties event tab in the on exit box enter the name of that macro. This will cause the form to refresh when they tab to the next field and lock in the number to that form. The next user should get the next number in sequence.

Gary

 

[MrMitch]

Gary, that is a good idea. However, I need some of the fields that are farther down on that form to have input in them, and the refresh causes a problem there.

 

[GAB]

Is the input on the other fields automatically filled when you open a new record? Even so when you refresh the form it shouldn't change the content. You've got me a little confused can you comment further on that please.

Gary

 

[MrMitch]

It is not. Maybe I am setting it up wrong? I created a Macro to run after exit of the first combo box that the user would be selecting from to "RunCommand" "Refresh". As soon as I exit that field however, it says "You must enter a value in the 'WorkOrders.Assignor' field, which is the next field down on the form, and terminates the macro.

 

[GAB]

In the workorders.assignor field In the properties box under data where it says default try something like =0. This should default the value to 0 until you put something in. Is that field linked to anything?

Gary

 

[MrMitch]

This would work like that, however, it does create the possibility of gaps if someone decides to cancel out of entering a record. I have an idea in mind that I am going to try and I'll post back here with any progress. Thanks for your help Gary.

 

[GAB]

Since your post was to stop duplicate entries, I guess the downside is having gaps in the numbering here is a link for an alternative work around that makes autonumbering editable, kind of.

http://www.vbaexpress.com/forum/showthread.php?t=11350
Good luck with this.
Gary