Select certain records to update

[pd06498]

Hi all

I'm not sure if this is the correct area for this question but I think it is.

My DB stores records relating to accountable books, which are all stored with the serial numbers etc (like ledger books with pages that have serial numbers). Each book contains a series of numbers that is different to each of the others (book 1 = 000001 to 000100; book 2 = 000101 to 000200, etc).

I have set up a search page to enable me to select records that are being inspected so that after they are inspected and audited, the DB records the date of the inspection and the person who inspected it against each relevant record. There may be several inspections against the one book.

I determine if a book is unused, in use, or complete, by using the start and finish date fields.

When selecting the records for inspection, I want to then be able to list those records that are in use, so I can enter the serial number within the book that we are up to when the inspection is done.

Would someone be kind enough to give me an idea on how to accomplish that?

 

[RichO]

So then a record that is currently in use will have a start date but no finish date, correct?

Set up a query and use Is Not Null as the criteria for the start date and Is Null for the finish date to determine which records are in use.

 

[pd06498]

Thanks Rich

That helped.

Now to add to the complication, I bring up a list of all records in a listbox that are either unused or in use, and from these I can select one, some or all of these records to add a date that they were inspected.

What I want to do is, from the records I select, bring up another list (or reduce the present list to those selected), and of those selected, find those in use (using the above method) so I can add the serial number the book is up to at the time it is inspected.

This will assist with audits and saves reauditing every book again next time. The auditor can just pick up where he finished last time.

Hope that makes sense!

Any ideas?

 

[RichO]

Would it be the same serial # for all records you select?

For example, if you were to select 10 records from the list box would you only need a single text box control on the form to enter a serial # that would then be applied to all 10 of the records (or only those of the 10 that are in use)?

Am I on the right track here?

 

[pd06498]

RichO

Not quite. My DB contains many different varieties of books, but usually there is one or two of each variety in use at the one time. The serial numbers entered would be different for each one...depends where we are in each book at the time of audit.

If you like, I can email you what I have so you know what I am talking about...