Visible True/False?

[pd06498]

In the database I am working on, I have a form that records the serial numbers of pages or forms in a book. For instance, a book might have pages 001201 to 001300. These numbers are unique and no two similar types of books or forms will have the same series of numbers. I also have a start and end date for each book or series, which I use to indicate if the book is in use, has never been used, or is used and finished.

Every so often, there is an inspection of these books to ensure accountability. At this time, I need to be able to identify those books currently 'in use' and input another entry indicating what serial number the book is up to at the time of the inspection. This serial number may change from inspection to inspection, and I just need the information from the latest inspection. Once a book is completed (indicated by the 'end date' field being completed), this information is no longer needed, just the start and end serial numbers and start and end dates.

What I am after is any ideas on the best way to tackle this, i.e. hide/unhide the additional serial number under particular conditions. I have tried a couple of methods using code but get errors advising that particular records cannot be saved.

Also, with the above scenario, is it necessary to save this additional serial number in my table or can it just remain in limbo until changed or is no longer needed.

Any pointers would be appreciated.

 

[willir]

pd06498

A lot depends on the design of your database.

Here is something simple to try for the "Current Record" event.

    If isnull(Me.EndDate) Then
        Me.InspectionFld.Visible = True
    Else
        Me.InspectionFld.Visible = False
    End If

Where InspectionFld would be a field on your form that you want to make visible or invisible. You can use the same tactic for making a subform visible / invisible.


If you are looking for a way to bring up the current books under going inspection, you can either search for records with a null EndDate, or search by StartDate with the most recent dates first.


As far as the serial numbers, I am not sure if I understand what you are looking for. However, if you an inspectation takes a while, then you need to store the serial number somewhere to be able to reference it. If it is no longer required, you may delete it or just keep it for historical reasons. (I surmise the number has to be used somewhere for something which may be why I am a little confused. Just the pack rat in me.)

Hopefully this helped a bit.

Richard

 

[pd06498]

Richard

Thanks for the reply.

The inspection serial number is used when an audit is done to indicate where the book was used to when it was inspected. This is later used in forms that are signed of by the auditors. Audits are usually done each quarter or thereabouts.
If a book is unused or fully used, that is not a problem. It is just when books are part used as I need to show where it was up to when it was inspected. Once a book is completed the middle serial number is not used. If a book is 'in use' for more than one inspection period, I just need the last inspection serial number.
Hope this helps.

 

[willir]

pd06498

I may be wrong about this, and it is your decision, but I would not worry about the serial numbers. Yes, they are critical during the audit so you can accurately track their location. However, I suspect you are more concerned about the audit end date.

I gather these audit books are like a note book or ledger book - finish one and continue to the next. For example, transactions for 2003 split over three books. Are the serial numbers on the book, and entered in to your new system? Or do you use the syste, to generate the number?

Regardless, I suspect it does not hurt to keep the serial number for the record - visible or not.

I wonder if the problem may be the design of your database.

There are tons of variations, and I may be out in left field, but here are some thoughts...

BookTbl
Would include location

PageTbl
If requried, 1:M relation to book, one book has many pages

AuditTbl
1:M relation to BookTbl, one book may be audited more than once
Would include start and end date of audit.

I did not include the detail stuff on the tables so as not to distract from the idea that the Audit is tracked on a related table, and not on the Book table.

Note that you could also have a location table. This would allow you to track not just where the book is currently, but where is has been.

... Your thoughts?

Richard

 

[pd06498]

Richard

The books are provided to us by our agency for use, and we have to record them, including the start/end date of each book and the serial numbers in each book (or rather the range within the book).

During inspections, the auditor merely signs off to say that he has inspected ALL books on hand (used, unused and partially used) since the last inspection and states that part used books are used to 'x' point.

The next inspection would then require an inspection of all unused books, partially used books and used books from the point of the previous inspection. Historic used books already inspected when they are completed are not inspected again.

Also, when the officer responsible for these books etc goes on holidays or transfers and is replaced, a list of unused and partially used books is generated to include in a 'handover' statement.

Needless to say, these forms and books are what we call 'Auditable Forms' or 'Accountable Forms'. In other words, in their unused form they are accountable and will eventually have a sum of money attached to them.

Hope this clears the air somewhat. I really do need to record the middle serial, but not forever, just for the life of the partially used book.

 

[formerTexan]

Here are some thoughts. Fortunately database data is not like those dusty accounting logs that keep piling up in shelves and filing cabinets. Data can be stored relatively efficiently.

I am sure someone has made a rule about it, but it seemsthat just after data has been discarded, someone figures out a need for it.

A suggestion is to set up a archive database (possibly on a CD) and copy your completed serial numbers (and related information) to the archives, then delete from your active database. They'll be out of your hair, but still available for unanticipated needs.

Otherwise I am not sure how you are coordinating digital records with your hard copies. How are serial numbers entered? Do auditors only audit hard copies or digital as well?

Cheers,
Bill

Cheers, Bill

 

[pd06498]

Bill

Auditors check hard copies as well as the list of books on hand, which until now, has been recorded in a book. As new books are received at my workplace, they (or rather their information) is entered into the database. Information includes:
* Type of book (what it is for)
* Date Received
* Serial Number range for the book
When a book is started, the start date is entered, and the end date entered when it is complete. The end date is set up to advise (in another field) the year that complated book can be destroyed (usually several years later).
I have also created a means to search for various types of books, and itemise those being inspected, thus adding the inspection date and who inspected it. I am just having trouble adding this last little bit about where a book is up to when it is inspected.
I can send the database for a peruse if interested. Just don't want to post it generally as there is some confidential stuff in it.

 

[willir]

pd06498

A lot has been said in this post. Have we made progress? Other than the visible / invisible issue which can be controlled at the form level, what else needs to be addressed?

Rather than posting the database, the structure and a bit of sample data would suffice.