Re: Checking a table for an existing entry and insert into a form?

I usually pass by the posters who require me and others to continually
ask for more information. You are still teasing out the information.
I read the following something like "I've got a secret that's very
important but I'm not going to tell you what it is until you ask me
pretty please::

=================================================
I need it to check the first field for a
matching entry, and if it finds a prior complaint, look up the data in
three
more fields and paste them into text boxes on the form (this is only
so the
user can verify they have the correct information -- obviously the
data will
not be entered a second time, since it's a unique record based on the
account
number).
=================================================

What field, what content?


Even though your point of view and the rationale for the existence of
your application may be "Complaints" other things such as "Customers"
and "Accounts" have to pre-exist before Complaints can have relevance.
Customers and Accounts are top level entities.

All live accounts are associated with specific customers in a
junction table, say tblCustomersAccounts. By the way, Principal seems
to me to be just a flavor of customer and does not require a separate
table. There should be a Boolean value in tblCustomersAccounts that
shows that this customer is in that kind of relationship regarding
this account. The other parties to this Principal relationship should
also have records here with that bit set. It doesn't matter that each
individual customer may or may not have other accounts.

Complaints would be on the many side of a one to many relationship
with the record in tblCustomersAccounts.

Both Customers and Accounts are really lookup tables for your use in
tracking the complaints.

While there may be no such thing as a Customer Account Number there
must be such a thing as a unique identifier for customers and
accounts. My preference is to use Autonumber surrogate Primary Keys
and to never, never, never let anyone else see them.

Complaint types would be listed in a lookup table and be selected via
combobox.

Reps/People in charge would be listed in a lookup table and selected
via combobox or listbox depending on whether there can be more than
one simultaneously.

The rest of the tings you mention that I think I understand would
logically fall under and be related to individual Complaints. In my
ignorance, I recommend that you have a table, tblComplaintEvent that
contains records of all of the events that actually occur regarding a
complaint. You would need another lookup table tblComplaintEventType
that would hold the list of possible events regarding complaints.
Each time a new event occurred you would open a new record, choose a
type and enter specifics about that event.

I don't know what resets are but they seem to be a possible outcome
that you note.

What's a principal party?? If account owner then you're already
covered. If you mean legal representative or advocate you may need a
separate lookup table for them.

To take the sting out of the Access Error messages, put in your own
error handlers and give your Luddite users gentle and friendly
information to help them out of difficulties. There are utilities
available that will help you comment your code and automatically
include error handling templates. I use one created by Duane Hookum
years ago.

Where so much of your information is list based: lookup tables with
combobox presentation, you should learn to use the combobox's
NotInList Event. That prevents erroneous entries due to typos yet
allows new entries when the user verifies that this is, indeed, a new
entry. Some people will bitch about anything and they may just want
to pull your chain. But making them affirm new information is sooooo
much better than trying to resolve data errors once they have made
their way into your database.

I think that the immediately above addresses the issue of your
concern. If you're using a combobox with autoexpand on and with
NotInList enabled it should be perceived as a pretty user friendly
operation.

I would expect that, in most cases, a complaint will be reported along
with the customer name and the account. It is to be hoped that in all
cases the account will be included. I would base the usual complaint
entry form on the Account record and enter data from that point. I've
been assuming the form/subform paradigm. It seems to fit your needs
very will. Using a combobox in the form's Header area, look for the
account number. Select it when found. Go to that record. (If you
haven't done this kind of thing before, take a copy of your form and
with the wizards turned on, add a combobox in the header area and tell
the wizard you want to go to that record. The wizard will write the
code for you). Once you arrive at the record, the appropriate fields
on your form will be filled in. If you are using the form/subform
parading with Complaints in the subform, all of the existing
complaints will be shown in the subform window.

Something not addressed is the initial relating of an account to a
customer. Is any or all of that information available via another
database within your reach? If you can get the assignments at machine
level then you should be able to save lots of new data entry.

Goth's go

HTH
--
-Larry-
--

