Save incomplete "records in progress" on separate table

[dayna]

I am working on a project in which accuracy in data entry is of the utmost importance and null values in tables most be avoided at all costs. Thus, I have made extensive use of validation rules/text in my tables to prevent inaccurate/incomplete records from being stored.

Though is strategy seems to work well for its intended purpose, I’ve found that it creates another problem. My data entry forms are, by necessity, long and include a lot of tedious, detailed information (stored in bound, referentially-enforced fields). There will almost certainly be times when a user will complete most of a form, then come to a “required” field for which he doesn’t know the value. (I put “required” in quotes because the Required property is actually set to No, but the Validation Rule includes Is Not Null.) The user will then proceed to curse me as he chunks the new record, investigates the unknown value, then comes back to enter the entire record again.

To illustrate, let’s say you just spent the last ten minutes of your miserable life entering in all the minutia of a financial transaction, but when you get to the very last field, a combobox, you’re not sure which value to select. The only way to find out is to ask your boss, but he’s out for the afternoon, honing his putting skills in that stupid little hat while you’re slaving away over your keyboard. Since he doesn’t take calls while he’s golfing, you won’t be able to fill in that value until tomorrow. Doesn’t it annoy you that tomorrow, when you find out the mystery value, you have to enter the whole record in again? Wouldn’t it be nice to be able to save all the work you did, and fill in only the one missing field tomorrow?

I have a couple vague ideas for how this might be accomplished, but I’d like to do some research before I go stabbing around in the dark. Evidently, I have not been sufficiently imaginative in coming up with search terms on this topic, so I’m asking for a little guidance. In the past, many of my most daunting problems have been solved by learning the right words to describe them. I have a feeling that might be what’s going on here.

Can anyone recommend any key words or links that may address the issue I’m describing? Can you help me phrase my search in a way that may yield some relevant results? Thanks!

 

[Banana]

In my case, I created a bunch of flags, which can be then queried and pushed back to the users if necessary.

For example, I have a form that fills in reservations for our classes. Sometime, someone registers for themselves and a bunch of friends but they don't have the contact or other information. Therefore, on the initial combobox, I select "(Reserved)" and the data entry is complete, with the validation entirely bypassed.

You can then query for any records with that "(Reserved)" value and ask to complete it again and again.

Is that what you were looking for?

 

[dayna]

Yeah, I think that's exactly the sort of thing that I'm after. Unfortunately, I'm pretty dense, so I still don't quite understand how I can bypass the validation rules in my table (which exclude null values) in cases where incomplete records need to be saved.

My idea was to unbind the data entry form and create an additional table, which would be a copy of the actual table without the validation rules. I could then come up with some little code in the OnClick event of my data entry form's Save button that would check each control for a null value. If no nulls are found, the data would be saved to the actual, validated table. If any null values are present, that data would be saved to the unvalidated "storage tank" for incomplete records to be queried and completed later...

I was just hoping there was a simpler way - ideally one that doesn't require too many late nights wrestling with the VBA Editor. I have a way of constructing really complex solutions to what I later find are simple problems that can be solved with one or two lines of code. I will definitely research the word "flag." Thanks!

 

[Banana]

Actually, you would get more information if you looked at the examples for "<ALL>" in a combobox, because this is basically the same principle (using a UNION query to populate the combobox with the flags, rather than word "ALL"), then whenever you decide appopriate (e.g. when user opens the application?) do a quick query to check for any records that has those flags then push it back to the users.

No need to enter a null value, unbinding, or whatever. My validation code has a IF/Then block to check for any flags (e.g. "Reserved") which it then bypass the normal validation routine and does other things.

Did that make sense?