 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / December 2007subform
A relationship between the "Financial Year" Table (one side) and "Customers" Table (many side) has been created. The record source for the main form is "Financial Year" and that for the subform is "Customers". From Wizard was used to create the form which gives a textbox that display the "Financial Year". The problem here is the textbox only display one financial year and the subform also lists all the record in that year. To view the next financial year record, I have to click on the "next" button from navigation bar below. What I prefer is to have all the financial year listed in the textbox so that when user clicks on any year, the record will automatically be displayed in the subform. I've checked that the control source of the textbox is "financial year" and the table is also set on "Lookup column". I guessed that the properties for the textbox should have a row source but it isn't there. Does anyone know how to solve this problem ? Hi, I'm a little surprised that you seem to be complaining that your subform contains many records. That's what you want, isn't - all customer records that match the financial year on your main form? Be that as it may your principal concern is how to get a list box to display all financial years and thus avoid using the navigation buttons to step through them. From your description your main form contains a text box displaying the financial year. A text box is no good for your purpose and you need to change this to a list box. Enter design view for your main form and right-click on the text box. Select 'Change to' and then select 'List Box' from the sub menu. Now you need to specify some properties of your list box. While in design view make sure the Property Sheet is displayed. If it's not click on the property sheet button at the right-hand end of the Access 2007 ribbon or the Property Sheet button on the toolbar in earlier Access versions. With the list box selected click on the 'Data' tab of the Property Sheet. Make sure the 'Row Source Type' is 'Table/Query' and then make an entry in 'Row Source.' The nature of the entry in 'Row Source' depends upon your financial year table but I suggest some SQL will serve your purpose such as: SELECT [FinYear] FROM tblFinYear ORDER BY [FinYear] DESC Substitute your own names. Browse through the other properties. Place your cursor in any property and then press F1 to invoke help and get a description of what the property does. This should do it. You haven't changed the name of your list (formerly text) box so the master-child field links should all still work. Rod And also.... I seems that you have bound the text box to a field of a record source for the Form itself. You need to empty the field in the properties data tab that shows Record Source. The form is then no longer bound to the fiscal year table. If you do not do that then you will be changing the fiscal year of the record you are sitting on whenever you "Select" a new year to view. Ron Sorry for the drag in this problem as I was dealing with other urgent tasks. I've done what was advised previously but the record in the sub-form does not refresh when I change the [Financial Year] value in the listbox. The record source for the sub-form is the "Customers" Table while the Filter property is set as the [Financial Year] and Order By "CustomerID". Regards > And also.... > [quoted text clipped - 9 lines] > > Ron Hi, Remove all filters from the form you use as a subform. When displayed in isolation the (sub)form should show every customer record for every financial year. Now in design view for the main form select the subform control and find in the Properties sheet the entries for Link Master Fields and Link Child Fields (they are on the Data tab). In the Link Master Fields enter the name of the main form list box; in the Link Child Fields enter the name of the text box (?) containing the financial year. It should now all work. Rod |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.