Automatic transfer of info from one object to another

[Aimee]

I have a table within my databse for which I need to know how to keep a seperate list of all of the records that have a 'tick' in a specific field. I would like the list to be updated automatically based on the 'tick' status in the original table.



I also need to add some additional information - I need to automatically record the date that this information was added

Im pretty new to Access, so please excuse my ignorance. Any help would be greatly appreciated.

[Dal Jeanis]

Typically, that would be done with a query against the same table, rather than a separate table. A query is always up to date.

SELECT (all the fields I need to see)
FROM MyTable
WHERE MyTable.Tick = True;

If you also need the date/time that the field has been set to Yes, then you'd set a date/timestamp field on the same record. On the form that allows that tickbox to be set or unset, you'd have a checkbox for the tick (for example, chkTick) and a TextBox for the timestamp (for example, txtTickUpdated).

In the AfterUpdate Event code of the checkbox control, you'd have a line that says sets the timestamp:

Private Sub chkTick_AfterUpdate()
  txtTickUpdated = Now()
End Sub

[Aimee]

So when I update info in the main table, this will automatically update in a pre executed query? ...I thought that I would have to repeat the query each time to get the most recent output?

[Dal Jeanis]

While you could possibly "pre-execute" a query, and store it somewhere (say, as a report) to look at later, that's not the way it normally works.

When you open any query that is on the list in your database, you will always be executing that query against whatever data is currently in the database. When you open any form that is linked to a query, the form always fills with the current information. Any other way of designing the application wouldn't meet most user's needs.

Did that make sense?

[Aimee]

It does, thank you.
...But that creates another problem for me; I have created a form based on a table, but whenever I check the tick box for that field, this does not show on the table, even though the rest of the written information does.
I added the tick box field at a later date, so assumed that maybe I had to re-create the form from scratch since I edited the original table. Surly this can’t be the case...?

[Dal Jeanis]

Nope. In Design mode, Right-click the checkbox, go to the properties pane, and set the record source to the field on the table that you want the checkbox to update. that's it.

[Aimee]

Sorted! Thank you Dal

[Dal Jeanis]

You're welcome. Please mark thread "Solved". Top of page, under "thread tools".