Multiple User Access Database Issue

[ansar.abdulhai]

Hello All,

Don't know this is the right spot to ask multiple user access issue.

I have a multiple user access database in access 2007. I did created back end and front end. Made accde format of front end and copied it on everyones desktop. Every thing works fine.

Problem:

I have a form "Form A" with a cmd button "Generate Ticket". Basically when the user fills up the form and hits the cmd button, the program under cmd button checks for the maximum value of "TicketNumber" field in the table "Main Table". The "TicketNumber" field is a custom autonumber type. The program now increments the value of maximum "TicketNumber" by 1 and assigns it to current record's TicketNumber field and also populates the rest of the fields. It also simultaneously sends out an email to respective personnel that a ticket number is generated.

The problem is when two users hit cmd button "Generate Ticket" at the same time with a delay of some milliseconds, the table is only populated with the info of the user who hit the cmd button first and the second user's info is getting deleted except his name and a blank record is getting populated in the Main Table. Now, here the email which is sending out the ticket number shows same ticket number generated for both the users. But when main table is checked, it is not. The second user's info is totally blank except his name is populated and the first user's info is getting populated as it is required.

Can anyone solve this issue? This is really urgent. I have only this issue to implement my software.

Thanks all,
-----------

 

[pbaldy]

Does pressing the button force a save of the record after the number is generated? If so, I would think it would be relatively hard for 2 users to get the same number.

If yours is a high volume environment where it can happen, some people use a one-record table to store the next available number. The process would open/lock that table, get the number, increment and save the next number, then close. A second user trying to get a number would run into the locked table and not be able to get the same number. I've seen people use error trapping to get around the locking, so the second user pauses and gets the next number.

 

[Access Hero]

I just answered this question on a different thread.

Don't create your own keys! Allow your DBMS to assign incremental keys for you. This is exactly the kind of problem you get.

 

[ansar.abdulhai]

I am having trouble to do this error trapping. How can I lock the table and let the other user wait until the first user completes his operation and populates the table and then let the second user generate another ticket number and close the form.

Please see my code below:
Private Sub Generate_Ticket_Click()
Dim olapp As Outlook.Application
Dim olNamespace As Outlook.Namespace
Dim olfolder As Outlook.MAPIFolder
Dim olMailItem As Outlook.MailItem
Dim olattach As Object
Dim strbodytext As String

Dim StrNumber As String
Dim ctl As Control
Dim Y As Long
Dim z As Variant
Dim X As String
Dim E As Variant

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim a As String

Set db = CurrentDb
Set qdf = db.QueryDefs("EmailAddressesQueryTechForm")

For Each prm In qdf.Parameters
prm.Value = Me.ValueStream 'Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)

Do While Not rs.EOF
For Each objField In rs.Fields
If Len(a) = 0 Then
a = objField.Value
Else
a = a & ";" & objField.Value
End If
Next
rs.MoveNext
Loop



For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
If Len(ctl.ControlSource) > 0 And Not ctl.ControlSource Like "=*" Then
If IsNull(ctl.Value) Then
If Not ctl.Name = "WorkOrderNo" And Not ctl.Name = "InitiationDate" Then
Y = Y + 1
End If
End If
End If
Case Else
End Select
Next ctl


If Y <> 0 Then
MsgBox "Please fill out the form completely to generate a ticket number. WorkOrderNo is optional.", vbInformation
Else
StrNumber = Nz(DMax("[TicketNumber]", "MainTable"), "0")
If StrNumber = 0 Then
CLONE:
StrNumber = Format(Date, "yyyymmdd0001")
Else
If Left(StrNumber, 8) < Format(Date, "yyyymmdd") Then
GoTo CLONE
Else
StrNumber = Format(Date, "yyyymmdd") & Format(Right(StrNumber, 4) + 1, "0000")
End If
End If
Me.TicketNumber = StrNumber
'DoCmd.RunCommand acCmdSave
Me.InitiationDate = Now()
Me.Status.Value = "Ticket Generated"

Set olapp = CreateObject("Outlook.Application")
Set olNamespace = olapp.GetNamespace("MAPI")
Set olfolder = olNamespace.GetDefaultFolder(olFolderInbox)
Set olMailItem = olfolder.Items.Add("IPM.Note")
Set olattach = olMailItem.Attachments

strbodytext = "<HTML><BODY>The Ticket [" & Me.TicketNumber & "] has been generated by [" & Me.EmpName & "] for Fixture [" & Me.FixtureNumber & "] at [" & Now() & "].</BODY></HTML>"

With olMailItem
.Subject = "Ticket Generated"
.To = a
.CC = Me.EmpName.Value
.HTMLBody = strbodytext
.Importance = olImportanceHigh
End With

olMailItem.Send
DoCmd.Close
MsgBox "Your ticket number is sent to you via email. Please check your email."

Set objsafeMail = Nothing
Set olMailItem = Nothing
Set olfolder = Nothing
Set olNamespace = Nothing
Set olapp = Nothing

End If
Set db = Nothing
Set rs = Nothing
Set qdf = Nothing
Set prm = Nothing
End Sub

Basically my code is looking for max. value of ticket number in the table and sequentially incrementing the ticket number and then populating it in the table by closing the form.

Any help would be greatly appreciated.

Thanks,
--------

 

[pbaldy]

