[AccessD] VBA Unbound data entry / update form

Why?  Two reasons.  One, you completely lose this record locking issue
you are having now. Two, you give the Users way more capability.

I don't have enough time right now to whip up a demo, sorry, under the
gun for a different project.  But here's the gist:

tblCase
CaseID
CaseName
StartDate
Etc.

tblContact
ContactID
FirstName
LastName
Etc.

tblCaseContacts
CaseID
ContactID

tblContactDetails
ContactDetailID
CaseID
ContactID
Representative
TimeStamp
DetailNote (this would be the memo field)

The reason I don't have time, is cause I don't have the time to build
the case and contact portion (creating a case, adding a contact, etc),
but your system already has this.  Now, when the user brings up a
contact, in the setup above, the big text box that displays what you
currently have as a single memo field, instead is returning the results
of a function.  This function is setup to be given variable criteria.  A
ContactID and or CaseID, Whether to ascend or descend, only details with
certain words, only details within a time frame, etc.  All of these
options are put in a handy little frame next to this big text box, so
with the push of a button, this 'memo' field they have can sort the
entries from oldest to newest, or newest to oldest.  With the push of a
button, they can display all of the contact details for that contact and
case, or for that case, or for that contact (so if that contact is
involved with multiple cases, they can instantly see the entries for
every case that contact has had, or if there are multiple contacts for
that case, displaying the details for the case instead of the contact,
they can say 'I see here that our reps talked to your brother
yesterday....'

And below this big box, is a little smaller one, where they can enter a
new contact detail, and as soon as they hit 'Submit' (or save,
whatever), whalla, that data is saved as a new record (with the relevant
information) in a split second.  No record locks, because the data other
reps may be viewing is not being locked either.

If you REALLY must have a demo for this, let me know, maybe I can do
that tomorrow....

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, May 28, 2008 6:04 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] VBA Unbound data entry / update form

 > Question, are the people entering this data ever actually 
EDITING information?

I don't sit and look over their shoulder, however I suspect 
that they do.  These records are any and all "contacts" with 
a specific individual or company.  Any given claim now has 
hundreds of contacts.  As I listed before there are 
literally dozens or even hundreds of different people that 
may be contacted to process a claim.  These are disability 
claims so there may be employers, witnesses, friends, 
acquaintances, lawyers, doctors, hospitals, pharmacies, 
spouses, children, neighbors, dogs, cats, and the man in the 
moon.  These claims can pay out 20K per month to a doctor on 
disability, this is BIG money and they track (and check up 
on) EVERYTHING.

I suspect that the reason they want all of the "contacts" 
with any given entity in one record is simply for ease of 
reading what was discussed with that contact.  Imagine 23 
phone calls to try and get ahold of the spouse.  12 are no 
answer, 5 are "I can't talk right now" and 2 are "here's the 
skinny".  Now, if each of these are in a separate record, 
separated in time and space with calls to the dog, the 
milkman, the physician, some other physician, the private 
investigator... Well... to some people it just makes sense 
to have a single "spouse" contact record.  Dump it all in, 
in date order, plainly marked and be done.

This is not a computer fix it help desk where there are a 
handful of calls and the problem is fixed, there are a 
minimum of dozens and often several hundred contacts in many 
cases.  I have seen one case where there were around 250 
contacts with JUST the spouse, trying to get her to repay an 
overpayment.  And any given claim won't always have specific 
contacts, some have spouses, some don't, some have lawyers, 
some don't, some have 20 DIFFERENT lawyers (and physicians, 
and hospitals, and drug stores).

Soo... yea, they are constantly editing the memo fields, 
adding new info about contacting that entity.  Each 
"contact" is date / time stamped with the initials of the 
person making contact, embedded right in the text.  I know 
that they do use searches right in the memo field looking 
for keywords and what is the point of clicking "again" 47 
times to read 47 "no answer" records?

I just don't "get" what the point is, and why the fixation 
on splitting the memo into different records.

If I split it up, how do I reassemble all the milkman 
contacts, all the physician contacts, all the spouse, dog, 
cat, neighbor etc.  More to the point, why bother?  They 
have a system that works, all it needs is to not lock the 
record when the field is edited.  I can do that with 
Gustav's unbound memo and a lock field.

I think the time spent fixating on normalizing things would 
be better spent on examining your navel.

John W. Colby
www.ColbyConsulting.com
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.