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

C

Crimsonsplat

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

Conrad

How do you feel about code? I would use it in both instances. If interested
let me know.
 
M

mscertified

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
 
C

Crimsonsplat

I'm ok with code, as long as it's not really, really long. I had a little
Visual Basic 6.0 training a few years ago--enough to prefer it over learning
macros. Frankly, I originally tried to do this in VB but got ordered off it
for various reasons, including that no one else in the company could
update/maintain it. Based on the problems I'm seeing, this isn't much
better!

See further answers to the other replies....
 
C

Crimsonsplat

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

Crimsonsplat

Macros are making me do more than that, but fortunately my desk can handle
the damage. Not too sure about my fist, or co-workers' nerves though! :)

The problem is, if I do it in code, I get dinged for maintenance issues. If
I stay "within Access" management is happy. *shrug* (Management has a
schizoid concept that I'm a wizard with MSOffice but as long as it's not
programming, it's easy enough for anyone to do. I won't get started on the
office politics with the IT dept.)

I just want the job done, and done right. In the end, they'll accept
performance. Crimsonsplat
 
J

John Vinson

The problem is, if I do it in code, I get dinged for maintenance issues. If
I stay "within Access" management is happy. *shrug* (Management has a
schizoid concept that I'm a wizard with MSOffice but as long as it's not
programming, it's easy enough for anyone to do. I won't get started on the
office politics with the IT dept.)
Click to expand...

<wry, sour laugh>

I once rewrote an application with over a thousand lines of hideously
convoluted macro code - macros calling macros, forms whose only
purpose was to have textboxes to store variables used as macro
parameters, etc. etc.

About a hundred lines of VBA; understandable, VASTLY faster, and oddly
enough it actually worked, unlike the macro under many circumstances.

VBA is an *integral* part of Access. VBA is the same language as used
for "macros" in Excel or Word. Just write VBA and tell management it's
"all done in macros, just like Excel or Word macros".

John W. Vinson[MVP]
 
L

Larry Daugherty

Each table should be used to capture information about a separate
entity type. Entities are the various "noun" kinds of things. Some
of those noun kinds of things are things like complaint codes, colors,
flavors, etc. Those tables are called "lookup tables". You might use
a combobox to lookup up a value in a table to insert into the current
record in the more "main" table. [Main is a very poor name for a
table].

When parts of a record repeat it's time to create a new, child, table
to capture the repeating information. Once that child table is
created that same information is captured in that table for all of the
"parent" table records.

Unless you've gone out of your way to change your form's properties
from their defaults, the only thing you need to do to get the data
from your form into your table is to navigate off the current record.

My guess would that a complaint from a new customer would require
records only in two tables: tblCustomer and tblComplaint. If this
were a new complaint from an existing customer then there would be a
new record only in tblComplaint.

By the way, for people just getting started with Access I recommend
both this group and microsoft.public.access.tablesdesign.

If confusion remains, post back with a more complete description of
your application.

HTH
 
L

Larry Daugherty

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
 
C

Crimsonsplat

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

Larry Daugherty

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
 
C

Crimsonsplat

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

Crimsonsplat

It occured to me that someone else might have had the same issue with combo
boxes, and I have verified by searching through this forum that the max
number is over 65k. You'd think I could accept the database breaking in
2075, but it assumes that we'll never have more than the 900 - odd complaints
per year. Essentially, if less than 20% of our customer base got involved in
a mass protest regarding their accounts, the app would break.

Yes, that sort of thing is always a possibility in my line, knock on wood.
But just to get the stupid thing to work, I may accept it and try to find a
solution later.

:
(snip)
 
B

Bruce

Providing that each entry has a unique piece of data in a field , say
reference or name them you could just use a find duplicate query based on
the common field data.

Bruce
 
C

Crimsonsplat

*BonK* (Sound of head hitting desk for missing the obvious)

Well I had to go through the test database by hand, (and will have to repeat
on the live one) to get the dupes out, but yes, they'll be unique. And *bonk
again* yes, a Find Duplicate query should work just as well or better than
the what I was doing last, which was guaranteed to cause the error message to
appear. *bonk yet again* I was beginning to think maybe this didn't belong
in the New User forum, but I think I just disproved that... :-/

Ok, now before I kill any more brain cells I can't spare, what's the
commands for getting the data out of the table and into the form? I thought
SetValue was supposed to work, but it generates errors, telling me that
object isn't legal when I point it to the table I want to get the info from.
If I don't include the table name, it pastes the name of the form I'm
working with into the form's text box. (Wait, I just thought of something....
the field name I've worked on is "Name" and I never tried the other field
because the first didn't work... I'll try changing it in case it's a keyword
problem.)

I'm not at work right now, so I can't give you the exact parameters right
now. I'll post them monday if/when I get time to come back to the problem.
 
B

Bruce

If you are talking about displaying the results of the find duplicate quert
in a form then just connect the query to the form display..

Bruce
 
Top