Filtering results on a form by a field populated by a DLookup expression

[IncidentalProgrammer]

I have a search form to find insurance policies in my database. I'm using the Allen Browne method with multiple filter criteria on a continuous form, and for the most part, it's working great. However, I can't get the filter to work for searching by the Account Name. I'm thinking the problem is most likely due to that field's control source being a DLookup, instead being tied directly to the table. The reason for this is that both the Account and Insurance Company's IDs are present in the continuous form, and they both are Entity IDs (EntID) that come from the same table. So when I originally created this form, instead of pulling the Account Name from that EntID, the field was populating with the Insurance Company's name based on that EntID field, so I wrote a DLookup to make it pull from the correct EntID.



So now when I try to use this code to compare the Account Name to the filter field, I get no results:

If Not IsNull(Me.ctlActName) Then
strWhere = strWhere & "([EntPrimName] Like ""*" & Me.ctlActName & "*"") AND "
End If

I've experimented with writing a DLookup into VBA instead of as the control source and a number of other tweaks, but can't get anything to work. How would I need to manipulate this code in order for it to read the results of the DLookup?

[June7]

Certainly a DLookup() can provide parameter for constructing a criteria string.

Textbox ctlActName has a DLookup as ControlSource? That expression is pulling the company name? Then the reference to the textbox should work.

Better would be to fix the data and use company ID for search.

[IncidentalProgrammer]

Sorry, ctlActName is the textbox to type the filter into. It's ctlEntPrimName that has the DLookup as control source, and that's where the [EntPrimName] in the code should be coming from (via the Entity table), like it does for the other filter criteria, which are working as intended (which is what makes me think it's the DLookup that's throwing the wrench in it). The insurance company name was being pulled instead of the account name when I had first set up the form, so I wrote the DLookup to pull the account name instead. I'm needing the rows of the search to show Policy #, Account ID, Account Name, Insurance Company, Effective Date, Expiration Date, and Policy Status.

I would love to use just the Account IDs for the search, but they're just abstract numbers, and there are a LOT of accounts. Being able to search by Account Name, especially with partial matches, will make it so much easier to use.

[June7]

I am a bit confused. If the code takes user input into ctlActName as parameter, what does ctlEntPrimName have to do with constructing the filter string? What values are in the field [EntPrimName] and how do they get there?

Instead of a textbox for entering ActName filter parameter, why not a combobox? The RowSource could have the Account name and number as columns then the code could pull data from whichever column you want. User would see and select the name but code could use the number.

Show the expression from ctlEntPrimName control source.

[IncidentalProgrammer]

EntPrimName is supposed to hold the Account name. The way I've structured my database, accounts, clients, insurance companies, and producers are all "entities", so because I had two Entity numbers present on the form (one for the account, and another for the insurance company), I had to use a DLookup for the EntPrimName box to pull Entity names based on the Accounts, rather than the insurance companies (it was pulling insurance company names when I initially put the form together through the wizard). Now when I try to enter something in ctlActName to filter the results by Account Name, nothing shows up, and I'm thinking it's because the data of EntPrimName is populated by DLookup (I theorize this, because it's the only field with a DLookup, and the only field not working).

I'd really like to keep it as a text box, if at all possible. The code allows for proximate matches, which is great for me and the rest of the users, because some of these accounts have...unique names. Being able to get results on a partial match is something they were all really excited about. And if the DLookup is what's throwing a wrench in things, I'm not sure switching to a combobox would resolve it.

The expression in ctlEntPrimName is: =DLookUp("EntPrimName","tblEntity","EntID = '" & [ctlEntID] & "'")
The expression works perfectly for populating this field. It just doesn't appear that it can communicate with the filter.

[June7]

If you can do a DLookup using an ID field then should be able to just join the tables in query to make the related info (EntPrimName) available to the form.