If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Multiple Many-to-Many relationships
The general idea can be used to build a filter string from a number of text
boxes, combo boxes, etc. Essentially you would build a filter string (strFilter), then do: Me.Filter = strFilter Me.FilterOn = True If you are looking to filter a report based on fields in the main record it is relatively straightforward. For instance, if the aritcles have a Type field, and you are looking for articles in which the Type is Astronomy, your filter string would be something like the following (spaces added for clarity) . I am assuming Type is a string, and that you have selected it from the combo box cboType. This code could be in the Click event of a command button after you have made selections from unbound combo boxes. Dim strType as String strType = Me.cboType strFilter = "[Type] = " " " & strType & " " " " If you are filtering by multiple criteria you need to assemble a string along the lines of: [Type] = "Astronomy" And [DateWritten] #01/01/09# This is the end result that you would see if you did Debug.Print when strFilter is completed. How you get there depends on what exactly you are using as filter criteria (dates, text, numbers, or whatever). One general approach is to add "And" at the end of the string as you add each element, then remove the trailing "And" when the string is complete. For instance, the strFilter expression above may be: strFilter = "[Type] = """ & strType & """ And " When the string is complete: strFilter = Left(strFilter,Len(strFilter) - 5) You may not be searching with all possilble criteria, so you need to test a criteria text box, combo box, etc. for a value before you add it to the string. If it is empty, skip over it. This is all very general, but there may be a lot of moving pieces, and the specifics depend on your exact needs. If you are filtering according to other criteria such as articles by a specific author that relate to a specific project, it gets more complicated. If you are using the form's built-in filtering capabilities I suspect you are not filtering in that way, but I cannot be sure. I will leave it at this for now. My suggestion is to start by building a one- element filter string, then adding another element. The arrangement of quotes can be tricky at first. This article may help with that: http://allenbrowne.com/casu-17.html This article may be of some interest for building a report filter. It does not exactly apply to your situation, but there may be things you can use: http://allenbrowne.com/ser-50.html Meaghan S wrote: Ok, this is the best response by far. There are still a couple of things I would like to ask about/get at. Here is what I want: A database that can be used by the user in two distinct ways. One: To enter data about articles/media Two: To search for articles based on specific criteria entered in by the user. This is what I have: Part One: Forms with subforms showing the article, the many projects it is related to, and the many authors it is related to. I am able to edit Author information by use of a separate form when 'not in list'. This I have used for the data entry portion of the DB, and it works perfectly. (it is in fact what a couple of you have already suggested to me for part 2...) Part Two is what I would like to come back to. "you could use unbound controls on a bound form to filter or redefine the form's recordset." Although I can apply filters using the form when emI/em want to find specific data, not everyone who is going to use this is so access savvy. That is why I am trying to set up a search form, akin to search forms you may find on an electronic journals database. the user, knowing little about access, enters in search criteria, and BAM, a list of related articles appear. So, perhaps due to the limitations of Access, the list (in this case a query, could be a form or even a report based on the query) is read only. Fine. But is there a way to point to the detail of the specific record on my Data Entry Form, directed from the list created by the query? thanks, (I know my tone sounds frustrated, it's just at access, not the help I am receiving. For all of your words and patience, I am grateful) M -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200910/1 |
|
Thread Tools | |
Display Modes | |
|
|