Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Question Unanswered: Combo filtered to Parent Form

    Is there a possibility to filter Combo in Child form to have only that number of choices that Parent form allows?

    Example:
    Parent form (Single) = Companies;
    Medium Form (Single) = Time periods for money reports for departments of companies;
    Child form (Continous) = Accounts for Companies Departments Money Reports, based on Time Periods; This form contains start, input, output, end period money report, with totals. Here in this, child form I want to put Combo - with possibility to choose department of the company for the new account record, but filtered to the company from Parent form.
    In simple version - there are 4 tables Companies, Periods, Accounts (related to Companies), and Money values - related to Accounts, and Periods).

    Any idea? Or example?

  2. #2
    Join Date
    Mar 2004
    Location
    Brighton, UK
    Posts
    25
    You should be able to set the Rowsource property of the combo in the Child form to do this.

    Assuming you are basing the rowsource on a query, in the query you need to include a column for eg CompanyID and set the criteria for this column to Forms!ParentFormName!CompanyID. Presumably you have a CompanyID field or equivalent in your Departments table which links each department record to a company - this is the field in the query you need to include and set the criteria for.

    You may also need to write a bit of code to requery the combo when you change records in the parent form, so that the list reflects the new company that is showing... possibly but I'm too hungover right now to know for sure if this is the case!

  3. #3
    Join Date
    Mar 2004
    Posts
    84

    Combos

    There is a lack of examples (in access) for the periodically structured (tables in) databases.

    Do you know a site with good examples for the solution you mentioned? I mean - example with tables, queries, forms, reports. I do have 15 MS Access 2000 Books and I can say that examples are mostly based on the similar directions - how to make simple accountant database, videostore ....

    Thank you!

  4. #4
    Join Date
    Mar 2004
    Location
    Brighton, UK
    Posts
    25
    15 books!!! Phew, plenty of bedtime reading. I assume you're talking about how to structure your tables, and you have a situation with time periods when you say "Periodically structured"?

    Sorry I don't know of any good examples. If this a database you are working on currently I could perhaps offer some advice if you like, if you write a description of what you're trying to achieve and how you're currently going about it....

  5. #5
    Join Date
    Mar 2004
    Posts
    84

    Question Structure:

    I have the database for example:

    Have 6 tables:
    - companies (IDcompany, companyname, company address)
    - fieldoffices (IDfieldoffice, IDcompany, fieldofficename)
    - fieldofficeaccounts(IDfieldofficeaccount, IDfieldoffice, fieldofficeaccountnumber, IDbank, fieldofficeaccountpurpose)
    - fieldofficeaccountsvalues(IDfieldofficeaccountvalu e, IDfieldofficeaccount, IDaccountingperiod, valueperiodstart, valueperiodinput,valueperiodoutput, valueperiodend)
    - accountingperiods(IDaccountingperiod, accountingperiodstart, accountingperiodend)
    - banks (IDbank, bankname)

    One company can have one or more fieldoffices; one fieldoffice can have none, one or more than one account; there is more than one company in the companies table - and all of them have to be in calculation for a accounting period, with accounting values - for example: periodstart, periodinput, periodoutput and calculated periodend).

    I did simplify this because of the space here.

    How to get opportunity to put everything in few forms, subforms, .... Just for data input?

  6. #6
    Join Date
    Mar 2004
    Location
    Brighton, UK
    Posts
    25
    Hmm it's tricky to advise properly because I don't know exactly what you're trying to achieve. I did write an access database ages ago that dealt with that sort of thing. I suppose the first choice you have to make is what is the "master" entity? Is it the company? If so then the main form will hold the company, and perhaps you'd have a subform which holds the FieldOffices, which I gather are what the accounting periods link into.

    Then you have a choice - to have a subform within the field office sub form containing the accounts... sounds pretty messy though because you're going to have another subform in that for accountvalues. So maybe you could have a page in the form for basic company details, a page for listing field offices, then a page for accounts. On the account page you would need an account subform, maybe taking up the whole page, with a subform within that showing the items making up that account - maybe this one could be in datasheet view so all the details can be seen at once.

    I tend to always use command buttons to add records (rather than the * button on the nav bar) as it gives much more control over what the user does. So the user could go to the Accounts tab and click the Add account button which launches a little form allowing the user to select the field office, enter various bits and pieces as you wish and then you can add in the new record using code. The same thing could happen for adding field offices too.

    Is this any help? I hope so, and that I've interpreted your data structure OK. Hope it doesn't sound patronising it's not meant to be!

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •