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 » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Two subforms, one based on selection in the other



 
 
Thread Tools Display Modes
  #1  
Old April 26th, 2010, 10:07 AM posted to microsoft.public.access.forms
atledreier[_2_]
external usenet poster
 
Posts: 22
Default Two subforms, one based on selection in the other

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.

  #2  
Old April 26th, 2010, 01:18 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Two subforms, one based on selection in the other

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/For...forms/201004/1

  #3  
Old April 26th, 2010, 01:40 PM posted to microsoft.public.access.forms
atledreier[_2_]
external usenet poster
 
Posts: 22
Default Two subforms, one based on selection in the other

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@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


  #4  
Old April 26th, 2010, 03:45 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Two subforms, one based on selection in the other

Type and Document are also reserved words. For more information, and a
utility to find such problems:
http://www.allenbrowne.com/Ap****ueBadWord.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/For...forms/201004/1

  #5  
Old April 26th, 2010, 05:19 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Two subforms, one based on selection in the other

On Mon, 26 Apr 2010 02:07:57 -0700 (PDT), 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.


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]
 




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 11:02 AM.


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