Sub Forms
in [Getting Started]
|
From: Ray C on 18 May 2010 16:21 I Have two Tables. the first holds various information with it's own Primary Key and also a Supplier ID "SW_Opp" that identifies a Supplier. The second "Suppliers" Table holds the "SupplierNo" number as the primary key and lists the "SupplierName" plus other info about the supplier. The first table "SW_Opp" is the same as the second table "SupplierID" and they are linked in the Relationship. I have a Sub Form that has its record source from the First Table, so all the Text boxes have their control Souce based on that Table. One of the Text Boxes in that sub form is called "SW_OPp and returns the number of the supplierlinked to that particular record in the first table. What i want to do is to have the supplier Name (held in the Suppliers Table) appear in the sub form depending on the Supplier number held in first table. I thought of using an unbound List Box and have tried to set the Row Source to get the result I want. I have tried many combinations but they all come up with an error. The one that I thought should work is as follows :- SELECT tbl_Suppliers.SupplierName FROM tbl_Suppliers WHERE (((tbl_Suppliers.SupplierNo)=[sub_frm_StoreAddressExtra].[SW_Opp])); It still comes up with an error and I must be doing something fundamentally wrong. Any Help please? RayC
From: KenSheridan via AccessMonster.com on 19 May 2010 14:14 Simply base the subform on a query which joins the two tables and include the SupplierName in the query's columns. You can then include a text box control in the subform bound to the SupplierName column. To prevent users trying to edit the name set the control's Locked property to True (Yes) and its Enabled property to False(No). The reason for the error in your query BTW was that you cannot reference a subform by the name of the underlying form object; it has to be referenced via the Form property of the subform control in the parent form. As the query is the RowSource of a control in the subform, however, you can simply reference the Form property, Form!SW_Opp without the need to qualify it by the name of the parent form's subform control. Ken Sheridan Stafford, England Ray C wrote: >I Have two Tables. the first holds various information with it's own Primary >Key and also a Supplier ID "SW_Opp" that identifies a Supplier. The second >"Suppliers" Table holds the "SupplierNo" number as the primary key and lists >the "SupplierName" plus other info about the supplier. The first table >"SW_Opp" is the same as the second table "SupplierID" and they are linked in >the Relationship. >I have a Sub Form that has its record source from the First Table, so all >the Text boxes have their control Souce based on that Table. One of the Text >Boxes in that sub form is called "SW_OPp and returns the number of the >supplierlinked to that particular record in the first table. What i want to >do is to have the supplier Name (held in the Suppliers Table) appear in the >sub form depending on the Supplier number held in first table. I thought of >using an unbound List Box and have tried to set the Row Source to get the >result I want. >I have tried many combinations but they all come up with an error. The one >that I thought should work is as follows :- >SELECT tbl_Suppliers.SupplierName FROM tbl_Suppliers WHERE >(((tbl_Suppliers.SupplierNo)=[sub_frm_StoreAddressExtra].[SW_Opp])); > >It still comes up with an error and I must be doing something fundamentally >wrong. > >Any Help please? > >RayC -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1
From: Ray C on 22 May 2010 07:02 Hi Ken Thank you so much for coming back to me. I have tried and tried to understand and implement both your alternatives but neither to any avail. First of all, i am an absolute beginer with Accesss and I am begining to think that I have some fundamental misunderstandings about how Access works. I may not have explained the way I have things set up too well to you. I have a "frm_Main" and that form has 4 unbound "Find Combo Boxes" that are used to search the various tables. it also has 2 "unbound" Sub Form controls that are used to display the information that is requested by the user and held in diffrent "Sub Forms that are based on the table in which the information is held. e.g. if the User wants to view Supplier names and address information that is held in the "suppliers" table, they select Suppliers from the appropriate combo box in The "Frm_Main" and this populates the appropriate "Find" Combo's with options available to that particular request (supplier name, Supplier ID, Contract No etc). The selection of the correct record from one of these find boxes will search the Table and set the Record with a Bookmark. The program then sets the Source Object of the Sub Form Control as the appropriate Sub Form, make it visible and that is job done. All that works OK other than the situation I was trying to resolve when I originaly made the enquiry. One of the Tables (the Customers Table) has 3 different fields that hold the Supplier Primary Key number from the Suppliers Table as an indication of the supplier that provides that particular service. this could be any combination of one supplier that supplies all 3 services; through to the different suppliers that supply the 3 different services. If the Sub Form is based on the Customers table, all that will be shown is the Supplier Number that is held in the Customer Table. I wanted to use that number to Look up the supplier name. (e.g. I wanted Supplier number 254 held in the appropriate Field of the Customers Table to Look up the Number (primary key) in the "Suppliers" Table and return the name of the supplier associated with that record. I did this with a List Box where the Row Source was set with the statement in the message below. That did not work as the Lisy Box always remained Blank. I changed the line to simply reference the Form Property with Form!SW_Opp but that did not work. I changed the Sub Form to be based on a Query as you suggest but that does not work as there seems to be nothing to link the seloection of the Customer that is selected with its information shown in the first Sub form Control (based on the Customer Table) with the second Sub form that is based on the query. Also, by using the query I can generate the Text Box that controls the Name of the Supplier for one of the 3 (Potentially) different suppliers held in the Customers Table but I acn not generate a Text Box that will display the names of the other two. Surely this information has to be displayed by a Lookup? Sorry this is so long. but any help will be usefull. Thanks Ray C "KenSheridan via AccessMonster.com" wrote: > Simply base the subform on a query which joins the two tables and include the > SupplierName in the query's columns. You can then include a text box control > in the subform bound to the SupplierName column. To prevent users trying to > edit the name set the control's Locked property to True (Yes) and its Enabled > property to False(No). > > The reason for the error in your query BTW was that you cannot reference a > subform by the name of the underlying form object; it has to be referenced > via the Form property of the subform control in the parent form. As the > query is the RowSource of a control in the subform, however, you can simply > reference the Form property, Form!SW_Opp without the need to qualify it by > the name of the parent form's subform control. > > Ken Sheridan > Stafford, England > > Ray C wrote: > >I Have two Tables. the first holds various information with it's own Primary > >Key and also a Supplier ID "SW_Opp" that identifies a Supplier. The second > >"Suppliers" Table holds the "SupplierNo" number as the primary key and lists > >the "SupplierName" plus other info about the supplier. The first table > >"SW_Opp" is the same as the second table "SupplierID" and they are linked in > >the Relationship. > >I have a Sub Form that has its record source from the First Table, so all > >the Text boxes have their control Souce based on that Table. One of the Text > >Boxes in that sub form is called "SW_OPp and returns the number of the > >supplierlinked to that particular record in the first table. What i want to > >do is to have the supplier Name (held in the Suppliers Table) appear in the > >sub form depending on the Supplier number held in first table. I thought of > >using an unbound List Box and have tried to set the Row Source to get the > >result I want. > >I have tried many combinations but they all come up with an error. The one > >that I thought should work is as follows :- > >SELECT tbl_Suppliers.SupplierName FROM tbl_Suppliers WHERE > >(((tbl_Suppliers.SupplierNo)=[sub_frm_StoreAddressExtra].[SW_Opp])); > > > >It still comes up with an error and I must be doing something fundamentally > >wrong. > > > >Any Help please? > > > >RayC > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1 > > . >
From: KenSheridan via AccessMonster.com on 22 May 2010 08:25 Ray: I think the pertinent point is: "If the Sub Form is based on the Customers table, all that will be shown is the Supplier Number that is held in the Customer Table" By making the control in the subform bound to the field containing the Supplier Number a combo box, and hiding the bound column, you can show the supplier name from another column. To do this set up the combo box as follows: RowSource: SELECT [Supplier Number], [Supplier Name] FROM [Suppliers] ORDER BY [Supplier Name]; BoundColumn: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert the last one. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. This is the standard way of showing meaningful data form a column of a referenced table where the keys are arbitrary values, usually numeric. Although the value of the control is the number, what you see is the corresponding text value from the Supplier Name column in the Suppliers table. As it's a combo box, selecting another supplier from the drop down list would change the value of the bound Supplier Number column in the Customers table of course if the subform is updatable. This can be prevented by setting the combo box's Locked property to True (Yes) and its Enabled property to False (No) as I described earlier. You can extend this if necessary to show other data from the Suppliers table in the subform by including the relevant columns in the combo box's RowSource and referencing the columns in an unbound text box in the subform; say for instance you also wanted to show data from a column Supplier Address you'd set up the combo box like this: RowSource: SELECT [Supplier Number], [Supplier Address], [Supplier Name] FROM [Suppliers] ORDER BY [Supplier Name]; BoundColumn: 1 ColumnCount: 3 ColumnWidths: 0cm;0cm;8cm Then add an unbound text box to the subform, with a ControlSource property of: =cboSupplier.Column(1) where cboSupplier is the name of the combo box. The column property is zero- based, so Column(1) is the second column, Supplier Address. Ken Sheridan Stafford, England Ray C wrote: >Hi Ken >Thank you so much for coming back to me. I have tried and tried to >understand and implement both your alternatives but neither to any avail. >First of all, i am an absolute beginer with Accesss and I am begining to >think that I have some fundamental misunderstandings about how Access works. >I may not have explained the way I have things set up too well to you. I >have a "frm_Main" and that form has 4 unbound "Find Combo Boxes" that are >used to search the various tables. it also has 2 "unbound" Sub Form controls >that are used to display the information that is requested by the user and >held in diffrent "Sub Forms that are based on the table in which the >information is held. >e.g. if the User wants to view Supplier names and address information that >is held in the "suppliers" table, they select Suppliers from the appropriate >combo box in The "Frm_Main" and this populates the appropriate "Find" >Combo's with options available to that particular request (supplier name, >Supplier ID, Contract No etc). The selection of the correct record from one >of these find boxes will search the Table and set the Record with a Bookmark. >The program then sets the Source Object of the Sub Form Control as the >appropriate Sub Form, make it visible and that is job done. >All that works OK other than the situation I was trying to resolve when I >originaly made the enquiry. One of the Tables (the Customers Table) has 3 >different fields that hold the Supplier Primary Key number from the Suppliers >Table as an indication of the supplier that provides that particular service. >this could be any combination of one supplier that supplies all 3 services; >through to the different suppliers that supply the 3 different services. If >the Sub Form is based on the Customers table, all that will be shown is the >Supplier Number that is held in the Customer Table. I wanted to use that >number to Look up the supplier name. (e.g. I wanted Supplier number 254 held >in the appropriate Field of the Customers Table to Look up the Number >(primary key) in the "Suppliers" Table and return the name of the supplier >associated with that record. I did this with a List Box where the Row Source >was set with the statement in the message below. >That did not work as the Lisy Box always remained Blank. I changed the line >to simply reference the Form Property with Form!SW_Opp but that did not work. >I changed the Sub Form to be based on a Query as you suggest but that does >not work as there seems to be nothing to link the seloection of the Customer >that is selected with its information shown in the first Sub form Control >(based on the Customer Table) with the second Sub form that is based on the >query. >Also, by using the query I can generate the Text Box that controls the Name >of the Supplier for one of the 3 (Potentially) different suppliers held in >the Customers Table but I acn not generate a Text Box that will display the >names of the other two. Surely this information has to be displayed by a >Lookup? >Sorry this is so long. but any help will be usefull. > >Thanks Ray C > >> Simply base the subform on a query which joins the two tables and include the >> SupplierName in the query's columns. You can then include a text box control >[quoted text clipped - 37 lines] >> > >> >RayC -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1
From: Ray C on 23 May 2010 22:35 Hi Ken I originally used a "List Box" because I wanted the box to look like a normal "Text Box" even though it obtained it's data from a diferent form. I thought that List boxes and Text boxes worked nearly the same way but it seems not. However, I have changed the box to a Combo box and I now have the rather ugly combo box selection arrow. It does work but is quite useless in that the Combo is Blank amd I have to klick the Combo select arrow to get the box to show it's drop down containing the Supplier name. I then select the dropdown to have the supplier name showing in the Combo itself. As this is just supposed to be a dispay of the information, that process rather defeats the object of the exercise. Do I need to execute a requery or something? I have tried to show a text box that is set up as you also suggest but that displays nothing either. Thank you Ray C "KenSheridan via AccessMonster.com" wrote: > Ray: > > I think the pertinent point is: > > "If the Sub Form is based on the Customers table, all that will be shown is > the Supplier Number that is held in the Customer Table" > > By making the control in the subform bound to the field containing the > Supplier Number a combo box, and hiding the bound column, you can show the > supplier name from another column. To do this set up the combo box as > follows: > > RowSource: SELECT [Supplier Number], [Supplier Name] FROM [Suppliers] > ORDER BY [Supplier Name]; > > BoundColumn: 1 > ColumnCount: 2 > ColumnWidths: 0cm;8cm > > If your units of measurement are imperial rather than metric Access will > automatically convert the last one. The important thing is that the first > dimension is zero to hide the first column and that the second is at least as > wide as the combo box. > > This is the standard way of showing meaningful data form a column of a > referenced table where the keys are arbitrary values, usually numeric. > Although the value of the control is the number, what you see is the > corresponding text value from the Supplier Name column in the Suppliers table. > As it's a combo box, selecting another supplier from the drop down list would > change the value of the bound Supplier Number column in the Customers table > of course if the subform is updatable. This can be prevented by setting the > combo box's Locked property to True (Yes) and its Enabled property to False > (No) as I described earlier. > > You can extend this if necessary to show other data from the Suppliers table > in the subform by including the relevant columns in the combo box's RowSource > and referencing the columns in an unbound text box in the subform; say for > instance you also wanted to show data from a column Supplier Address you'd > set up the combo box like this: > > RowSource: SELECT [Supplier Number], [Supplier Address], [Supplier Name] > FROM [Suppliers] ORDER BY [Supplier Name]; > > BoundColumn: 1 > ColumnCount: 3 > ColumnWidths: 0cm;0cm;8cm > > Then add an unbound text box to the subform, with a ControlSource property of: > > > =cboSupplier.Column(1) > > where cboSupplier is the name of the combo box. The column property is zero- > based, so Column(1) is the second column, Supplier Address. > > Ken Sheridan > Stafford, England > > Ray C wrote: > >Hi Ken > >Thank you so much for coming back to me. I have tried and tried to > >understand and implement both your alternatives but neither to any avail. > >First of all, i am an absolute beginer with Accesss and I am begining to > >think that I have some fundamental misunderstandings about how Access works. > >I may not have explained the way I have things set up too well to you. I > >have a "frm_Main" and that form has 4 unbound "Find Combo Boxes" that are > >used to search the various tables. it also has 2 "unbound" Sub Form controls > >that are used to display the information that is requested by the user and > >held in diffrent "Sub Forms that are based on the table in which the > >information is held. > >e.g. if the User wants to view Supplier names and address information that > >is held in the "suppliers" table, they select Suppliers from the appropriate > >combo box in The "Frm_Main" and this populates the appropriate "Find" > >Combo's with options available to that particular request (supplier name, > >Supplier ID, Contract No etc). The selection of the correct record from one > >of these find boxes will search the Table and set the Record with a Bookmark. > >The program then sets the Source Object of the Sub Form Control as the > >appropriate Sub Form, make it visible and that is job done. > >All that works OK other than the situation I was trying to resolve when I > >originaly made the enquiry. One of the Tables (the Customers Table) has 3 > >different fields that hold the Supplier Primary Key number from the Suppliers > >Table as an indication of the supplier that provides that particular service. > >this could be any combination of one supplier that supplies all 3 services; > >through to the different suppliers that supply the 3 different services. If > >the Sub Form is based on the Customers table, all that will be shown is the > >Supplier Number that is held in the Customer Table. I wanted to use that > >number to Look up the supplier name. (e.g. I wanted Supplier number 254 held > >in the appropriate Field of the Customers Table to Look up the Number > >(primary key) in the "Suppliers" Table and return the name of the supplier > >associated with that record. I did this with a List Box where the Row Source > >was set with the statement in the message below. > >That did not work as the Lisy Box always remained Blank. I changed the line > >to simply reference the Form Property with Form!SW_Opp but that did not work. > >I changed the Sub Form to be based on a Query as you suggest but that does > >not work as there seems to be nothing to link the seloection of the Customer > >that is selected with its information shown in the first Sub form Control > >(based on the Customer Table) with the second Sub form that is based on the > >query. > >Also, by using the query I can generate the Text Box that controls the Name > >of the Supplier for one of the 3 (Potentially) different suppliers held in > >the Customers Table but I acn not generate a Text Box that will display the > >names of the other two. Surely this information has to be displayed by a > >Lookup? > >Sorry this is so long. but any help will be usefull. > > > >Thanks Ray C > > > >> Simply base the subform on a query which joins the two tables and include the > >> SupplierName in the query's columns. You can then include a text box control > >[quoted text clipped - 37 lines] > >> > > >> >RayC > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1 > > . >
|
Next
|
Last
Pages: 1 2 3 4 Prev: Rounding Time Next: Crunch numbers with MS Access: is this possible? |