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

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: Checking a table for an existing entry and insert into a form?
    ... matching entry, and if it finds a prior complaint, look up the data in ... 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)
  • Using Combo Box to select records for update
    ... One form is for data entry and one form is for updates. ... pick which complaint they want to update. ... surname from what is pre-populated, the underlying record in the original ... with a first name of Margaret and a complaint of "Too long ...
    (microsoft.public.access.formscoding)
Loading