"Crimsonsplat" <Crimsonsplat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:485B1FB2-98C6-4977-9B8F-8279D83F5D67@xxxxxxxxxxxxxxxx
> Ok, the long form it is. I think it's overkill, but if it helps....
>
> I work for a major metro utility department. Unlike more
traditional
> companies, most utilities operate on the basis of "one connection =
one
> account." If you have two meters, you have two accounts, six means
six, and
> so on. Doesn't matter if they all serve one proprty or six
properties.
>
> Then we've got customers, who as a result of this may have more than
one
> account. There is NO such thing as a customer account number. Over
time, a
> single account number (with minor variations enough to make it
unique) may be
> owned by more than one person (the "principal").
>
> A customer may have more than one complaint over time.
> The complaints may be about the same or different accounts.
> Complaints are categorized by subject.
> Each complaint is handled by one of several people.
> Who that person is may change from one complaint to the next.*
> Sometimes that person changes mid-process of a single complaint.
> There is a Hearing step at the end of the process, which may or may
not occur.
> Hearings are held at designated times. The customer can ask for a
reset.
> I need to track the overall status of the complaint.
> I need to track sepecific sub-steps in the Hearing.
> I need to track resets.
> I need to know what accounts are being complained about
> I need to know who the principal party is for these complaints.
> I have to be able to mine this puppy for any and all the above, plus
> whatever else no one ever thought of before but I'm supposed to have
known I
> needed data on for the last ten years. (Right, yeah.)
>
> Now toss in a workforce whose attitude towards computers can best be
> described as "borderline phobic," meaning I have to error trap (and
prevent)
> the bejabbers out of this app, and I'm about the only person who can
> fix/change anything. My goal is ZERO error messages, ZERO manual
corrections.
>
> Soooooo.... The primary "core" item which belongs in what I think of
as the
> "master" table, on which all else hinges, is the _Complaint_.
Parent, child,
> junction, I don't know what the correct term is.
>
> If a customer is coming back for his fourth complaint, I simply want
the
> entry form to recognize previously entered information and
auto-enter it.
> Combo boxes are not an option but that's what all the tutorials tell
me to
> use. We get close to a thousand complaints to track per year; maybe
4-5% are
> duplicates. It would be the combo box from hell.
>
> BUT.
>
> I am currently having a problem with setting up the query to check
the new
> entry vs. the existing record. I need it to check the first field
for a
> matching entry, and if it finds a prior complaint, look up the data
in three
> more fields and paste them into text boxes on the form (this is only
so the
> user can verify they have the correct information -- obviously the
data will
> not be entered a second time, since it's a unique record based on
the account
> number). If there is no match, the user simply continues the entry.
But it
> always generates an error message if a match is found (becuase it's
a
> duplicate record). I also cannot get it to paste the data from the
existing
> record's fields into the text boxes, whether or not I connect the
form and
> boxes to the table.
>
> I need to avoid pop-up error messages; my users have been
conditioned to
> think the sky is falling if they hear the "ding". Which means I get
a call.
>
> This requirement list caused the top Access expert in the whole
Department
> to scratch his head and mutter a few things; unfortunately a) he
works for
> another division and can only give limited help, and b) he's out
weeks due to
> surgery, and c) this project is already behind.
>
> Management has no clue of course. "Track this." The rest is just
the usual
> "omigoddotheyunderstandwhattheyreaskingfor????"
>
> Suggestions (that do not involve later criminal charges) are
welcome.
>
> Crimsonsplat
> *I think I have a relationship incorrect to account for this but I'm
> deferring that problem until after I get the data entry to work.
>
> "Larry Daugherty" wrote:
> > I answered your initial post without realizing that there was
already
> > a long thread behind it.
> >
> > What ever "Complaints is, it most likely isn't a "master" table.
If
> > it is to record an actual complaint then it's probably a child
table
> > or a junction table.
> >
> > What it tblTimes all about?
> >
> > What are Accounts, Principals? What are Assignments, Hearings and
> > Rep(s) List?
> >
> > There needs to be more explanation.
> >
> > HTH
> > --
> > -Larry-
> > --
> >
> > "Crimsonsplat" <Crimsonsplat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> > message news:F67EB45A-0A05-4CB0-A72E-2ECEEC195042@xxxxxxxxxxxxxxxx
> > > Sure! Here are the tables and relationships:
> > >
> > > Complaints (The "master" table)
> > > Principals (one to many to Complaint)
> > > Type List (one to many to Complaint)
> > > Times (one to many to Complaint)
> > > Assigments (many to one to Complaint)
> > > Hearings (one to one to Complaint)
> > > Accounts (one to many to Complaint)
> > > Rep List (one to many to Assignments)
> > >
> > > Due to legacy issues, preferred entry pattern will be to start
with
> > the
> > > Account Table, _then_ create the Complaint, select and enter a
Type
> > (from
> > > combo box?), and follow up with an Assignment (from combo box?).
> > All the
> > > other tables will be used at later points during the process.
> > >
> > > Currently, there are no one-on-one form to table relationships;
I
> > was
> > > complicating my life by trying to follow a legacy app which
would
> > require all
> > > the tables above to connect to a single form (which meant an
unbound
> > form an
> > > fields). Preferably VERY indirectly, through select & action
> > queries and/or
> > > temporary storage tables. It would really break my heart to
have to
> > > streamline all this by going to one table, one form....Not!
> > >
> > > I can eliminate actual duplicates by hand before converting the
> > existing
> > > database; it's a pain but doable (already done in my testbed) so
all
> > I need
> > > to do is prevent new dupes from arising and complete entries for
the
> > user if
> > > the data is already there. (Even if the "entry" doesn't
acutally
> > enter
> > > anything except a reference to the existing record).
> > >
> > > "mscertified" wrote:
> > >
> > > > It would help if you told us what these 5 tables are and what
the
> > > > inter-relationships are between them. Normally (in a good
design),
> > one form
> > > > updates one table. If you really need to update more than one
> > table, I'd do
> > > > it in the after update event for the form. Don't use macros!
Use
> > event
> > > > procedures instead.
> > > > Regarding the duplicate records, you need an additional column
for
> > the key
> > > > which will result in a compund key that is unique. Look at
your
> > table design
> > > > again.
> > > >
> > > > Dorian
> > > >
> > > > "Crimsonsplat" wrote:
> > > >
> > > > > Two related questions for a complaints tracking database in
> > Access2000:
> > > > >
> > > > > First, the super-basic one: what is the best way to go about
> > taking data
> > > > > from a form and putting it in a table. Due to the complexity
of
> > the business
> > > > > relationship, about 5 tables will need entries for each
> > complaint, plus
> > > > > standard codes will need to be looked up from about three
more.
> > (I figure
> > > > > combo boxes for those, of course). I've read through many
> > articles and
> > > > > questions--more confused than ever now!
> > > > >
> > > > > Second, the headache: 95% of the records will be unique,
but
> > about 5% will
> > > > > duplicate prior records in two of the tables. I've been
trying
> > to create a
> > > > > macro that will fire on the LoseFocus event, and auto-insert
> > data from the
> > > > > table's fields into the form if a duplicate record exists,
but I
> > get various
> > > > > errors depending on the contents of the macro's SetValue
> > arguments. If I'm
> > > > > on the right track, I'll post the full macro and error
messages
> > for further
> > > > > assistance; if not, point me elsewhere.
> >
> >
> >


