".Value" is the default property - not necessary to add it - but it doesn't hurt.
I've read that specifying the field names is faster than selecting all fields (*)
When I add records to a recordset I already have open, I create another recordset (append only) to add the records. I've had less problems...
I didn't test this code, but it should work.....
Code:
Private Sub Add_Divsion_of_Work_Click()
Dim strSQL As String
Dim rs As DAO.Recordset
Dim n As DAO.Recordset
If IsNull(Me.combo_subdivision_lookup) Then
MsgBox ("You must make a selection before adding")
Else
strSQL = "SELECT company_id FROM company_division WHERE company_division.company_id = " & Me.ID & _
" AND company_division.subdivision_number = '" & Me.combo_subdivision_lookup.Column(2) & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF Then
strSQL = "SELECT company_id, subdivision_number FROM company_division"
Set n = CurrentDb.OpenRecordset(strSQL, , dbAppendOnly)
n.AddNew
'MsgBox ("Debug: Adding company ID: " & Me.ID.Value)
n!company_id = Me.ID
'MsgBox ("Debug: Adding division ID: " & Me.combo_subdivision_lookup.Column(2))
n!subdivision_number = Me.combo_subdivision_lookup.Column(2)
n.Update
n.Close
Set n = Nothing
Else
MsgBox ("This division of work is already defined for this company")
End If
rs.Close
Set rs = Nothing
Me.List_of_subdivisions.Requery
End If
End Sub
Don't know if the above code mods will help....
Multi-users across a network seems to always slow the response time.