How to check duplicate value in a Date field and throw error message?

R

Ramona

Hi there,
Can anyone help me on this?
I m stuck at this particular code. Im wondering if you can find what I
did wrong. Any suggestions would be greatly appreciated.
Here it goes.
I have 1 table called, OrderDetails which consists of fields like
OrderDate, CustID, DeliveryDate, etc and I also have Customers table
which consists of all customer info.
In the EditOrderDetails subform , I would like to put a flag message
if the user have input the same delivery date for one particular
customer. BTW, this is one-to-many relationship between Customer and
OrderDetails.
So, in the subform EditOrderDetails (MainForm is called EditCustomers)
on DeliveryDate field, I put this code:

Private Sub DeliveryDate_BeforeUpdate(Cancel As Integer)

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Forms!EditCustomers!EditOrderDetails.Form!VisitDate) >
0 Then
Beep
MsgBox "The delivery date already exists"

End If

End Sub

For example, if there is already a record for customer witch CustID "1"
with a visit date of "07/10/2006" and a DeliveryDate of 07/13/2006
and the user enters on the form 07/13/2006 in the DeliveryDate field
for CustID 1 with 07/10/2006, it will flag the error message.

Please correct me if im wrong. This code is supposed to look at table
OrderDetails for Cust ID and VisitDate that matches with what is
currently active on the form. Then, it's supposed to check if the
DeliveryDate the user enters at the moment already exist in the table
OrderDetails by using Dcount function (counting if the specified record
0). If the user enters the DeliveryDate that already exists for that particular CustomerID and VisitDate, it should give the error message.
Click to expand...

The problem now is this code doesnt seem to work. I put the same
deliverydate and it doesnt pop up the message.

Help?
Thanks,
Ramona
 
D

Douglas J Steele

Try:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#")) >
0 Then

Note that this assumes that VisitDate only contains Date: no Time (in other
words, you didn't use the Now() function to populate it). If it does contain
time, you'll need to use:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] BETWEEN " &
Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#") & " And " & Format(DateAdd("d", 1,
Forms!EditCustomers!EditOrderDetails.Form!VisitDate), "\#mm\/dd\/yyyy\#")) >
0 Then




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ramona said:
Hi there,
Can anyone help me on this?
I m stuck at this particular code. Im wondering if you can find what I
did wrong. Any suggestions would be greatly appreciated.
Here it goes.
I have 1 table called, OrderDetails which consists of fields like
OrderDate, CustID, DeliveryDate, etc and I also have Customers table
which consists of all customer info.
In the EditOrderDetails subform , I would like to put a flag message
if the user have input the same delivery date for one particular
customer. BTW, this is one-to-many relationship between Customer and
OrderDetails.
So, in the subform EditOrderDetails (MainForm is called EditCustomers)
on DeliveryDate field, I put this code:

Private Sub DeliveryDate_BeforeUpdate(Cancel As Integer)

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Forms!EditCustomers!EditOrderDetails.Form!VisitDate) >
0 Then
Beep
MsgBox "The delivery date already exists"

End If

End Sub

For example, if there is already a record for customer witch CustID "1"
with a visit date of "07/10/2006" and a DeliveryDate of 07/13/2006
and the user enters on the form 07/13/2006 in the DeliveryDate field
for CustID 1 with 07/10/2006, it will flag the error message.

Please correct me if im wrong. This code is supposed to look at table
OrderDetails for Cust ID and VisitDate that matches with what is
currently active on the form. Then, it's supposed to check if the
DeliveryDate the user enters at the moment already exist in the table
OrderDetails by using Dcount function (counting if the specified record
0). If the user enters the DeliveryDate that already exists for that
Click to expand...
Click to expand...
particular CustomerID and VisitDate, it should give the error message.
 
R

Ramona

I tried that but now eventhough I put a different delivery date, it
keeps popping the error message to me. Why is that?
It's supposed to just flag the message if we put same delivery date for
customers whose custID and visitDate on the EditOrdeDetails form match
with the records in OrderDetails table. In other words, I dont want the
user to enter same or duplicate delivery date for a customer who has
already have that delivery date.

Any ideas?


Try:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#")) >
0 Then

