I have the following code in a field of a form with orders and Access crashes when I enter a new order
The tables are : HOUSES and ORDERS
Ex: A house has 1000 orders and the number of order (1000) is stored in a field of house table [NO_ORDER]
When I add a new order and I enter the name of the house this code give to the order field the number 1000+1=1001 and updates also the field [NO_ORDER] of house table to 1001
Private Sub ORDER_GotFocus()
'PURPOSE: To give the next order number for the order.
'NOTE: The code as is leads to rundom crashes. It must be modified to avoid the problem.
Dim thisCompany As String
Dim mm99 As Long
Dim dbthis As Database
Dim recthis As Recordset
On Error GoTo ORDER_GotFocus_Error
'The last given order for a house is stored at the house table. If all the needed data have 'been entered by the user we will open a recordset to that house, we will increase the order 'number by one and give the new number to that order. The code to be completely correct 'should put a read lock to the house record during the duration of the whole operation 'Unfortunately MS Access does not support read locks, so we put a write lock instead. Since 'write locks are not so restrictive as the read ones the code has been constructed to perform
'the critical parts of the operation in just two steps minimizing the risk for duplicate
'order numbers. To completly guard against such a possibility we use the combination of house 'code and order number is the primary key of the orders table.
If IsNull(Me![ORDER]) And Not IsNull(Me![HOUSE]) Then
thisCompany = Me![HOUSE]
Set dbthis = DBEngine.Workspaces(0).Databases(0)
Set recthis = dbthis.OpenRecordset("SELECT * FROM HOUSES WHERE [HOUSES]![HOUSE] = '" & thisCompany & "';")
recthis.Edit
mm99 = recthis![NO_ORDER] + 1
recthis![NO_ORDER] = mm99
recthis.UPDATE
Me![ORDER] = mm99
recthis.Close
End If
ORDER_GotFocus_Exit:
Exit Sub
ORDER_GotFocus_Error:
If Err.Number = 3260 Then 'the record is locked
Resume ORDER_GotFocus_Exit
Else
MsgBox Err.Description
End If
End Sub
Any ideas?
Thank you
The tables are : HOUSES and ORDERS
Ex: A house has 1000 orders and the number of order (1000) is stored in a field of house table [NO_ORDER]
When I add a new order and I enter the name of the house this code give to the order field the number 1000+1=1001 and updates also the field [NO_ORDER] of house table to 1001
Private Sub ORDER_GotFocus()
'PURPOSE: To give the next order number for the order.
'NOTE: The code as is leads to rundom crashes. It must be modified to avoid the problem.
Dim thisCompany As String
Dim mm99 As Long
Dim dbthis As Database
Dim recthis As Recordset
On Error GoTo ORDER_GotFocus_Error
'The last given order for a house is stored at the house table. If all the needed data have 'been entered by the user we will open a recordset to that house, we will increase the order 'number by one and give the new number to that order. The code to be completely correct 'should put a read lock to the house record during the duration of the whole operation 'Unfortunately MS Access does not support read locks, so we put a write lock instead. Since 'write locks are not so restrictive as the read ones the code has been constructed to perform
'the critical parts of the operation in just two steps minimizing the risk for duplicate
'order numbers. To completly guard against such a possibility we use the combination of house 'code and order number is the primary key of the orders table.
If IsNull(Me![ORDER]) And Not IsNull(Me![HOUSE]) Then
thisCompany = Me![HOUSE]
Set dbthis = DBEngine.Workspaces(0).Databases(0)
Set recthis = dbthis.OpenRecordset("SELECT * FROM HOUSES WHERE [HOUSES]![HOUSE] = '" & thisCompany & "';")
recthis.Edit
mm99 = recthis![NO_ORDER] + 1
recthis![NO_ORDER] = mm99
recthis.UPDATE
Me![ORDER] = mm99
recthis.Close
End If
ORDER_GotFocus_Exit:
Exit Sub
ORDER_GotFocus_Error:
If Err.Number = 3260 Then 'the record is locked
Resume ORDER_GotFocus_Exit
Else
MsgBox Err.Description
End If
End Sub
Any ideas?
Thank you
