Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autopopulate form? (Office XP)

    Is there a way to autopopulate a form to update it to ever-changing data? Instance: One day, I have one record with a column containing the info: CC. Next day I have 8 records with the following; 2 with CC, 4 with NA, 1 with MM, and 1 with NC. I want my form to count those values, as well as saying what they are. What I'm trying to do is make a report that I can open and it will count all those and I can print it easily.....anyone understand the 'jist' of my question?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autopopulate form? (Office XP)

    Investigate the concept of Totals queries. They are used to aggregate data: records are grouped on the unique values of one or more fields, and you can count, sum, average etc. other data.
    - Create a query based on your table.
    - Change it to a Totals query by selecting View | Totals or by clicking the Totals button on the toolbar.
    - Add the field containing CC etc. to the query grid, and leave the Total option unchanged (Group By is the default)
    - Add an arbitrary field, and set its Total option to Count.
    - Switch to datasheet view to inspect the result.

    You can base a form and/or a report on this query. Note: because the query aggregates data, neither the query nor a form based on it are updatable.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autopopulate form? (Office XP)

    So to update the info, what would I have to do? Recreate the query?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autopopulate form? (Office XP)

    What exactly do you want? See only the data for today?

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autopopulate form? (Office XP)

    I'd like to see info current as of when I run the query/form/macro/whatever I have to use.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autopopulate form? (Office XP)

    Each time you open a query, it reads the records, so it reflects the current situation. The same holds for a form or report based on a table or query.

    But perhaps you mean something else. If so, please try to be specific.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autopopulate form? (Office XP)

    HansV:
    :::You can base a form and/or a report on this query. Note: because the query aggregates data, neither the query nor a form based on it are updatable.::::

    That's why I'm asking about the update thing....you said in an earlier post that they're not updatable...maybe I was mislead or I misread it...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autopopulate form? (Office XP)

    By updatable, I meant that the user cannot edit values, add new records or delete existing records in such a query or form. But the data that are displayed will be up-to-date each time you open the query, form or report.

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autopopulate form? (Office XP)

    So what if on day one I have CC, and day two I have MM and NA but not CC? Then would the CC go away and the MM and NA show up? Another question - Is there a way to put a filter on a form?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autopopulate form? (Office XP)

    That depends on how you populate your table. Does the CC from the first day get deleted on the second day? If so, it wouldn't show up in the query on the second day. But if the records accumulate, you'd see the records for day one and day two. If you want to see only the records for the current day, you must use a where-condition - that's why I asked if you only wanted to see the records for today, a few replies up in this thread.

    Yes, there are several ways of putting a filter on a form. Look at Records | Filter. The submenu lists four options: Filter by Selection, Filter Excluding Selection, Filter by Form and Advanced Filter/Sort... Look up Filters, Create Filters in the online help for more info.

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autopopulate form? (Office XP)

    You've answered all my questions - yet again - , thank you!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autopopulate form? (Office XP)

    I miss-spoke. I meant filter a report....
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autopopulate form? (Office XP)

    There are several possibilities:
    <UL><LI>You can use a quert as Record Source of the report, and put criteria in the query. These criteria can be dynamic:

    <UL><LI>You can use a parameter prompt such as [Which Country] for a Country field; when you open the report, you will be asked to enter a country.
    <LI>You can also refer to a text box or other control on a form, e.g. [Forms]![frmSelect]![txtCountry].[/list]<LI>You can pass a where-condition to a report if you open it using code, for example from a command button on a form:

    DoCmd.OpenReport "MyReport", acViewPreview, , "Year = " & Me.txtYear

    <LI>You can set and remove a filter for an already open report in VBA code. See ACC2000: How to Filter a Report from a Pop-Up Form (applies to other versions than Access 2000 too.)[/list]There is no interactive filter option for reports, the way there is for forms. The last option mentioned above simulates it.

Posting Permissions

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