Dynamic Multi Search (1 Viewer)

[Infinite]

Well CJ, That has been something that has annoyed me for quit some time. I would like to thank you VERY much for helping me and answering my question, being patient, correcting my wrongs. Thank you much!

Stop trying to hide what you actually want to do.

I am sorry I was confusing, truly, I am, that was NOT what I was at all trying to do, I was just trying to give you examples. Sorry again if what I have said has confused you. Did not mean for it to look like I was hiding something from you. Thanks once again!

 

[Mihail]

@Infinite
What about this ?

In attachment you have a DB.
Search the word "Smith"

 

[CJ_London]

We got there in the end.

Did not mean for it to look like I was hiding something from you.

there are real benefits in providing things like real field and table names, the full sql you are using, sample data of what you have and what you want to see from the query, and use of correct terminology (is a 'name' a field name or a control name for example) etc

Benefits are
1. one or more of these tell a much clearer story
2. it sometimes uncovers other things like use of reserved words, spaces and non alpha numeric characters in names which can have unexpected results and incorrect solutions are proposed
3. a vague question gets suggestions, not answers
4. you will get a much quicker answer
5. and most importantly you will often be able to simply copy and paste the answer provided

This thread took twenty two posts and five days to answer for a very basic requirement . If you had just provided a copy of your sql, at the beginning, it would have been answered in hours rather than days and probably one post. So an extra couple of minutes on your original thread would have save everyone several hours.

The other thing to bear in mind is that without a clearer explanation

1. many responders will think 'I don't understand the question and I don't have the time to clarify - so I won't bother'
2. as you start to get a reputation for poorly worded questions and not reading the solutions provided properly (this is the second time I have provided you with a correct solution early on and the thread has continued with many more posts and many more days because you hadn't read it properly), responders won't even look, they'll just ignore you

 

[Infinite]

I believe my problem is, I keep thinking that you guys are reading this from my prospective. If I was to read it myself, I would understand it, but, I dont think about someone else reading it, and not understanding it. I need to get better at proof reading, and because im not very good at it, people dont understand my questions (not there fault, all of its mine) so it takes, 22 post and a couple of days. And from now on, If I can remember it, I will post the query SQL for query's, and the data base I am working on for any questions I have to do with forms or tables. Thanks once again!

 

[Infinite]

I have a quick question, when using the search code you gave me, something is not working. I have the items in my form, and I have it so when I double click, it opens up another form to edit it. But, if I have John Doe Smith and I search for Doe, and double click John Doe Smith, it opens up a blank record. But, if I search for John and double click it, it opens up John Doe Smiths record. Any reason why it would be doing this?


FYI: It only does that with a record that has more then 3 names to it. So if I did the same thing with only John Smith, it would work searching for Smith.

 

[CJ_London]

none at all - would need to see sample data, the sql for your form and the code that opens the other form and an explanation of which field is doubleclicked. If it is a combo, would also need to see the combo properties - rowsource, bound column, columncount, column widths

 

[Infinite]

SELECT tblItems.Model, tblItems.Price, tblItems.OnlineShow, tblItems.Category, tblStatus.Status AS StatusID, tblItems.Status, tblItems.FitPerSheet, tblItems.Wholesale, tblItemSizes.ModelWidth, tblItemSizes.ModelHeigth, tblItemSizes.ModelArea, tblItemSizes.ModelPerimiter, tblItems.ModelPicture, tblItemCutOutLength.DetailInch, tblItemCutOutLength.LogoInch, tblItemCutOutLength.TriggerInch, tblItems.[2ndModelNames], tblItems.[3rdModelNames], tblItems.[4thModelNames], tblItems.[5thModelNames], tblItemCutOutLength.TimeToCutOut, tblItems.ModeIID
FROM tblStatus INNER JOIN ((tblItems LEFT JOIN tblItemSizes ON tblItems.ModeIID = tblItemSizes.Model) LEFT JOIN tblItemCutOutLength ON tblItems.ModeIID = tblItemCutOutLength.Model) ON tblStatus.StatusID = tblItems.Status
WHERE (((tblItems.Model) Like [Forms]![frmItems]![txtSearchI] & "*") AND ((tblStatus.Status)=[Forms]![frmItems]![Frame16])) OR (((tblItems.Model) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblItems.Category) Like [Forms]![frmItems]![txtSearchI] & "*") AND ((tblStatus.Status)=[Forms]![frmItems]![Frame16])) OR (((tblItems.Category) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblStatus.Status)=[Forms]![frmItems]![Frame16]) AND ((tblItems.Status) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.Status) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblStatus.Status)=[Forms]![frmItems]![Frame16]) AND ((tblItems.[2ndModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[2ndModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblStatus.Status)=[Forms]![frmItems]![Frame16]) AND ((tblItems.[3rdModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[3rdModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblStatus.Status)=[Forms]![frmItems]![Frame16]) AND ((tblItems.[4thModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[4thModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblStatus.Status)=[Forms]![frmItems]![Frame16]) AND ((tblItems.[5thModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[5thModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblItems.OnlineShow) Like [Forms]![frmItems]![txtSearchI] & "*") AND ((tblStatus.Status)=[Forms]![frmItems]![Frame16])) OR (((tblItems.OnlineShow) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null))
ORDER BY tblItems.Category DESC;

That is the SQL for it. If you need the data base, just say the word.

 

[CJ_London]

OK - just need the items in red.

would need to see sample data, the sql for your form and the code that opens the other form and an explanation of which field is doubleclicked.

if you had used real names rather than 'John Smith', I might have been able to have worked it out but I cannot relate this name to 'model', 'status' etc.

I don't want another 22 posts, either provide the info as requested or start a new thread - and I don't need the db

 

[Infinite]

The code that opens it is a macro. The macro is in the picture.

Just look at the pics, should explain what you want.

sample data

Not sure what you mean by that, sorry

 

[CJ_London]

I think your problem is to do with normalisation and I'm guessing you are using lookups in your table design which could be causing the problem.

I think you would be better off starting a new thread. Sorry I can't help any further

good luck with your project