duplicating entries due to multiple users

[benzan]

at first i had it set up so that when users open new request form, it automatically generates a number for them.
in a long run it ended up creating too many blank forms so i have decided to assign the number after they have finished filling out the form.

so i have below coding in after save button is clicked.

If Nz(Me.txtPRnum, 0) = 0 Then

Dim rs As New ADODB.Recordset
Dim NextRec As Integer

rs.Open "select max([prnum]) as M from tbl_PurchaseRequisition", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
NextRec = Nz(rs("M"), 0) + 1
rs.Close

Set rs = Nothing

Me.txtPRnum = NextRec

End If

here's the problem. If two (or more) users are filling out the form at the same time, it generates the same PR number which one of the record doesn't get saved.

i've tried adding lines like "docmd.runcmdsave"
to save the first record so that pr number is saved but that didnt work out.

any ideas??

 

[Remou]

You should read MichaelRed's FAQ. faq700-184

 

[benzan]

i can't understand half of those codings >_<

 

[Remou]

It's good stuff. For the most part, you do not need to understand it, just to use it. Mess around with a scratch database. Enjoy

 

[benzan]

so.. copy & paste and change names?
and call the function like Function NewQI_Num() ?

 

[Remou]

That's it. As far as I recall, there is not a lot of name changing required.

 

[benzan]

im having hard time putting in my own values.

since
MyDbs = CurrentDb()
doens't that mean i can just leave it that?

and what do i put in openrecordset? tablename?

Set basedata = mydbs.openrecrdset("basic Data")