Prevent Duplicate Records in table with multiple Fields as Primary

[Sreedhar]

This must be very simple I guess, but I'm missing the basics much to the
annoyment of me and my Users. Please give me pointers so that I can take a
lead.

I've two tables tblCustomers and tblAccounts with Many-to-Many relationship,
with an intermediate table joining the CustomerId and AccountId (Primary keys
of the first two tables). Both these fields together are the Primary Key in
this Intermediate table, say, tblJoin.
I've two methods of updating this tblJoin,
---1) a form bound to tblJoin
---2) a form bound to tblCustomers, with unbound txtboxes for AccountIds of
the Customer - a command button updates the tblJoin using recordsets.

Now, how can I ensure that each record in tblJoin is unique ? I don't want
Access crying about PrimaryKey violations. That's not very User-friendly.

 

[Klatuu]

You should delete the form bound to tblJoin. If you allow that to be edited,
you will loose relationships.
Also, Your form should be based on the tblCustomers with a subform based on
tblAccounts. ALL WITH BOUND FIELDS.

Then to ensure data integrity and avoid annoying users, in the After Update
event of the Account primary key field control on your subform, use a DLookup
to determine if the combination exists and present your own error message to
the user:

If Not IsNull(DLookup("[AccountID]", "tblJoin", "[AccountID] = '" &
Me.AccountID & "' AND [CustomerID] = '" & Me.Parent!CustomerID)) Then
MsgBox "This Customer/Account combination already exists"
End If

 

[Tim Ferguson]

I've two methods of updating this tblJoin,
---1) a form bound to tblJoin
---2) a form bound to tblCustomers, with unbound txtboxes for
AccountIds of the Customer - a command button updates the tblJoin
using recordsets.

I would urge you to start thinking about forms in terms of your users'
processes. Either they start with an account number, and have to allocate
a customer to it; or they start with a customer and choose an existing or
new account number to give him. In either case, you design your form to
follow that order of events.

Please don't think of forms as windows onto tables -- forms are for doing
things.

Now, how can I ensure that each record in tblJoin is unique ? I don't
want Access crying about PrimaryKey violations. That's not very
User-friendly.

Answer 1: you create a unique index on the two fields (probably a Primary
Key). This is the only way to Protect Your Data.

Answer 2: you use logic in the form to prevent users from issuing the
same account number to the same user twice. The plan is that the users
should never see the db engine error message. How you use that depends
entirely on the application. One way is (eg) to remove used account
numbers from the list box so the users don't even see what they can't do.
Another way is to query the table prior to writing the new record and, if
it's not available, putting up a nice error message like "I'm sorry Dave,
I can't do that..." Another way (if it's appropriate) is to make the
users jump through hoops to get an existing account number so duplicate
checking is just a step in that process. Etc...

Hope that helps


Tim F

 

[Sreedhar]

Hi Klatuu,

You should delete the form bound to tblJoin. If you allow that to be edited,
you will loose relationships.

This form is available to Users in Add mode only when creating records. And
when Users want to edit relationships, such as when one Customer quits one
on-going Account and may (or may not) join a new account, this process is
carefully filtered with enough checks. I've got that part working fine.

Also, Your form should be based on the tblCustomers with a subform based on
tblAccounts. ALL WITH BOUND FIELDS.

This idea sounds good. I must try this. Though I'm afraid it breaks my UI to
some extent. I tried the DLookUp solution with my existing forms, but
couldn't get it right. Now, I should make it work.

THANKS A LOT.

 

[Sreedhar]

Hi Tim,

I would urge you to start thinking about forms in terms of your users'
processes. Either they start with an account number, and have to allocate
a customer to it; or they start with a customer and choose an existing or
new account number to give him. In either case, you design your form to
follow that order of events.

Please don't think of forms as windows onto tables -- forms are for doing
things.

Thanks for the tip. I can't agree more on this. My question is designed to
specifically highlight the problem, with just enough details ( or so I
thought) and at the same time not rambling on to recite every detail. My UI
matches your description of creating an easy flow of processes for the User.
Of course, there is always scope for betterment.

