Prev: Fields Positioned At Bottom of the Form
Next: The disappearing form
From: atledreier on 26 Apr 2010 05:07
I have a master list of tags, with a related table of documents for
each tag.

I have a query that select relevant tags based on 'owners', and would
like a form that present all the relevant tags on the left, and the
relevant documents for the selected tag on the right.

TBL_Tag:
*TAG
Description
Area
misc....

TBL_DocRef:
*DocNo
*GenId

TAG and GenId are the related fields.

From: BruceM via AccessMonster.com on 26 Apr 2010 08:18
First, don't use the word Tag as a field or control name. It is a reserved
word (it is a property of controls and forms), so using it as a field name
will likely lead to problems unless you *always* surround the name with
square brackets.

Where do owners fit into the table hierarchy? You say you select tags based
on owners, but you do not show how a tag is associated with an owner.

You should be able to do what you want with a form and subform, but the
details are not clear enough that I can offer a specific suggestion.

atledreier wrote:
>I have a master list of tags, with a related table of documents for
>each tag.
>
>I have a query that select relevant tags based on 'owners', and would
>like a form that present all the relevant tags on the left, and the
>relevant documents for the selected tag on the right.
>
>TBL_Tag:
>*TAG
>Description
>Area
>misc....
>
>TBL_DocRef:
>*DocNo
>*GenId
>
>TAG and GenId are the related fields.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1

From: atledreier on 26 Apr 2010 08:40
The ownership to tags is based on [area]. I have a table

TBL_Users_Area:
User
Area

A query returning the Documents and relevant tags:
(Some fields are named different from the earlier post, I translated
the field names for it to make sense to non-norwegian people :-) )

SELECT Doc_ref.Docid, Doc_ref.Genid_1, Doc_ref.Type
FROM (TBL_Bruker_Modul INNER JOIN Document ON TBL_Bruker_Modul.Modul =
Document.Area) INNER JOIN Doc_ref ON Document.Docid = Doc_ref.Docid
WHERE (((TBL_Bruker_Modul.Login)=getlogin()))
ORDER BY Doc_ref.Docid;

Another query returning documents and their descriptions only.

SELECT Document.Docid, Document.Description
FROM TBL_Bruker_Modul INNER JOIN Document ON TBL_Bruker_Modul.Modul =
Document.Area
GROUP BY Document.Docid, Document.Description, TBL_Bruker_Modul.Login
HAVING (((TBL_Bruker_Modul.Login)=Getlogin()));


The Getlogin function:

Public Function getlogin()
getlogin = Login
End Function

'Login' is a public variable set when the user start the application.

The 'Tag' field is set in stone, sadly. The data structure is set by
my client, since the underlying database is imported to a 3rd party
system on their end.


On 26 apr, 14:18, "BruceM via AccessMonster.com" <u54429(a)uwe> wrote:
> First, don't use the word Tag as a field or control name.  It is a reserved
> word (it is a property of controls and forms), so using it as a field name
> will likely lead to problems unless you *always* surround the name with
> square brackets.
>
> Where do owners fit into the table hierarchy?  You say you select tags based
> on owners, but you do not show how a tag is associated with an owner.
>
> You should be able to do what you want with a form and subform, but the
> details are not clear enough that I can offer a specific suggestion.
>
>
>
>
>
> atledreier wrote:
> >I have a master list of tags, with a related table of documents for
> >each tag.
>
> >I have a query that select relevant tags based on 'owners', and would
> >like a form that present all the relevant tags on the left, and the
> >relevant documents for the selected tag on the right.
>
> >TBL_Tag:
> >*TAG
> >Description
> >Area
> >misc....
>
> >TBL_DocRef:
> >*DocNo
> >*GenId
>
> >TAG and GenId are the related fields.
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1

From: BruceM via AccessMonster.com on 26 Apr 2010 10:45
Type and Document are also reserved words. For more information, and a
utility to find such problems:
http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot change the names you need to be sure they are always in square
brackets.

I still do not understand the table structure. However, I doubt you can
display the information in the desired format on a form based on a single
query. Subforms would work better for displaying related information. If a
tag record may have many related records, use a form based on the Tag table,
and a subform based on the Document table. If a user may have many related
Tag records, the User table is the source for the main form, with a subform
based on the Tag table, which in turn has its own subform based on the
Document table.


atledreier wrote:
>The ownership to tags is based on [area]. I have a table
>
>TBL_Users_Area:
>User
>Area
>
>A query returning the Documents and relevant tags:
>(Some fields are named different from the earlier post, I translated
>the field names for it to make sense to non-norwegian people :-) )
>
>SELECT Doc_ref.Docid, Doc_ref.Genid_1, Doc_ref.Type
>FROM (TBL_Bruker_Modul INNER JOIN Document ON TBL_Bruker_Modul.Modul =
>Document.Area) INNER JOIN Doc_ref ON Document.Docid = Doc_ref.Docid
>WHERE (((TBL_Bruker_Modul.Login)=getlogin()))
>ORDER BY Doc_ref.Docid;
>
>Another query returning documents and their descriptions only.
>
>SELECT Document.Docid, Document.Description
>FROM TBL_Bruker_Modul INNER JOIN Document ON TBL_Bruker_Modul.Modul =
>Document.Area
>GROUP BY Document.Docid, Document.Description, TBL_Bruker_Modul.Login
>HAVING (((TBL_Bruker_Modul.Login)=Getlogin()));
>
>The Getlogin function:
>
>Public Function getlogin()
> getlogin = Login
>End Function
>
>'Login' is a public variable set when the user start the application.
>
>The 'Tag' field is set in stone, sadly. The data structure is set by
>my client, since the underlying database is imported to a 3rd party
>system on their end.
>
>> First, don't use the word Tag as a field or control name.  It is a reserved
>> word (it is a property of controls and forms), so using it as a field name
>[quoted text clipped - 28 lines]
>> --
>> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1

From: John W. Vinson on 26 Apr 2010 12:19
On Mon, 26 Apr 2010 02:07:57 -0700 (PDT), atledreier <atledreier(a)gmail.com>
wrote:

>I have a master list of tags, with a related table of documents for
>each tag.
>
>I have a query that select relevant tags based on 'owners', and would
>like a form that present all the relevant tags on the left, and the
>relevant documents for the selected tag on the right.
>
>TBL_Tag:
>*TAG
>Description
>Area
>misc....
>
>TBL_DocRef:
>*DocNo
>*GenId
>
>TAG and GenId are the related fields.

You'll need to take action on the reserved words, but if you want one
continuous subform dependent on another contionuous subform, you can use a
"relay" textbox. Put a textbox on the mainform named txtRelay with a Control
Source such as

=subformTags.[TAG]

The second subform would be based on tbl_DocRef, and would use GenID as the
Child Link Field and txtRelay as the Master Link Field. The subform wizard
won't offer this as an option but you can just type the name of the control
into the property.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Fields Positioned At Bottom of the Form
Next: The disappearing form