.

Relevant Pages

  • Re: Checking a table for an existing entry and insert into a form?
    ... I need to take a user entry from a form's text box, ... > to me to be just a flavor of customer and does not require a separate ... > this account. ... > Complaint types would be listed in a lookup table and be selected via ...
    (microsoft.public.access.gettingstarted)
  • Re: O2 - is this standard for them?
    ... >> I called them but they said they couldn't discuss the account because ... >> nothing that would affect my credit rating and take my address off ... >> not dealing with a reasonable complaint. ... which is incompatible with the purpose or purposes for which it was ...
    (uk.telecom.mobile)
  • Re: O2 - is this standard for them?
    ... > address and eventually the O2 bills stopped being sent to my house. ... > I was surprised when a letter, addressed to my tenant, arrived. ... > I called them but they said they couldn't discuss the account because ... > not dealing with a reasonable complaint. ...
    (uk.telecom.mobile)
  • Re: Checking a table for an existing entry and insert into a form?
    ... account. ... A customer may have more than one complaint over time. ... Hearings are held at designated times. ... entry form to recognize previously entered information and auto-enter it. ...
    (microsoft.public.access.gettingstarted)
  • Re: Software Release Decisions
    ... why don't you put an overciew link? ... Any way when you have a customer to complaint and an account guy to ...
    (comp.arch.embedded)