So I've done some additional testing and narrowed the problem down to the unbound control that is used to apply the client filter.
I can change the atty filter back and forth without a problem, but as soon as I filter by client the recordset becomes not updatable. the filter is applied properly but after that the recordset is not updateable. Bottom line is that I don't want to update the recordset and can't figure out what I'm doing that makes access thinks I'm trying to (both filter fields are unbound btw - one is a combo box and the problematic one is just a regular text box).
The event code on the problematic filter box is as follows:
Private Sub SelectClientMaint_LostFocus()
Dim strWhere As String
Dim lngLen As Long 'Length of the criteria string to append to.
If Not IsNull(Me.SelectAttyMaint) Then
strWhere = strWhere & "([ATTY_CK] = " & Me.SelectAttyMaint & ") AND "
End If
If Not IsNull(Me.SelectClientMaint) Then
strWhere = strWhere & "([CLCL_Display_Name] Like ""*" & Me.SelectClientMaint & "*"") AND "
End If
lngLen = Len(strWhere) - 5
If lngLen > 0 Then 'remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
the query under the main form is below - note that there is no desire to update the recordset in the form and in fact the fields (other than the filter fields) are all set to not updateable. I do have the form set as edits allowed - I'm not sure why I changed that back from no anymore.
The table relationships are as follows: MSCO has an optional 1:1 relationship to CLCL. The ERRORs thing is a query that is based on an Import_contacts table with no joins - it is simply a way I have centralized my error definition. I put it below in case you would want to see that.
SELECT T_CLCL_Client.CLCL_ExpDate, T_CLCL_Client.ATTY_CK, T_CLCL_Client.CLCL_CK, T_MSCO_Contacts.MSCO_Contact_Type, T_MSCO_Contacts.MSCO_Mail_To_Name, T_MSCO_Contacts.MSCO_Address, T_MSCO_Contacts.MSCO_Address2, T_MSCO_Contacts.MSCO_City, T_MSCO_Contacts.MSCO_State, T_MSCO_Contacts.MSCO_Zip, T_MSCO_Contacts.MSCO_Email_Address, T_MSCO_Contacts.MSCO_Contact_Name, T_CLCL_Client.MSCO_CK, T_CLCL_Client.CLCL_Display_Name, T_CLCL_Client.CLCL_Customer_ID, T_CLCL_Client.CLST_CK, T_CLCL_Client.CMDB_Status, T_CLCL_Client.CLCL_Market_Segment, T_CLCL_Client.CLCL_Referred_By, T_CLCL_Client.CLCL_Anniversary, T_CLCL_Client.CLCL_Input_Errors, T_CLCL_Client.CLCL_CreatedDate, T_CLCL_Client.CLCL_LastUpdatedDate, T_CLCL_Client.CLCL_LastUpdatedDateTime, T_CLCL_Client.CLCL_UpdatedBy, T_CLCL_Client.CLCL_Client_Type, T_CLCL_Client.CLCL_UpdatedRsn, IIf([DisplayNameReqd] Is Not Null,[DisplayNameReqd] & Chr(13) & Chr(10)) & IIf([NicknameReqd] Is Not Null,[nicknamereqd] & Chr(13) & Chr(10)) & IIf([TypeInvalid] Is Not Null,[TypeInvalid] & Chr(13) & Chr(10)) & [professioninvalid] AS Errors
FROM (T_CLCL_Client LEFT JOIN T_MSCO_Contacts ON T_CLCL_Client.MSCO_CK = T_MSCO_Contacts.MSCO_CK) LEFT JOIN [ERROR - CORRECT PRIOR TO IMPORT] ON T_CLCL_Client.CLCL_Customer_ID = [ERROR - CORRECT PRIOR TO IMPORT].[Customer ID]
WHERE (((T_CLCL_Client.CLCL_ExpDate) Is Null));
---- errors query ---
SELECT IMPORT_Contacts.office, IMPORT_Contacts.nickname, IMPORT_Contacts.[Display name], IMPORT_Contacts.Profession, IMPORT_Contacts.[Customer ID], IIf([Display name] Is Null," - Outlook Display Name is required","") AS DisplayNameReqd, IIf([nickname] Is Null," - Nickname is required. Populate Outlook with Formal Mailing Label Name","") AS NicknameReqd, IIf([Office] Is Null,"-Outlook Office is Required. Valid values are: Prospect, Client, Estate, Vendor, Referral, Aide, Attorney, or Inactive",IIf([Office] In ("Prospect","Client","Estate","Vendor","Referral","Aide","Attorney","Inactive"),"","- Outlook OFFICE is set to '" & [Office] & "' which is invalid. Valid values are: Prospect, Client, Estate, Vendor, Referral, Aide, Attorney, or Inactive")) AS TypeInvalid, IIf([profession] Is Null,"-Outlook Profession is Required. Valid values are: Individual, Nonprofit, Business, Estate, All, NA",IIf([profession] In ("Individual","Nonprofit","Business","Estate","ALL","NA"),"","- Outlook PROFESSION is '" & [profession] & "' which is invalid. Valid values are: Individual, Nonprofit, Business, Estate, All, NA")) AS ProfessionInvalid
FROM IMPORT_Contacts
WHERE (((IIf([Office] Is Null,"-Outlook Office is Required. Valid values are: Prospect, Client, Estate, Vendor, Referral, Aide, Attorney, or Inactive",IIf([Office] In ("Prospect","Client","Estate","Vendor","Referral","Aide","Attorney","Inactive"),"","- Outlook OFFICE is set to '" & [Office] & "' which is invalid. Valid values are: Prospect, Client, Estate, Vendor, Referral, Aide, Attorney, or Inactive")))<>"")) OR (((IIf([Display name] Is Null," - Outlook Display Name is required",""))<>"")) OR (((IIf([nickname] Is Null," - Nickname is required. Populate Outlook with Formal Mailing Label Name",""))<>"")) OR (((IIf([profession] Is Null,"-Outlook Profession is Required. Valid values are: Individual, Nonprofit, Business, Estate, All, NA",IIf([profession] In ("Individual","Nonprofit","Business","Estate","ALL","NA"),"","- Outlook PROFESSION is '" & [profession] & "' which is invalid. Valid values are: Individual, Nonprofit, Business, Estate, All, NA")))<>""));
