Prevent Duplicate Order by date

[nomeepk]

Hi,

i need some help here and i m not a vba user but i do use basic macros.
I was to achieve something like this;

field one: ID (Primary key)
Field two; Name
Field three: date
and i got a little subform with these. as some kind of order sheet.

i want it to notify me for Duplicate Name but on a single date. when i enter address in it.
Let me try and explain it again.

One customer cant have more than one orders in a day. even if he makes a repeat purchase, it should notify that today we already got his another order. So i can simply leave the name as it is, but put a child record under the previous order.

but ignore any order which are not with the current date.

Hope that explains.

an Advance thanks.

[Rabbit]

There's no need for any code. Put a unique index on your name and date fields. That will prevent any entry with a duplicate name / date combination.

[NeoPa]

What you describe confuses me. If he cannot make another order the same day, how can a child record be put under the previous order?

Be that as it may, I suggest your first step is to look at the design of your table. A unique index that includes both those fields should be put in place. After that, it won't be possible to enter the same data twice. Check out Datebase Normalisation And Table Structures.

When you have a fundamentally sensible design, then you can consider if anything else is even required and, if so , what that is.

[nomeepk]

@rabbit Should i just change these two fields to Indexed (No Duplicates)?

[nomeepk]

@Neopa
Let me try to explain what i want to achieve:
I would always dispatch orders in the evening before closing.
so

You are a customer and You bought an item in the morning.

so i got your name and date and item (in subform)
(The order is not yet dispatched and its in my record)

Later before dispatch you will get one more item.

I already got your Name and today's date
but i will just put another item in your order so at the end it will be one order of two items.

The problem is:

I want it to notify me only if:
You the customer already have an order in Today's date. not in any old date.

[zmbd]

nomeepk:
Not exactly what rabbit is after, what he is suggesting is a composite-indexed key. Because you have not indicated which version of Access you are using, I'll give you the ACC2010 instructions:
Open the table you wish to have the composite index on in desgin view.
A context ribbon for "Table Tools" should be open
"Show/Hide" group
"Indexes"

Once selected, the "Indexes:___" where ___ = table name dialog will open... for simplicty, let's call this the Indexes Table.
If you have a primary key, you will have one entry in the table... "PrimaryKey"
Now look at the two fields you want in the index, in this case say [order_date] and [customer_id]

In the Indexes Table, under the heading [Index Name] make a new entry, let's call it "idx_dailyorders"
under the heading [Field Name] same row as idx_dailyorders, select the first field.
Now on the next row, leave [Index Name] BLANK; in the [Field Name] select the second field.
Now go back and select the index name
You will have a section at the bottom of the indexes table where you can set a few properties, in this case, Primary (if you already have a primary then = NO (I advise against composit keys for primary keys for most uses - that is a subject of much debate) Unique = Yes (obvious - No?) Ignore Nulls = No

And there you have it

You can also do this via SQL in the query editor, however, you have to use SQL, not the standard editor view - and this is not for the faint-of-heart: Create or modify tables or indexes by using a data-definition query

[NeoPa]

That makes more sense. I appreciate better what you're asking for. The answer stays the same, but it helps to understand the question anyway :-)

Z has given a very full answer to your question about Rabbit's suggestion. I was planning to myself, but he's already done a better job than I would have - albeit with a PrimaryKey that appears to be Primary = No (Giggles).

[zmbd]

Now go back and select the index name

Ah... but what you see is not the [Index Name]= "PrimaryKey" properties (^.^) - instead, I have selected the [Index Name]= "idx_DailyOrders" element; thus, you see the propeties for that key in the image.

If I had selected the element [Index Name]= "PrimaryKey" then you would have seen: Primary Key = Yes, Unique = Yes, and Ignore Nulls = No.

I should have drawn the orange indicators in the picture - for some reason they didn't come thru... arrrgh, (\./) .

(I'll fix that... stomp stomp stomp... where's that orange pen... KIDS!!! WHERE'D YOU PUT MY ORANGE BOX!!! Growl - ROAR - FLAMES (chuckle...) )

[neelsfer]

Hope i am not missing something, but this sounds risky to me to to use the surname and date.What happens if a different Mr Jones arrives?
Rather use some unique number or ID linked to this specific person, as well as the date.

[zmbd]

neelsfer
I certainly agree, notice in my example I use a field named "Customer_ID"
I should have explained that in my post at the time, thank you for bringing that back to point!

[nomeepk]

@Zmbd Thank you so much for you help. yes the problem solved.
and sorry for replying late. last night i actually tried and changed this unique to Yes. and it worked. then i didnt have network so could reply.

Thanks to you All. it really helped me and achieved the required result.