In general I would agree, but there are times when the skipped numbers possible with autonumbers are not acceptable (for example invoice numbers, where auditors will be looking for that number to be accounted for).

 

[ansar.abdulhai]

That's right Paul. My software would not be implemented if there are skipped autonumbers coz it is frequently audited. Is there any way around?

 

[ansar.abdulhai]

Is there anyone who can solve my issue????????????? Please!!!!!!!!!!!

 

[pbaldy]

I gave you the outline of a method earlier. The code you posted does not incorporate it, so I figured I'd wait for you to give it a try. To clarify, I wouldn't lock the main data table, I'd have a one record table that stored the next number to be used.

 

[ansar.abdulhai]

I will give it a try Paul. I am trying now!

 

[ansar.abdulhai]

Hi Paul,

I tried opening new table and all that stuff. Please see my code below:

Private Sub Generate_Ticket_Click()

Dim olapp As Outlook.Application
Dim olNamespace As Outlook.Namespace
Dim olfolder As Outlook.MAPIFolder
Dim olMailItem As Outlook.MailItem
Dim olattach As Object
Dim strbodytext As String

Dim StrNumber As String
Dim ctl As Control
Dim Y As Long
Dim z As Variant
Dim X As String
Dim E As Variant

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim tbl As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim a As String
Dim objField As DAO.Fields

Set db = CurrentDb
Set tbl = db.TableDefs("CounterTable")
Set rs2 = tbl.OpenRecordset(dbOpenDynaset)

Do While rs2.EOF
For Each objField In rs.Fields
If objField!Name = "NextAvailableTicket" Then
objField!Value = objField!Value + 1
If objField!Name = "Username" Then
objField!Value = DLookup("Username", "Employee", "[Username]= '" & Forms!Login!Username.Value & "'")
End If
End If
Next
rs2.MoveNext
Loop
'rs!NextAvailableTicket = rs2!NextAvaialbleTicket + 1
'rs!Username = DLookup("Username", "Employee", "[Username]= '" & Forms!Login!Username.Value & "'")

If (rs2!NextAvailableTicket = 1) And (DLookup("Username", "Employee", "[Username]= '" & Forms!Login!Username.Value & "'") = rs2!Username) Then
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
If Len(ctl.ControlSource) > 0 And Not ctl.ControlSource Like "=*" Then
If IsNull(ctl.Value) Then
If Not ctl.Name = "WorkOrderNo" And Not ctl.Name = "InitiationDate" Then
Y = Y + 1
End If
End If
End If
Case Else
End Select
Next ctl
If Y <> 0 Then
MsgBox "Please fill out the form completely to generate a ticket number. WorkOrderNo is optional.", vbInformation
Set db = Nothing
Set tbl = Nothing
Set rs2 = Nothing
Exit Sub
Else
StrNumber = Nz(DMax("[TicketNumber]", "MainTable"), "0")
If StrNumber = 0 Then
CLONE:
StrNumber = Format(Date, "yyyymmdd0001")
Else
If Left(StrNumber, 8) < Format(Date, "yyyymmdd") Then
GoTo CLONE
Else
StrNumber = Format(Date, "yyyymmdd") & Format(Right(StrNumber, 4) + 1, "0000")
End If
End If
Me.TicketNumber = StrNumber
dt = Now()
Me.InitiationDate = dt
Me.Status.Value = "Ticket Generated"
Me.Dirty = False

Set qdf = db.QueryDefs("EmailAddressesQueryTechForm")

For Each prm In qdf.Parameters
prm.Value = Me.ValueStream 'Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)

Do While Not rs.EOF
For Each objField In rs.Fields
If Len(a) = 0 Then
a = objField!Value
Else
a = a & ";" & objField!Value
End If
Next
rs.MoveNext
Loop
Set olapp = CreateObject("Outlook.Application")
Set olNamespace = olapp.GetNamespace("MAPI")
Set olfolder = olNamespace.GetDefaultFolder(olFolderInbox)
Set olMailItem = olfolder.Items.Add("IPM.Note")
Set olattach = olMailItem.Attachments

strbodytext = "<HTML><BODY>The Ticket [" & Me.TicketNumber & "] has been generated by [" & Me.EmpName & "] for Fixture [" & Me.FixtureNumber & "] at [" & dt & "].</BODY></HTML>"

With olMailItem
.Subject = "Ticket Generated"
.To = a
.CC = Me.EmpName.Value
.HTMLBody = strbodytext
.Importance = olImportanceHigh
End With

olMailItem.Send
DoCmd.Close
MsgBox "Your ticket number is sent to you via email. Please check your email."

Set objsafeMail = Nothing
Set olMailItem = Nothing
Set olfolder = Nothing
Set olNamespace = Nothing
Set olapp = Nothing

End If

DoCmd.SetWarnings False
DoCmd.OpenQuery "CounterTableDeleteQuery2"
DoCmd.SetWarnings True
Set db = Nothing
Set rs = Nothing
Set qdf = Nothing
Set prm = Nothing
Set rs2 = Nothing
Set tbl = Nothing
Set objField = Nothing
Else
MsgBox "You cannot generate a ticket now because another user is already generating it. Please wait and try again."
DoCmd.SetWarnings False
DoCmd.OpenQuery "CounterTableDeleteQuery"
DoCmd.SetWarnings True
Exit Sub
End If

End Sub

The moment I hit the cmd button, it is going to Else part which I highlighted above. I donno reason why. Can you help me?

--------