Hi,
I'm pretty new to access and have been learning via youtube and forums. A quick background of the back end - there is a table with the company name, website, address, etc. Then a second table with the actual contact names (first, last, email) associated with each company (as companies can have multiple points of contacts) with a one to many relationship.
I created a split form that includes a few search options (using a combo box to search by state, division or company specialty, and also a keyword search box to search by company name or description). The results are filtered using the DoCMd.ApplyFilter function. Below is my code to filter after update of my keyword search. What I would like to do, is also be able to search by first or last name, which is on my subform. However, I have gone in circles and in many different directions without success. Any help is greatly appreciated. (Side note, I have also tried to break up this line of code using &_ without success....which is why it is one long line). The below code is searching the company name and trade description, while looking to see if a state, division, and specialty is selected (or null).
Private Sub KW_AfterUpdate()
DoCmd.ApplyFilter "", "([Company Name] Like ""*"" & [Forms]![NVSF]![KW] & ""*"" OR [TrdDesc] Like ""*"" & [Forms]![NVSF]![KW] & ""*"") AND ([State] Like ""*"" & [Forms]![NVSF]![cboSt] & ""*"" or [State] Is Null) And ([Division] Like ""*"" &[Forms]![NVSF]![CboDiv]& ""*"" Or [Division] Is Null) And ([Specialty] Like ""*"" &[Forms]![NVSF]![cboSp] & ""*"" Or [Specialty] Is Null)", ""
End Sub