Tracking Log of updates in a form

[cin324]

I have a form that is updated daily that I would like to create a report showing only the items that were updated.



a few of the controls on the form are:
familymember.pantsize (text)
familymember.panttype (text)
familymember.pantfilled (y/n)
familymember.socksize (text)
familymember.socktype (text)
familymember.sockfilled (y/n)

Currently, I have a report that lists the size and types of items if the "filled" checkbox = No. There are inventories of these items that the report is used to pull from and fill the items on the report. When the form is updated reflecting the "filled" items they fall off the report. But at the same time some of the items are not able to be filled, and new items are potentially added to the form (for different families) - how can I print a new report to show only the newly added items? If everything shows on the report again there is great potential that items will be looked at again to pull from inventory when they have already been established that there is not an item in inventory to complete the request.

Is there a tracking log that can be utilized? Something that would show the recent additions and/or changes to the form (ultimately the FamilyMember table) that I can use to create the new reports?

Thanks in advance!

[John_G]

Hi -

You have a misunderstanding of what forms do. Forms do not store data - they only display data from the underlying table or query, and allow you to manipulate (add, edit, delete) records. They can do a lot more (including generate reports) by using VBA.

From your description, you will need a Date/Time field in your table to indicate when each record was entered or last updated, and use VBA in your form to update that field whenever the data is changed. Your report could use that date/time, together with the "filled" checkboxed to determine which records to print.

HTH

John

[cin324]

Yes, I understand how forms work. I have a table where the data is stored.

I have about 30 Yes/No boxes on the form that I was hoping I wouldn't have to attach date fields to.

Oh well
Thanks for your help

[John_G]

I think I follow - but unfortunately Access does not have the capability to log "time of last update" at the field level or record level.

Regarding your last sentence, note that you cannot attach (bind) date/time fields to checkboxes - these are bound to Yes/No fields.

John

[dblife]

This is just my humble opinion, but 30 check boxes suggests you are not using Access in a relational way - you're probably using it like a spreadsheet..
If this is the case, you cannot hope to benefit from the features Access provides.
Would it be possible to post your table structure - a screen cap of your relationship diagram?

[cin324]

@dblife:
The database is for an organization that helps provide Christmas to area families. One table (FamilyMember) has fields for tops, pants, socks, coats, boots, underwear, CDs, DVD's, video games, household items, sport equipment, etc.

For each clothing item there are 3 fields: (for instance)
TopType (Text) (Another table is linked for types)
TopSize (Text) (Another table is linked for sizes)
TopFilled (Yes/No)

The family fills out a form listing all of the items they would like the organization to help them with. When the forms are returned the corresponding items are then filled in on the FamilyMember form (which then populates the table).

As items are "shopped" for and fulfilled from the list the "Filled" control is checked in order to keep track of what has been fulfilled for each family member. As items are filled reports are generated to know what has to be shopped for.

One catch to this is an inventory that's kept at the organizations storage facility - that inventory is gone through first to fulfill the requests, then whatever is not in the inventory has to be bought from stores. Printing report to use for inventory "shopping" - then update database with any items that were filled from inventory - print report again and items that were already searched for from inventory are still on the report (not the filled items, my query uses the Yes/No fields to not include anything that has been filled) along with any additional requests from families that have been added to the database. The second report needs to differentiate between the new items that have been requested and the items that were already gone through from inventory so people are not searching for items in inventory again after they already searched for them and were not filled.

Hope that makes sense.... How can I create a report that somehow differentiates between items already reviewed and new items added since last report?

[dblife]

I think I understand what it is you need to do.
The bad news?
Your structure is wrong.
I have put a few tables together of what it needs to be more like.
If there is still time and you want the assistance, I can help.
The structure should be more like that shown.
You can then put dates against items being removed etc and then you can very easily create reports about what items are still required for what person etc.

[cin324]

I tried to attach the database but for some reason it keeps saying it failed. It's probably too late to make any major changes at this point since the database is already being used, but I can start on a new one for next year....

I have attached the screen shot of the relationships

[dblife]

Your ER diagram confirms what I feared.
I would be happy to work with you on an improved version for next year if you wish?

[cin324]

That would be great! Thanks!

Is there a way to make changes now without losing the data already in there?