|
|
I have different teams ( say TeamA , TeamB , TeamC ) , each team need
separate sequential Work Order Numbers ( if work order is created by TeamA
member it should see the last order number and add one to it )
tblCurrentMaxIDFmly contains the last Id number used by the team and this is
updated every time if Work order is created
RnDGroup CurrentMaxID
TeamA 4
TeamB 1
TeamC 2
I get calculate the Next Rev number in form “Frmtreeview†and use query
“"QryCreateWO"†that takes input from this form to created new work orders.
The database is silted in Front End and Back End ( Front end is on each
users local machine and the back end is on the network)
The Following code works fine BUT is two users click the cmbCreateWO button
on the EXACT SAME SECOND then it assigns the same WO number to both users.
Is there a Way to Prevent this from happening ( In Backend – Tools- Advance
– Default record locking I have set it to “Edited Record†) But still I am
not able to LOCK THE RECORDSET if it is been used by a different User.
Please help
Thanks
Raj
Private Sub cmbCreateWO_Click()
Dim strSQL As String
Dim stDocName As String
'Dim rs As Recordset
Dim rs As DAO.Recordset
Dim db As Database
Dim Fmly As String
Dim REV As String
If (Forms![Frmtreeview]![CmbFmly] & "" = "") Then Exit Sub
Set db = CurrentDb()
Fmly = Forms![Frmtreeview]![CmbFmly]
Set rs = db.OpenRecordset("SELECT Max(CurrentMaxID)AS MaxOfRev FROM
tblCurrentMaxIDFmly WHERE RnDGroup = '" & Fmly & "'")
If rs.RecordCount > 0 Then
'rs.MoveFirst
Forms![Frmtreeview]![cmbNexttRev] = rs!MaxofRev + 1
Me.CmbYourWONumberIs.Visible = True
Forms![Frmtreeview]![CmbYourWONumberIs] = "Work Order is" & " "
& Fmly & "-" & Me.cmbNexttRev
Forms![Frmtreeview]![CmbWOsimple] = Fmly & "-" & Me.cmbNexttRev
Set rs = db.OpenRecordset("SELECT * FROM tblCurrentMaxIDFmly
WHERE RnDGroup = '" & Fmly & "'")
If rs.RecordCount > 0 Then
rs.Edit
rs!CurrentMaxID = Forms![Frmtreeview]![cmbNexttRev]
rs.Update
End If
DoCmd.SetWarnings False
stDocName = "QryCreateWO"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "QryAppendDistinctWorkOrder"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Else
strSQL = "INSERT INTO tblCurrentMaxIDFmly ( RnDGroup, CurrentMaxID )
" & vbCrLf & _
"SELECT [Forms]![FrmTreeView]![CmbFmly] AS Fmly, ""1"" AS Rev " &
vbCrLf & _
"FROM tblCurrentMaxIDFmly;"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
stDocName = "QryCreateWO"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "QryAppendDistinctWorkOrder"
DoCmd.OpenQuery stDocName, acNormal, acEdit
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
|
|
"Raj" <Raj[ at ]discussions.microsoft.com> escreveu na mensagem
news:0438C7E2-79B9-4F32-A2CF-00814DA1D2B7[ at ]microsoft.com...
[Quoted Text] >I have different teams ( say TeamA , TeamB , TeamC ) , each team need
> separate sequential Work Order Numbers ( if work order is created by TeamA
> member it should see the last order number and add one to it )
>
> tblCurrentMaxIDFmly contains the last Id number used by the team and this
> is
> updated every time if Work order is created
>
> RnDGroup CurrentMaxID
> TeamA 4
> TeamB 1
> TeamC 2
>
>
>
> I get calculate the Next Rev number in form “Frmtreeview†and use query
> “"QryCreateWO"†that takes input from this form to created new work
> orders.
>
> The database is silted in Front End and Back End ( Front end is on each
> users local machine and the back end is on the network)
>
>
> The Following code works fine BUT is two users click the cmbCreateWO
> button
> on the EXACT SAME SECOND then it assigns the same WO number to both users.
>
> Is there a Way to Prevent this from happening ( In Backend – Tools-
> Advance
> – Default record locking I have set it to “Edited Record†) But still I am
> not able to LOCK THE RECORDSET if it is been used by a different User.
>
> Please help
>
> Thanks
> Raj
>
>
>
>
>
> Private Sub cmbCreateWO_Click()
> Dim strSQL As String
> Dim stDocName As String
>
> 'Dim rs As Recordset
> Dim rs As DAO.Recordset
> Dim db As Database
> Dim Fmly As String
> Dim REV As String
> If (Forms![Frmtreeview]![CmbFmly] & "" = "") Then Exit Sub
> Set db = CurrentDb()
> Fmly = Forms![Frmtreeview]![CmbFmly]
>
> Set rs = db.OpenRecordset("SELECT Max(CurrentMaxID)AS MaxOfRev FROM
> tblCurrentMaxIDFmly WHERE RnDGroup = '" & Fmly & "'")
> If rs.RecordCount > 0 Then
> 'rs.MoveFirst
> Forms![Frmtreeview]![cmbNexttRev] = rs!MaxofRev + 1
> Me.CmbYourWONumberIs.Visible = True
> Forms![Frmtreeview]![CmbYourWONumberIs] = "Work Order is" & " "
> & Fmly & "-" & Me.cmbNexttRev
> Forms![Frmtreeview]![CmbWOsimple] = Fmly & "-" & Me.cmbNexttRev
>
> Set rs = db.OpenRecordset("SELECT * FROM tblCurrentMaxIDFmly
> WHERE RnDGroup = '" & Fmly & "'")
> If rs.RecordCount > 0 Then
> rs.Edit
> rs!CurrentMaxID = Forms![Frmtreeview]![cmbNexttRev]
> rs.Update
> End If
> DoCmd.SetWarnings False
> stDocName = "QryCreateWO"
> DoCmd.OpenQuery stDocName, acNormal, acEdit
>
> stDocName = "QryAppendDistinctWorkOrder"
> DoCmd.OpenQuery stDocName, acNormal, acEdit
>
> Else
>
> strSQL = "INSERT INTO tblCurrentMaxIDFmly ( RnDGroup,
> CurrentMaxID )
> " & vbCrLf & _
> "SELECT [Forms]![FrmTreeView]![CmbFmly] AS Fmly, ""1"" AS Rev " &
> vbCrLf & _
> "FROM tblCurrentMaxIDFmly;"
> DoCmd.RunSQL strSQL
>
> DoCmd.SetWarnings False
> stDocName = "QryCreateWO"
> DoCmd.OpenQuery stDocName, acNormal, acEdit
> stDocName = "QryAppendDistinctWorkOrder"
> DoCmd.OpenQuery stDocName, acNormal, acEdit
> End If
>
> rs.Close
> Set rs = Nothing
> db.Close
> Set db = Nothing
>
>
>
>
|
|
I think this may be related to the fact that you are adding records using a
treeview, using recordsets rather than bound forms. My use of treeviews has
always been just to display existing records, and use the data, rather than
using the treeviews to add records. I just don't have similar problems to
what you describe when using bound forms and optimistic locking.
Further, it would seem to me if you have defined your table with a Primary
Key of Team AND Work Order Number, that you would get an error when trying
to write the duplicate, in which case you could regenerate the Work Order
Number and try again.
Larry Linson
Microsoft Office Access MVP
"Raj" <Raj[ at ]discussions.microsoft.com> wrote in message
news:0438C7E2-79B9-4F32-A2CF-00814DA1D2B7[ at ]microsoft.com...
[Quoted Text] >I have different teams ( say TeamA , TeamB , TeamC ) , each team need
> separate sequential Work Order Numbers ( if work order is created by TeamA
> member it should see the last order number and add one to it )
>
> tblCurrentMaxIDFmly contains the last Id number used by the team and this
> is
> updated every time if Work order is created
>
> RnDGroup CurrentMaxID
> TeamA 4
> TeamB 1
> TeamC 2
>
>
>
> I get calculate the Next Rev number in form "Frmtreeview" and use query
> ""QryCreateWO"" that takes input from this form to created new work
> orders.
>
> The database is silted in Front End and Back End ( Front end is on each
> users local machine and the back end is on the network)
>
>
> The Following code works fine BUT is two users click the cmbCreateWO
> button
> on the EXACT SAME SECOND then it assigns the same WO number to both users.
>
> Is there a Way to Prevent this from happening ( In Backend - Tools-
> Advance
> - Default record locking I have set it to "Edited Record" ) But still I am
> not able to LOCK THE RECORDSET if it is been used by a different User.
>
> Please help
>
> Thanks
> Raj
>
>
>
>
>
> Private Sub cmbCreateWO_Click()
> Dim strSQL As String
> Dim stDocName As String
>
> 'Dim rs As Recordset
> Dim rs As DAO.Recordset
> Dim db As Database
> Dim Fmly As String
> Dim REV As String
> If (Forms![Frmtreeview]![CmbFmly] & "" = "") Then Exit Sub
> Set db = CurrentDb()
> Fmly = Forms![Frmtreeview]![CmbFmly]
>
> Set rs = db.OpenRecordset("SELECT Max(CurrentMaxID)AS MaxOfRev FROM
> tblCurrentMaxIDFmly WHERE RnDGroup = '" & Fmly & "'")
> If rs.RecordCount > 0 Then
> 'rs.MoveFirst
> Forms![Frmtreeview]![cmbNexttRev] = rs!MaxofRev + 1
> Me.CmbYourWONumberIs.Visible = True
> Forms![Frmtreeview]![CmbYourWONumberIs] = "Work Order is" & " "
> & Fmly & "-" & Me.cmbNexttRev
> Forms![Frmtreeview]![CmbWOsimple] = Fmly & "-" & Me.cmbNexttRev
>
> Set rs = db.OpenRecordset("SELECT * FROM tblCurrentMaxIDFmly
> WHERE RnDGroup = '" & Fmly & "'")
> If rs.RecordCount > 0 Then
> rs.Edit
> rs!CurrentMaxID = Forms![Frmtreeview]![cmbNexttRev]
> rs.Update
> End If
> DoCmd.SetWarnings False
> stDocName = "QryCreateWO"
> DoCmd.OpenQuery stDocName, acNormal, acEdit
>
> stDocName = "QryAppendDistinctWorkOrder"
> DoCmd.OpenQuery stDocName, acNormal, acEdit
>
> Else
>
> strSQL = "INSERT INTO tblCurrentMaxIDFmly ( RnDGroup,
> CurrentMaxID )
> " & vbCrLf & _
> "SELECT [Forms]![FrmTreeView]![CmbFmly] AS Fmly, ""1"" AS Rev " &
> vbCrLf & _
> "FROM tblCurrentMaxIDFmly;"
> DoCmd.RunSQL strSQL
>
> DoCmd.SetWarnings False
> stDocName = "QryCreateWO"
> DoCmd.OpenQuery stDocName, acNormal, acEdit
> stDocName = "QryAppendDistinctWorkOrder"
> DoCmd.OpenQuery stDocName, acNormal, acEdit
> End If
>
> rs.Close
> Set rs = Nothing
> db.Close
> Set db = Nothing
>
>
>
|
|
Raj <Raj[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >The Following code works fine BUT is two users click the cmbCreateWO button
>on the EXACT SAME SECOND then it assigns the same WO number to both users.
>
>Set rs = db.OpenRecordset("SELECT Max(CurrentMaxID)AS MaxOfRev FROM
>tblCurrentMaxIDFmly WHERE RnDGroup = '" & Fmly & "'")
> If rs.RecordCount > 0 Then
> 'rs.MoveFirst
> Forms![Frmtreeview]![cmbNexttRev] = rs!MaxofRev + 1
> Me.CmbYourWONumberIs.Visible = True
> Forms![Frmtreeview]![CmbYourWONumberIs] = "Work Order is" & " "
>& Fmly & "-" & Me.cmbNexttRev
> Forms![Frmtreeview]![CmbWOsimple] = Fmly & "-" & Me.cmbNexttRev
>
> Set rs = db.OpenRecordset("SELECT * FROM tblCurrentMaxIDFmly
>WHERE RnDGroup = '" & Fmly & "'")
> If rs.RecordCount > 0 Then
> rs.Edit
> rs!CurrentMaxID = Forms![Frmtreeview]![cmbNexttRev]
> rs.Update
> End If
I think the problem is the amount of time between the first SELECT and
the second SELECT. Don't execute the first SELECT but move the code
after the second SELECT. You should be Editing the data, fetching the
cmbNexttRev while still locking the record, adding 1 to it and then
updating the record.
> strSQL = "INSERT INTO tblCurrentMaxIDFmly ( RnDGroup, CurrentMaxID )
>" & vbCrLf & _
> "SELECT [Forms]![FrmTreeView]![CmbFmly] AS Fmly, ""1"" AS Rev " &
>vbCrLf & _
> "FROM tblCurrentMaxIDFmly;"
> DoCmd.RunSQL strSQL
>
> DoCmd.SetWarnings False
The problem with DoCmd.RunSQ is that it ignores any errors. Either
of the following will display any error messages received by the
query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror..
For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText You can then remove the
docmd.setwarnings lines.
If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.
Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
|