Prevent Duplicates in form that is allowed in table

[ltodd155]

I have an access database for recording client information at our food pantry.

We have a client form to record basic information then a subform to record each visit.
The client and visits are in separate tables. The rec_date is indexed to each client and since all records are kept in a table duplicate dates are allowed in the table for individual clients.

I need a way to prevent the users from entering duplicate dates in the subform when recording a visit.

I currently use a click control to start a new record.

Example users creates new record for client then does not think they recorded a record and creates an additional record. I need a way to eliminate that additional record creation. thanks Luther (know enough about VBA to be dangerous"

[NeoPa]

Hi Luther.

May I suggest you look into indices for your tables. Indices can be made up of multiple fields where necessary so you can ensure that more than one visit is never entered for a Client AND a Date. The same for the Client table.

Let us know how you get on or if you'd like more help.

[PhilOfWalton]

I frequently use NeoPa's method with indexes, but there is a caveat with dates. Providing the date is a whole date and there is no time element in it, then there is no problem, but from the point of view of indexing, obviously 27 May 2017 is different from 27 may 2017 10AM.

If there is the slightest possibility that time could be entered, the simplest way is on the BeforeUpdate event to check the formatted date in the existing table with the formatted date just entered.
Don't forget that this is a string comparison, so you will need to use double quotes.

Phil

[ltodd155]

Thank You = works perfectly

[NeoPa]

Hi Phil.

If anyone were to require a situation where dates, specifically, were expected to be unique but that times were also entered, then it would surely make good sense to separate the dates and the times and simply include the date element in the index without the time element. I struggle to think of a scenario where that might be required but experience tells me that you can't always rely on experience to show you everything. New things come up all the time.

Or to put it another way, just because I can't think of something doesn't mean it can't occur.

[NeoPa]

@Luther.

It's always gratifying when someone asks for help then finds their own solution with just a hint of help.

Good for you :-)

[PhilOfWalton]

@ NeoPa

Ah, Neopa, if only you weren't so young (in comparison anyway). In the good old days people paid bills with cheques and I was treasurer of various sailing / yachting clubs. When subscriptions were due, we frequently got 40 or 50 cheques a day, and the situation, similar to Luther's, is that we didn't want duplicates posted against any Member.

So when the cheque was entered, the default date was Now() rather than Date(). Although the time element of the date was totally irrelevant, it kept the cheques in order. A list was prepared in date/time order and the cheques sent to the bank in that order.

By and large, the Bank processed the cheques in the same order, so it was very easy to reconcile the bank Statement against the Club's entries.

Obviously the list could have been sorted by Date & PaymentID Order, but the date & fictitious time seemed more flexible & intuitive.

Phil

[NeoPa]

That'd do it Phil :-) As I say, just because I couldn't think of a scenario doesn't mean there couldn't be one.

In such a scenario, assuming that Client & Date, rather than Client & DateTime, were required to be unique, splitting the date and time elements and including the date with the client ref in the PK would do the business for you.

[ltodd155]

Fortunately I use date only - so the table is based on client record + unique date. Thank you for your suggestion - I worked with access before I retired, unfortunately every time I try something new I need to be retrained.

[NeoPa]

Hi Luther.

You're very welcome. You didn't require retraining as much as a small hint. We all need that when we try something outside of our comfort-zone. Not everyone does of course, so good for you.