User Profile

INJacqui · Jul 9 '18, 07:34 PM

I'm still working on all the error handling, but I will once I get it solid.

INJacqui · Jul 9 '18, 03:51 PM

I wanted to thank you all for your help. I am much further along in my understanding of SQL.

I also wanted to give an update. I decided to try another route using recordset filters instead, and it is working. I don't know that it is the most elegant solution, but it's providing the results I want.

Thanks!!!!

INJacqui · Jul 5 '18, 01:45 PM

SELECT tbl_Parts.PartI D FROM (SELECT join_ParttoTag. PartID, Count(join_Part toTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag. PartID IN (10)) GROUP BY join_ParttoTag. PartID HAVING (Count(join_Par ttoTag.PartID)= 1) AS Cat1 INNER JOIN ((SELECT join_ParttoTag. PartID, Count(join_Part toTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag. PartID IN ()) GROUP BY join_ParttoTag. PartID HAVING (Count(join_Par ttoTag.PartID)= 0) AS Cat2 INNER JOIN tbl_Parts...

INJacqui · Jul 5 '18, 01:26 PM

AHA! I should have caught that one. However, now we've got:

Run-time error 3075:

Syntax error (missing operator) in query expression '(Count(join_Pa rttoTag.PartID) =1) AS Cat1
INNER JOIN ((SELECT join_ParttoTag. PartID, Count(join_Part toTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag. PartID IN ()) "GROUP BY join_ParttoTag. PartID HAVING (Count(join_Par ttoTag.PartID)= 1) AS Cat2 INNER JOIN tbl_Parts ON...

INJacqui · Jul 5 '18, 12:48 PM

Hey, twinnyfo,

I was trying to see if I could psych it out first. I really am trying to improve my SQL skills. I've put it off long enough :)

Anyway, the error I am receiving is

Run-time error 3075:
Extra ) in query expression 'Count(join_Par ttoTag.PartID)= 1) AS Cat1
INNER JOIN ((SELECT join_ParttoTag. PartID, Count(join_Part toTag.PartID) FROM join_ParttoTag WHERE join_ParttoTag. PartID IN ()) "GROUP...

INJacqui · Jul 4 '18, 03:39 PM

Thanks for the new approach. I'm still having some trouble with the SQL statement, but I'm working through it a bit at a time. I guess it's about time for me to really understand SQL :)

INJacqui · Jul 2 '18, 01:28 PM

Sorry, I've been away from the project for a couple of days.

Here's the code that I copied from Twinny's suggestion. Again, it runs until I apply the filter.

Code:

Option Compare Database
Option Explicit

Private strFilter As String

Private Sub lstEngineType_AfterUpdate()
On Error GoTo EH

    strFilter = ""
    
    CheckListBoxes
    
    With

...

INJacqui · Jun 29 '18, 01:44 PM

Rabbit - thanks for the clarification. I thought that was what it was doing, but it's always good to get confirmation.

I am using twinny's code exactly as written, and it runs until line 36

.FilterOn = True

Then I get the following error:
Run-time error '2176'
The setting for this property is too long.

Should strFilter set in line 106 actually be the list of PartIDs that are the...

INJacqui · Jun 28 '18, 01:47 PM

OK...I used your suggestions to build individual queries outside of VBA. I believe I know understand what you are doing with the COUNT function. I've got some good ideas of how to move forward.

I very much appreciate all the help! I'll get back to you and let you know if I'm able to get it working or if I need more help.

Thanks again.

INJacqui · Jun 28 '18, 01:13 PM

Twinnyfo - thanks for the direction. I was thinking about it in the shower last night and realized that the code you provided would direct me as to how to pull the tagIDs from the string. The one thing I am confused about in your code is the Count. What is the count doing?

INJacqui · Jun 27 '18, 11:44 PM

Thanks so much for all the tips so far. I'll dive into them and see what I can figure out.

To clarify a little more, each part can have any number of Tags. The Tag Categories exist just to make searching for parts with certain tags a little easier for the end user. Rather than having to cycle though every tag, they can look at the tags by category.

I am able to get the results I want by building queries for each category...

INJacqui · Jun 27 '18, 12:10 AM

Sorry that I did not provide enough information. This is my first time posting in a forum like this, and I was trying to only provide the info pertaining to my specific question.

I also apologize if it appeared that I was wanting someone to provide the answer and the code to go along. I've been trying to figure this out for a couple of days and haven't been able to find anything that seems to be what I need. I'm really just looking for...

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Multiple search criteria on one junction table field