!No Duplicates!

[OldBirdman]

Sometimes the rigidity that is programming demonstrates that the initial design is in error. This needs to go all the way back to whomever is requesting the design. Assume Keys 1; 2; 3; and 4. But key 3 is a duplicate of key 2, entered in error. OK, delete it.
Stop!
Right here is the problem: Key 4 exists. What now? If you don't have a new sample ready to enter, do what? Change key 4 to key 3, even if work has been done as key 4?
Ask the requestor to answer this question is my answer to this issue.
By the way, there seems to be no audit control with the current design request.

[NeoPa]

Mine too. That's spot on OB. You illustrated the issue quite clearly.

@MyWaterloo
I suggest you explain to the requestor the flaw in their assumptions, and ask THEM to indicate how you should proceed.

If you have a soluble problem, we're glad to help. If the question makes no logical sense, there's little else we can do but point this out to you.

Good luck. It's always difficult being put in a position such as yours.

[MyWaterloo]

Thanks OBman. How so no audit control? *Sigh*
"ask requestor to answer this question..."
Most of the time when my superiors have "grand" ideas to make some change to the current record keeping design, I cringe. I am usually able (with my self learned limited knowledge of VB and Access) to make the changes they wish to their satisfaction. The latest idea was to begin with, good. We use to keep track of our samples by month and client. Example: JohnDoe01, JohnDoe02, etc. for the month. Then restart at the beginning of the next month. The new idea was to start at the beginning of the year and number from there with a number as the ID. I actually think this a wonderful idea that will eliminate some potential for confusion. We have never actually worried about knowing the current total of samples taken at any given time. However, this seems to be the latest "idea". "We want the sample to have an ID that represents what number sample it is." And so I am left to implement it. Whenever one of these grand schemes comes along I find myself back here looking for the sagacious advice of the bytes community.

A simple question, how do I total my records? Not using that number for the ID field, but just as a separate field. Any way to keep a current sum of records on my form I use for sample entry? Just knowing the sum even if it is not identical to the ID's may be enough to satisfy.

[topher23]

That's an easy one. Create a text box control on the form and set its control source to either the Count or DCount function. The Count function is appropriate if your data entry form is not bound to a custom recordset or data-entry only, while the DCount function can be used in any type of form since it pulls its count directly form the table/query that you tell it to rather than the form's Recordsource. Look them up in Access Help for usage info.

[OldBirdman]

How so no audit control?

Any time there is a gap in a sequence, the question is "Why"? One complaint about Access autonumber is that you can't fill the missing gaps, as you know.
An auditor might ask, why the gap? Many labs might have to account for samples received, perhaps for legal reasons. You're in NewYork, so you know about the current problems with peanut butter from a manufacturer. Apparently there are lab tests involved. If there were methods you proposed for eliminating gaps, some lab technician might be rich now, and there would be no way to recover any prior data. Any record of receipt of sample is gone - forever.
Were I in your place, I would propose that EVERY sample be given a number. This you could generate from the date and highest number used to date, i.e. 09-00013. After entry, this sample number, client, and entry date would be locked. Can't change, delete, overwrite, etc. Lost or missing samples would be recorded. Or a new record could be created, and the old saved, with the old data. Lab technicians would think they were overwriting data, but for critical datafields, a copy of the old data would be kept.
If technician wanted to delete a record, it wouldn't go away. It would be marked as deleted, but still be available to auditors and management.
The rest of your questions are design & implimentation questions. Start with 2 forms, bound to the same table. frmMeSee will be the data in the table you (and later auditors) want to see. frmTechSee will be the working form for the technicians. Identify controls that will be bound to a table, those that will be calculated from other controls, and those that are independent of a given record of the table, such as sample counts. Design the table to have all identified fields.
Most of the "How To..." stuff is already in this thread.
Good luck!

[MyWaterloo]

As always, thanks everyone for your "free" time that you spend answering my questions. Hopefully I'll have the experience someday to return the favor on some other wandering star in the Access universe.

topher23: Count/DCount that's what I thought. Wanted to make sure I'm not missing something else obvious.

OBman: I see what you mean about the questions that could arise with missing samples. I hate redesigning. =-\ Sometimes it's just the thing to do.