Note that this assumes that VisitDate only contains Date: no Time (in other
words, you didn't use the Now() function to populate it). If it does contain
time, you'll need to use:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] BETWEEN " &
Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#") & " And " & Format(DateAdd("d", 1,
Forms!EditCustomers!EditOrderDetails.Form!VisitDate), "\#mm\/dd\/yyyy\#")) >
0 Then




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ramona said:
Hi there,
Can anyone help me on this?
I m stuck at this particular code. Im wondering if you can find what I
did wrong. Any suggestions would be greatly appreciated.
Here it goes.
I have 1 table called, OrderDetails which consists of fields like
OrderDate, CustID, DeliveryDate, etc and I also have Customers table
which consists of all customer info.
In the EditOrderDetails subform , I would like to put a flag message
if the user have input the same delivery date for one particular
customer. BTW, this is one-to-many relationship between Customer and
OrderDetails.
So, in the subform EditOrderDetails (MainForm is called EditCustomers)
on DeliveryDate field, I put this code:

Private Sub DeliveryDate_BeforeUpdate(Cancel As Integer)

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Forms!EditCustomers!EditOrderDetails.Form!VisitDate) >
0 Then
Beep
MsgBox "The delivery date already exists"

End If

End Sub

For example, if there is already a record for customer witch CustID "1"
with a visit date of "07/10/2006" and a DeliveryDate of 07/13/2006
and the user enters on the form 07/13/2006 in the DeliveryDate field
for CustID 1 with 07/10/2006, it will flag the error message.

Please correct me if im wrong. This code is supposed to look at table
OrderDetails for Cust ID and VisitDate that matches with what is
currently active on the form. Then, it's supposed to check if the
DeliveryDate the user enters at the moment already exist in the table
OrderDetails by using Dcount function (counting if the specified record
0). If the user enters the DeliveryDate that already exists for that
Click to expand...
Click to expand...
particular CustomerID and VisitDate, it should give the error message.
The problem now is this code doesnt seem to work. I put the same
deliverydate and it doesnt pop up the message.

Help?
Thanks,
Ramona
Click to expand...
Click to expand...
 
R

Ramona

I tried that but now eventhough I put a different delivery date, it
keeps popping the error message to me. Why is that?
It's supposed to just flag the message if we put same delivery date for
customers whose custID and visitDate on the EditOrdeDetails form match
with the records in OrderDetails table. In other words, I dont want the
user to enter same or duplicate delivery date for a customer who has
already have that delivery date.

Any ideas?


Try:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#")) >
0 Then

Note that this assumes that VisitDate only contains Date: no Time (in other
words, you didn't use the Now() function to populate it). If it does contain
time, you'll need to use:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] BETWEEN " &
Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#") & " And " & Format(DateAdd("d", 1,
Forms!EditCustomers!EditOrderDetails.Form!VisitDate), "\#mm\/dd\/yyyy\#")) >
0 Then




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ramona said:
Hi there,
Can anyone help me on this?
I m stuck at this particular code. Im wondering if you can find what I
did wrong. Any suggestions would be greatly appreciated.
Here it goes.
I have 1 table called, OrderDetails which consists of fields like
OrderDate, CustID, DeliveryDate, etc and I also have Customers table
which consists of all customer info.
In the EditOrderDetails subform , I would like to put a flag message
if the user have input the same delivery date for one particular
customer. BTW, this is one-to-many relationship between Customer and
OrderDetails.
So, in the subform EditOrderDetails (MainForm is called EditCustomers)
on DeliveryDate field, I put this code:

Private Sub DeliveryDate_BeforeUpdate(Cancel As Integer)

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Forms!EditCustomers!EditOrderDetails.Form!VisitDate) >
0 Then
Beep
MsgBox "The delivery date already exists"

End If

End Sub

For example, if there is already a record for customer witch CustID "1"
with a visit date of "07/10/2006" and a DeliveryDate of 07/13/2006
and the user enters on the form 07/13/2006 in the DeliveryDate field
for CustID 1 with 07/10/2006, it will flag the error message.

Please correct me if im wrong. This code is supposed to look at table
OrderDetails for Cust ID and VisitDate that matches with what is
currently active on the form. Then, it's supposed to check if the
DeliveryDate the user enters at the moment already exist in the table
OrderDetails by using Dcount function (counting if the specified record
0). If the user enters the DeliveryDate that already exists for that
Click to expand...
Click to expand...
particular CustomerID and VisitDate, it should give the error message.
The problem now is this code doesnt seem to work. I put the same
deliverydate and it doesnt pop up the message.

Help?
Thanks,
Ramona
Click to expand...
Click to expand...
 
D

Douglas J Steele

You're comparing the date on the form to the visitDate in the table. That's
not going to check the delivery date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ramona said:
I tried that but now eventhough I put a different delivery date, it
keeps popping the error message to me. Why is that?
It's supposed to just flag the message if we put same delivery date for
customers whose custID and visitDate on the EditOrdeDetails form match
with the records in OrderDetails table. In other words, I dont want the
user to enter same or duplicate delivery date for a customer who has
already have that delivery date.

