subform

[lhtan123]

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 ?

 

[Rod Plastow]

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

 

[Ron2006]

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

 

[lhtan123]

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

 

[Rod Plastow]

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

 

[lhtan123]

Hi,

It works now. Thanks.