Increment Unique Number for Multiple Users 1

[julieatnexus]

I am developing a database that allows multiple users to access and enter information into a form. If they are entering a new record, the form will be numbered incrementally (like autonumber). The problem exists when two or more users are accessing the form at the same time. I am trying to write the code for a command button that locks the table, determines the next number, assigns it, and releases the table.

I would appreciate any help you can give.

Julie

 

[Eradic8or]

If you have 2 people entering data at the same time, Access will return a error when the users try to save.
You could use a a bit of VBA code here on the save record button that would use a case statement to trap the error (I forget the code).
If access return the error, your case statement will come into effect and you can then +1 to your unique number.
If you do this in a loop, it wont matter how many users are accessing the record, it will automatically find the next available number.

eradic8or.

 

[MichaelRed]

See the FAQ in Ms. Access Tables and Relationships.

Why Autonumber shouldn't be used in Multiuser databases (and how to get the unique number)

I think it covers the subject pretty well.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[julieatnexus]

Michael,

Please bear with me.

I am getting a compile error (User defined type not defined) for the "Dim Mydbs as Database" statement. (I did replace the variable with my database name.)

Thanks in advance for your help.

Julie

 

[MichaelRed]

Is the error on the DIM statement?

Pleaser post the parts/lines which are involved in the err. At least the dim and set statements.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[julieatnexus]

Michael,

When it errors, it goes to the "Dim Waivers as Database" line. I have created tblNewQiNum and qSelQiMax as you listed in the FAQ. I changed Mydbs to Waivers and Basic Data to tblWaivers. I call the function on the click event for a command button.

Public Function NewQi_Num() As Long
On Error GoTo NewQINum_Err

Dim Waivers As Database
Dim BaseData As Recordset
Dim tblNewQiNum As Recordset
Dim qSelQiMax As Recordset

Const RiErr = 3000
Const LockErr = 3260
Const InUseErr = 3262
Const NumReTries = 20#

Dim NumLocks As Integer
Dim IngX As Long
Dim QiNum As Long
Dim IngOldQiNum As Long
Dim IngNewQiNum As Long
Dim IngBigQiNum As Long
Dim QiYear As Long
Dim QiMnth As String
Dim strQiNum As String

Set Waivers = CurrentDb()
Set BaseData = Waivers.OpenRecordset("tblWaiver&quot

Do you need more of the code?

Julie

 

[MichaelRed]

Julie,

This is 'new' to me. I have not seen an error in Dim XXX as database, except where XXX is a reserved word. The function was developed for Ms. Access '97, so if you are using Ms. Access 2K, there MAY be some difference in the syntax? such as needing to be "DAO.Databse", however I have not (yet) migrated to the 2K product.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[julieatnexus]

Michael,

There must be a difference in the syntax for 2000. I tried it in 97 and it worked. I'll start a new thread for help with the 2K syntax.

The next question I have is how do I get the QiNum into the Qi field for the new record I create?

I tried:

With tblWaiver
.edit
!Qi=tblNewQiNum!QiNum
.update
End With

but it changed the first record instead of the new record I was on.

Thanks,

Julie

 

[MichaelRed]

You need to do a seek or Find for the correct record to do it this way. I just make a bound text box on the form where the record is being constructed, and on the event which creates the new record, just go off and set the textbox to the number retrieved. Then, when the record is created, the "number" is just a field which is set. Of course, where ever the field is displayed. it should have .enabled set to false.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over