Any ideas?


Try:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#")) >
0 Then

Note that this assumes that VisitDate only contains Date: no Time (in other
words, you didn't use the Now() function to populate it). If it does contain
time, you'll need to use:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] BETWEEN " &
Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#") & " And " & Format(DateAdd("d", 1,
Forms!EditCustomers!EditOrderDetails.Form!VisitDate), "\#mm\/dd\/yyyy\#")) >
0 Then




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ramona said:
Hi there,
Can anyone help me on this?
I m stuck at this particular code. Im wondering if you can find what I
did wrong. Any suggestions would be greatly appreciated.
Here it goes.
I have 1 table called, OrderDetails which consists of fields like
OrderDate, CustID, DeliveryDate, etc and I also have Customers table
which consists of all customer info.
In the EditOrderDetails subform , I would like to put a flag message
if the user have input the same delivery date for one particular
customer. BTW, this is one-to-many relationship between Customer and
OrderDetails.
So, in the subform EditOrderDetails (MainForm is called EditCustomers)
on DeliveryDate field, I put this code:

Private Sub DeliveryDate_BeforeUpdate(Cancel As Integer)

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Forms!EditCustomers!EditOrderDetails.Form!VisitDate)
0 Then
Beep
MsgBox "The delivery date already exists"

End If

End Sub

For example, if there is already a record for customer witch CustID "1"
with a visit date of "07/10/2006" and a DeliveryDate of 07/13/2006
and the user enters on the form 07/13/2006 in the DeliveryDate field
for CustID 1 with 07/10/2006, it will flag the error message.

Please correct me if im wrong. This code is supposed to look at table
OrderDetails for Cust ID and VisitDate that matches with what is
currently active on the form. Then, it's supposed to check if the
DeliveryDate the user enters at the moment already exist in the table
OrderDetails by using Dcount function (counting if the specified record
0). If the user enters the DeliveryDate that already exists for that
Click to expand...
particular CustomerID and VisitDate, it should give the error message.
The problem now is this code doesnt seem to work. I put the same
deliverydate and it doesnt pop up the message.

Help?
Thanks,
Ramona
Click to expand...
Click to expand...
Click to expand...
 
R

Ramona

Thanks Douglas.
Im new at this stuff so I really need help and specific example.

I changed the code to:
Private Sub DeliveryDate_BeforeUpdate(Cancel As Integer)
If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[DeliveryDate] = " &
Format(Forms!EditCustomers!EditOrderDetails.Form!DeliveryDate,
"\#mm\/dd\/yyyy\#")) > 0 Then
Beep
MsgBox "The delivery date already exists"

End If

End Sub

Still doesnt work. :( keeps popping up the message everytime I type
DeliveryDate.

You're comparing the date on the form to the visitDate in the table. That's
not going to check the delivery date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ramona said:
I tried that but now eventhough I put a different delivery date, it
keeps popping the error message to me. Why is that?
It's supposed to just flag the message if we put same delivery date for
customers whose custID and visitDate on the EditOrdeDetails form match
with the records in OrderDetails table. In other words, I dont want the
user to enter same or duplicate delivery date for a customer who has
already have that delivery date.

Any ideas?


Try:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#")) >
0 Then

Note that this assumes that VisitDate only contains Date: no Time (in other
words, you didn't use the Now() function to populate it). If it does contain
time, you'll need to use:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] BETWEEN " &
Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#") & " And " & Format(DateAdd("d", 1,
Forms!EditCustomers!EditOrderDetails.Form!VisitDate), "\#mm\/dd\/yyyy\#")) >
0 Then




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi there,
Can anyone help me on this?
I m stuck at this particular code. Im wondering if you can find what I
did wrong. Any suggestions would be greatly appreciated.
Here it goes.
I have 1 table called, OrderDetails which consists of fields like
OrderDate, CustID, DeliveryDate, etc and I also have Customers table
which consists of all customer info.
In the EditOrderDetails subform , I would like to put a flag message
if the user have input the same delivery date for one particular
customer. BTW, this is one-to-many relationship between Customer and
OrderDetails.
So, in the subform EditOrderDetails (MainForm is called EditCustomers)
on DeliveryDate field, I put this code:

Private Sub DeliveryDate_BeforeUpdate(Cancel As Integer)

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Forms!EditCustomers!EditOrderDetails.Form!VisitDate)
Click to expand...
0 Then
Beep
MsgBox "The delivery date already exists"

End If

End Sub

