To know if same value in the field already exists

[Imranis]

Hi,
I have a table called 'maintable'. It has 5000 plus entries of employees
with different fields. Every Employee has unique ID. One field is date of birth (dob). Normally more than one person can have the same date of birth. A form (maintable1) is created to enter data into this table. In order to avoid duplicate entry of the same employee again with all his data, I wish to know if the same date of birth (dob) which is being entered now already exists for any employee to verify if the employee being entered is the same, if not then I can enter the data of new employee with the same date of birth. So basically when I will enter data in a form for the dob field, if the value is the same, then a message that entry already exists, then the 'maintable' may open highlighting the same date of birth entry, so it can be verified if the same person is not entered again. thanks a lot.

 

[billmeye]

You can use Dcount() to see if there are any other entries with that dob and if so open a report based on your table maintable only listing those entries. Place the code in the forms Before Update event.

If Me.NewRecord And Dcount("*","maintable","[DOB]=#" & Me.DOB & "#") > 0 Then
Msgbox "There are other Employees with that dob.  Here is a list.", vbOkOnly,"Multiple Employees Same DOB"
DoCmd.OpenReport "rptMaintable", acViewPreview, , "[DOB]=#" & Me.DOB & "#"
End If

 

[Mihail]

Manage this at table level by setting unique indexes on two or more fields (like FirstName, LastName and Birthday).

Normally, each one from the employes should have a unique numeric code used in your Country.
If you have a field like this, select Indexed - No duplicates for that field, at table level, and this should be enough.

 

[Imranis]

You can use Dcount() to see if there are any other entries with that dob and if so open a report based on your table maintable only listing those entries. Place the code in the forms Before Update event.

If Me.NewRecord And Dcount("*","maintable","[DOB]=#" & Me.DOB & "#") > 0 Then
Msgbox "There are other Employees with that dob.  Here is a list.", vbOkOnly,"Multiple Employees Same DOB"
DoCmd.OpenReport "rptMaintable", acViewPreview, , "[DOB]=#" & Me.DOB & "#"
End If

Thanks for the reply. I created the report "rptMaintable", the code worked only for entry, then it stopped working altogether, now if I enter record with duplicate Date of Birth, the message is not coming up. I think there is some slight problem.

 

[Imranis]

Manage this at table level by setting unique indexes on two or more fields (like FirstName, LastName and Birthday).

Normally, each one from the employes should have a unique numeric code used in your Country.
If you have a field like this, select Indexed - No duplicates for that field, at table level, and this should be enough.

Thanks for reply. But I do need. duplicates of date of birth because many people have same date of birth. Indexing with No duplicate of field does not serve the purpose. thanks for reply again.

 

[Mihail]

It is not easy for my English to explain this.

I understand (and is usual logic) that many peoples have the same birthday. And the same first name. And the same last name.
But is a very low probability that 2 persons to have the same birthday and the same first name and the same last name and to work in the same factory.

Access allow you to define an index that hold all this fields as once.
So, you can have duplicate enters for birthday, first name or last name.
But you can't have duplicates for all 3 fields at once.

So you can't have 2 records where the birthday is the same and the first name is the same and the second name is the same.

Hope you understand what I mean.

 

[Imranis]

It is not easy for my English to explain this.

I understand (and is usual logic) that many peoples have the same birthday. And the same first name. And the same last name.
But is a very low probability that 2 persons to have the same birthday and the same first name and the same last name and to work in the same factory.

Access allow you to define an index that hold all this fields as once.
So, you can have duplicate enters for birthday, first name or last name.
But you can't have duplicates for all 3 fields at once.

So you can't have 2 records where the birthday is the same and the first name is the same and the second name is the same.

Hope you understand what I mean.

Thanks I understand that. But purpose is to avoid re-entering all the data again & again for the same person and the check is to be placed on only
one filed that is date of birth. So If entry is made with the same date of birth again, message pops up and tells that with the same date of birth another person is already entered, his particulars will be checked with the help of that report mentioned in code. If not, then the entry will be made. Thanks again.

 

[billmeye]

Thanks for the reply. I created the report "rptMaintable", the code worked only for entry, then it stopped working altogether, now if I enter record with duplicate Date of Birth, the message is not coming up. I think there is some slight problem.

The code is set up to only check when you are creating a new record, not when you are editing an existing record. If you want it to fire any time, remove the test for Me.NewRecord.

 

[Imranis]

The code is set up to only check when you are creating a new record, not when you are editing an existing record. If you want it to fire any time, remove the test for Me.NewRecord.

thanks. it is working. only last question on this. if the any other field on
the table is text not the date, same code will work or there will be some
change in it, if so, please help. thanks again.

 

[billmeye]

When you are working with a text field you will need to surround the variable with single quotes.

Dcount("*","maintable","[fldText]='" & Me.fldText & "'")
...
DoCmd.OpenReport "rptMaintable", acViewPreview, , "[fldText]='" & Me.fldText& "'"