Displaying results of a Select statement

[csh2013]

Good afternoon, all. Need some assistance. I have a database with several one-to-many relationships and a nested subform based off of those relationships. Relationships are as follows:
One Lender to Many Relationship IDs
One Relationship ID to Many Tax IDs
One Tax ID to Many DocumentsRequired

My forms are nested as follows:
Relationship ID form (contains info for Relationship ID and Lender)
-> Tax ID
-> DocumentsRequired

I believe I'm just overcomplicating this. But I have simple search box (an unbound text box with a command button), which is located on the top-level form for Relationship IDs. As you'll see above, that form only contains the fields for Relationship ID and Lender. However, users have to be able to search by Relationship ID, Customer Name, or Tax ID number, the last two of which are only available on the nested subforms. Currently I have the search box reaching out to grab results from a query. When I assign the results to the Me.RecordSource, it works perfectly except that it's in read-only format. The users have to be able to edit the results of their search. I'm not even sure I'm doing this in the easiest fashion. I would have preferred to just use the select statement to search through the subform, but I'm guessing my syntax was wrong because I never got it to work. Below is what I currently have. I'm open to all suggestions as I've been working on this for 2 days...:banghead:

Dim strtext As String
Dim strsearch As String

strtext = Me.SearchBoxTxt.Value
strsearch = "SELECT [Relationship ID] " & _
"FROM CustomerNormQuery " & _
"WHERE [Relationship ID] like ""*" & strtext & "*"" OR [Customer Name] Like ""*" & strtext & "*"" " & _
"OR [EIN/SSN] Like ""*" & strtext & "*"" " & _
"GROUP BY [Relationship ID]"
Me.RecordSource = strsearch

 

[CJ_London]

I think your problem is the group by in your sql. If you think about it, if your records are grouped - which one is being edited?

 

[csh2013]

I found that part of the reason I couldn't edit the lender specifically was because it wasn't part of my SELECT statement. However, it appears as though you are correct. Even after I added the Lender field in, which is primarily what we were trying to edit, I still can't choose another lender if I have the GROUP BY clause in my SELECT statment.

Do you have any suggestions on how I can group the records? I've attached a sample copy of the database. The issue I have when I'm not able to group by relationship is that it brings up the relationship twice. So, for instance, if you search by "Harry", the results will show 3 records, but there are only 2 relationships. Relationship ID 00000000004 has two customers by the name of Harry, but I just want it to show earch relationship only once.

 

[CJ_London]

I've had a look at your db and although I understand the problem, I'm struggling to suggest a solution with the way you have things set up.

Part of the problem is you are building the form on your relationship table as the source for the main form. I think the mainform source should be customers. If you are searching for 'harry' you will find more than one - they are two separate records with potentially different lenders and therefore different relationship iD's.

The other part of the problem is that there is little point in having the table lender choices in a relationship since it only has one field. The way you should be using this is as a lookup table to be used in a combo box.

That then means you don't need the relationshipid table either - you can just use the lender choices table as a source to a combo in your customer table. If the relationshipID is supposed to mean something - such as an agreement number, then have this as an extra field in your customer table.

What you might want to do is have a separate table for customers (since I presume at some point you will need things like contact details and they can have more than one lender) and then your current customer table becomes the 'relationships' table.

If you do this, I think you will find this will solve your problem.

Some other points - table and field names - recommend you lose the spaces and stop using non alphanumeric characters such as (): -they will almost certainly cause you problems in the future.

Also, your filter won't work properly, this

[Relationship_ID]='SELECT [Relationship_ID] FROM Me.SecondaryForm WHERE [Relationship_ID] like "*ruth*" OR [Customer Name] Like "*ruth*" OR [EIN/SSN] Like "*ruth*" GROUP BY [Relationship_ID]'

is looking for a relationshipID (which should be numeric) which equals='SELECT .....etc rather than equals a number (or text number in your case)

 

[csh2013]

Thanks for the review, CJ_London. I totally understand the naming convention issue. I'm trying to modify a database built by someone else and I didn't want to tweak too many of the table and query names because there are a number of reports in the live database that all depend on the naming conventions originally setup and, quite honestly, I just don't have the time to go back through it all right now.

As far as the relationship function, my example database probably didn't have the best test data in it. The relationship ID is used in situations where a customer has multiple businesses, so each business has its own Tax ID. They have an overarching relationship that ties that customer and all his/her business entities together. That's why that piece is important. It's only one customer and therefore only one lender.

I will definitely check out the filter information you mentioned above. Obviously I want to make sure that's working correctly. Thanks for all your timely advice!