For example, if there is already a record for customer witch CustID "1"
with a visit date of "07/10/2006" and a DeliveryDate of 07/13/2006
and the user enters on the form 07/13/2006 in the DeliveryDate field
for CustID 1 with 07/10/2006, it will flag the error message.

Please correct me if im wrong. This code is supposed to look at table
OrderDetails for Cust ID and VisitDate that matches with what is
currently active on the form. Then, it's supposed to check if the
DeliveryDate the user enters at the moment already exist in the table
OrderDetails by using Dcount function (counting if the specified record
0). If the user enters the DeliveryDate that already exists for that
particular CustomerID and VisitDate, it should give the error message.

The problem now is this code doesnt seem to work. I put the same
deliverydate and it doesnt pop up the message.

Help?
Thanks,
Ramona
Click to expand...
Click to expand...
Click to expand...
 
R

Ramona

Hey, I think the code is working now!
*Big smile*
Thanks for your help, Douglas. Im going to do more tests on this code.

Ramona

Thanks Douglas.
Im new at this stuff so I really need help and specific example.

I changed the code to:
Private Sub DeliveryDate_BeforeUpdate(Cancel As Integer)
If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[DeliveryDate] = " &
Format(Forms!EditCustomers!EditOrderDetails.Form!DeliveryDate,
"\#mm\/dd\/yyyy\#")) > 0 Then
Beep
MsgBox "The delivery date already exists"

End If

End Sub

Still doesnt work. :( keeps popping up the message everytime I type
DeliveryDate.

You're comparing the date on the form to the visitDate in the table. That's
not going to check the delivery date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ramona said:
I tried that but now eventhough I put a different delivery date, it
keeps popping the error message to me. Why is that?
It's supposed to just flag the message if we put same delivery date for
customers whose custID and visitDate on the EditOrdeDetails form match
with the records in OrderDetails table. In other words, I dont want the
user to enter same or duplicate delivery date for a customer who has
already have that delivery date.

Any ideas?



Douglas J Steele wrote:
Try:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#")) >
0 Then

Note that this assumes that VisitDate only contains Date: no Time (in other
words, you didn't use the Now() function to populate it). If it does contain
time, you'll need to use:

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] BETWEEN " &
Format(Forms!EditCustomers!EditOrderDetails.Form!VisitDate,
"\#mm\/dd\/yyyy\#") & " And " & Format(DateAdd("d", 1,
Forms!EditCustomers!EditOrderDetails.Form!VisitDate), "\#mm\/dd\/yyyy\#")) >
0 Then




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi there,
Can anyone help me on this?
I m stuck at this particular code. Im wondering if you can find what I
did wrong. Any suggestions would be greatly appreciated.
Here it goes.
I have 1 table called, OrderDetails which consists of fields like
OrderDate, CustID, DeliveryDate, etc and I also have Customers table
which consists of all customer info.
In the EditOrderDetails subform , I would like to put a flag message
if the user have input the same delivery date for one particular
customer. BTW, this is one-to-many relationship between Customer and
OrderDetails.
So, in the subform EditOrderDetails (MainForm is called EditCustomers)
on DeliveryDate field, I put this code:

Private Sub DeliveryDate_BeforeUpdate(Cancel As Integer)

If DCount("[DeliveryDate]", "OrderDetails", "[Customers ID]=" &
Forms!EditCustomers!EditOrderDetails.Form!CustomersID & " AND
[VisitDate] =" & Forms!EditCustomers!EditOrderDetails.Form!VisitDate)

0 Then
Beep
MsgBox "The delivery date already exists"

End If

End Sub

For example, if there is already a record for customer witch CustID "1"
with a visit date of "07/10/2006" and a DeliveryDate of 07/13/2006
and the user enters on the form 07/13/2006 in the DeliveryDate field
for CustID 1 with 07/10/2006, it will flag the error message.

Please correct me if im wrong. This code is supposed to look at table
OrderDetails for Cust ID and VisitDate that matches with what is
currently active on the form. Then, it's supposed to check if the
DeliveryDate the user enters at the moment already exist in the table
OrderDetails by using Dcount function (counting if the specified record
0). If the user enters the DeliveryDate that already exists for that
particular CustomerID and VisitDate, it should give the error message.

The problem now is this code doesnt seem to work. I put the same
deliverydate and it doesnt pop up the message.

Help?
Thanks,
Ramona
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Simple error message for duplicate records 3
How to check whether the value in text field is Date type 1
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
Need a message box when a field is entered 5
Detecting text entered in a text field 2
Dlookup help for duplicate phone number entry and message box. 8
Determine if an unbound field is in a table 2
how to do date comparison between a list of different dates? 0

Top