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

Larry,
I have considered my answer to your post all night, and have decided that it
is simply best to thank you for the spirit of assistance in which you offered
your help. It is appreciated. But I think you're engaging in overkill and
it's probably best to let someone else answer the question.

I'm not asking anyone to design my database, and my industry has some unique
issues that the table/database design either works with or around. A Q&A to
clear these issues would involve as much work as some consulting contracts.
I _knew_ these issues would start cropping up as I gave more information;
it's not that I'm keeping secrets, I'm just trying to reduce extraneous
issues. I say again, I'm not asking you (or anyone) to _design_ the
database. I just need an answer that is apparently non-trivial. Perhaps it
doesn't belong in New Users?

It's possible that a key parameter behind my choices has been buried by the
sheer volumn of text we have written. For the sake of anyone whose eyes have
glazed over from the preceeding novels, I shall restate/summarize the primary
question with the salient facts:

I need to take a user entry from a form's text box, compare it to existing
data in a table, and if the entry is a duplicate, obtain the information from
the other fields in that record and paste them into other text boxes on the
same form, so the user can confirm it is correct.* If the entry is not
duplicate, the form needs to allow the user to enter data into those fields
and then append the entries to the table as a new record. I don't know
whether the form should be linked to a query or directly to the table, nor
how to accomplish this without getting hit by error messages complaining that
the entry would create a duplicate value in an index, blah, blah, blah.

I know a combo box is the normal way to do this. However, there are
900-1000 entries per year, of which less than 5% are duplicates. This means
a combo box would have _thousands_ of unique entries in only a few years. I
have no idea what the maximum number is, but I'm not willing to gamble the
entire design on the hope that an MS sofware engineer never said "Ah, there's
no way anyone will ever make a combo box with over 4,096 entries... twelve
bits is enough." Or 32,767, for that matter, even if that would be sometime
in 2035 and we're running this app on our wristwatches. Nor do I want to
gamble that loading the form won't be delayed excessively while the system
populates the dropdown.

On the other hand, if someone _has_ done this successfully, I am all ears. I
would prefer a method that avoids a lot of code, but if I have to bite the
bullet, I will.

Crimsonsplat
*I'd prefer to avoid a pop-up if the check gets a hit, but I'll bite that
bullet also, if it's easier/necessary.

"Larry Daugherty" wrote:

> 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
.

Relevant Pages

  • 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: 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)
  • supposed to be a letter of complaint to NTL (long)
    ... Complaint Letter of the Year. ... real-life customer complaint ... letter sent to NTL ... your 3-in-one deal for cable TV, cable modem, and telephone. ...
    (uk.people.silversurfers)
  • OT Letter of complaint,(long)
    ... Complaint Letter of the Year. ... A real-life customer complaint ... letter sent to NTL ... your 3-in-one deal for cable TV, cable modem, and telephone. ...
    (sci.electronics.design)