A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Multiple Many-to-Many relationships



 
 
Thread Tools Display Modes
  #11  
Old October 28th, 2009, 08:43 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.