Answer 1: you create a unique index on the two fields (probably a Primary
Key). This is the only way to Protect Your Data.

Can you elaborate on "creating unique index on two fields" - I thought
making the two fields together as a Primary Key was sufficient. Am I missing
something here ?

Answer 2: you use logic in the form to prevent users from issuing the
same account number to the same user twice. The plan is that the users
should never see the db engine error message.

You're bang on target.That's what I'm at a loss as to how to do this.

One way is (eg) to remove used account
numbers from the list box so the users don't even see what they can't do.

Used Accounts may be required for a new Customer joining an existing
Account. That's one of the reasons for the "Many-to-Many" relationship.

Another way is to query the table prior to writing the new record and, if
it's not available, putting up a nice error message like "I'm sorry Dave,
I can't do that..."

I tried the recordset's Find(First) method, DLookup function, and the
BookMark method, but no single approach is working for all the cases:
------Customers with more than one Account
------Accounts with more than one Customer
------Groups of Customers with Groups of Account Plans etc etc....

Another way (if it's appropriate) is to make the
users **jump through hoops** to get an existing account number so duplicate
checking is just a step in that process. Etc...

Sorry, English is not my native language. Couldn't get what you mean. Can
you please elaborate ?

 

[Tim Ferguson]

Hi Tim,

My UI matches your description of creating an
easy flow of processes for the User.

If I were doing this, I'd probably have a form looking after all the
Customer details, with a cmdButton called something like "Allocate
Account Number"... this would bring up a dialog with one big list box, a
button called "New" and an OK and a Cancel. The list box would have a
rowsouce based on a query of all account numbers something like

SELECT ALL accountnum
FROM accounts
WHERE accountNum NOT IN
( SELECT accountNum
FROM allocations
WHERE allocations.customerNumber = 10933
)
ORDER BY accoutNum

The 10933 (or whatever) would be fed in from the current customer number
by a bit of code in the Form_Load event. That way, the user never gets
the chance to allocate an account number that is already allocated to the
customer.

Just because I would do it this way does not mean you should... it's just
an illustration of how getting the process itself right should fix the
problem at source.

Can you elaborate on "creating unique index on two fields" - I thought
making the two fields together as a Primary Key was sufficient. Am I
missing something here ?

A Primary Key _is_ a unique index, so yes it is sufficient. Just for
information, you can also create unique keys on other field(s) as well as
having a PK.

You're bang on target.That's what I'm at a loss as to how to do this.

See above.

Used Accounts may be required for a new Customer joining an existing
Account. That's one of the reasons for the "Many-to-Many"
relationship.

Yes... that kind of dawned on me after I finished posting

I tried the recordset's Find(First) method, DLookup function, and the

Well, another way to do this is simply to use the Form_BeforeUpdate and
check

sqlWhere = "CustomerID = " & txtCustomerID & _
" AND AccountNum = " & txtAccountNum

If DCount("*", "Allocations", sqlWhere) > 0 Then
' it's already allocated
MsgBox "something courteous"
Cancel = True ' prevents the form update happening

End If

Sorry, English is not my native language.

Gosh - I would not have known

Couldn't get what you mean. Can you please elaborate ?

What I meant was that for some jobs I deliberately make it hard for users
so that they actually have to want to do something dangerous...

If MsgBox("Are you really sure you want to do this?", _
vbYesNo or vbDefaultNo or vbIconSkullAndCrossbones, _
"Career-defining moment" _
) = vbYes Then

or just burying the function under two or three dialog boxes that have to
be filled out along the way.

In the end it's all down to how your users see the process and what they
have to do.

Best wishes


Tim F

 

[Sreedhar]

Hi Tim,

Thanks for the thorough tutorial. I now realise that I could have avoided
this problem altogether if I let the Users "Select" the AccountId instead of
"Typing-in".
I learnt it the hardway that designing a SMART UI is better than writing
lots of code to ensure data integrity.

Thanks again.

 

[Tim Ferguson]

I learnt it the hardway that designing a SMART UI is better than writing
lots of code to ensure data integrity.

The database is there to look after data integrity... the UI is there to
protect the user from making mistakes that will upset the database.

Best of luck with the project.


Tim F