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 |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|