Autopopulate form? (Office XP)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

So to update the info, what would I have to do? Recreate the query?

Reply | Quote

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

Reply | Quote

I’d like to see info current as of when I run the query/form/macro/whatever I have to use.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

I’d like to see info current as of when I run the query/form/macro/whatever I have to use.

Reply | Quote

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

Reply | Quote

So to update the info, what would I have to do? Recreate the query?

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

You’ve answered all my questions – yet again – , thank you!

Reply | Quote

You’ve answered all my questions – yet again – , thank you!

Reply | Quote

I miss-spoke. I meant filter a report….

Reply | Quote

There are several possibilities:

• You can use a quert as Record Source of the report, and put criteria in the query. These criteria can be dynamic:
  • 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.
  • You can also refer to a text box or other control on a form, e.g. [Forms]![frmSelect]![txtCountry].
    [/list]
  • 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

  • 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.

Reply | Quote

There are several possibilities:

• You can use a quert as Record Source of the report, and put criteria in the query. These criteria can be dynamic:
  • 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.
  • You can also refer to a text box or other control on a form, e.g. [Forms]![frmSelect]![txtCountry].
    [/list]
  • 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

  • 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.

Reply | Quote

I miss-spoke. I meant filter a report….

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote