Prevent Duplicates

[history]

Hello Friends

I need a way to prevent entry for the name of a person in the same date.

I mean :
In my database ,,, I need if I entered a date for example 05/04/2010 ... and tried to enter the name twice ... a message will tell me that this person is already entered in this date ... but it will permit to enter the same person in another date.

Example :
Date ... 05/04/2010
Name .. Peter Adam

Date ... 05/04/2010
Name .. Peter Adam
Message appear : This name is already entered in this date

Date ... 06/04/2010
Name .. Peter Adam

Thank you

wait to hear from you

 

[CharlesWilliams]

I had to do the same thing with a database i was working on in work. Unfortunatly i do not have access to database untill Monday. I will post the information on Monday for you.

one way to do it would to use a 3rd field that constacates the date and name field together and then use a if then statement based on the 3rd field.

 

[SOS]

1. you could set up a multi-field index.

or

2. You can use a DCount on your form's BEFORE UPDATE event to check and cancel if necessary:

If DCount("*","TableNameHere", "[NameFieldNameHere]=" & Chr(34) & Me.YourNameControl & Chr(34) & " And [DateFieldNameHere]=#" & Me.YourDateControlHere & "#") <> 0 Then
   Cancel = True
   MsgBox "That name and date already exist.", vbExclamation, "Error"
End If

 

[John Big Booty]

Here's a solution that I have been playing with, that is based on a cascading Combo Box, except that the Combo is populated by an SQL statement that limits the list dependant of the date entered in the current record and what has already been entered against the same date previously, and then limits the selection.

Unfortunately it was a rather more complex problem than I first imaged, and is not as concise as SOS's solution. Any way here it is, you will need to examine the Form's On Current event and the Order Date's On Lost Focus event.

 

[Datagopher]

A simple solution would be to just primary keys on both the Date and Name fields in the table. This way it will not allow the same person to be entered on the same date but will allow the same person to be entered mutliple times as long as it's on different dates, which sounds like what you're trying to do.

Dan
Access Development

 

[John Big Booty]

A simple solution would be to just primary keys on both the Date and Name fields in the table. This way it will not allow the same person to be entered on the same date but will allow the same person to be entered mutliple times as long as it's on different dates, which sounds like what you're trying to do.

Dan
Access Development

Perhaps a sample DB demonstrating your solution is in order?

 

[Datagopher]

Hi John,

Here's a real quick database that I just threw together. Of course, using a name as part of the primary key is not the best idea because theres always a chance that there can be multiple people with the same name, so using something such as an employee id, client id etc in conjunction with the date would be better.

Dan
Access Development

 

[Datagopher]

Perhaps a sample DB demonstrating your solution is in order?

Hi John,

Here's a real quick database that I just threw together. Of course, using a name as part of the primary key is not the best idea because theres always a chance that there can be multiple people with the same name, so using something such as an employee id, client id etc in conjunction with the date would be better.

Dan
Access Development

 

[John Big Booty]

Not really a normalised DB though is it?

 

[Datagopher]

Not really a normalised DB though is it?

That's just a real quick example. You would want to have a customers, clients table or whatever the case would be and use something like an Auto-ID field and set the relationship on that field to an ID field in the table that contains the name and the date, which would be just a junction table. The name field would be numeric and could either be setup as a sub-form with the main table or have a drop-down for that field that would be set to the id but display the name.

Dan
Access Development

 

[John Big Booty]

OK. Sorry about that, now I see how that can work It's really a compound key.

 

[history]

1. you could set up a multi-field index.

or

2. You can use a DCount on your form's BEFORE UPDATE event to check and cancel if necessary:

If DCount("*","TableNameHere", "[NameFieldNameHere]=" & Chr(34) & Me.YourNameControl & Chr(34) & " And [DateFieldNameHere]=#" & Me.YourDateControlHere & "#") <> 0 Then
   Cancel = True
   MsgBox "That name and date already exist.", vbExclamation, "Error"
End If

Thank you very much friend
I'm happy about (multi field index)
but if you please to declare something :
what's (YourNameControl) & (YourDateControlHere) refere to ???

Thanks a lot

waiting friend

 

[Datagopher]

OK. Sorry about that, now I see how that can work It's really a compound key.

Exactly, you got it.

Dan
Access Development

 

[Datagopher]

Ok, let's break this down.

DCount("*","TableNameHere", "[NameFieldNameHere]=" & Chr(34) & Me.YourNameControl & Chr(34) & "

* - refers to all the fields in your table
TableNameHere - Refers to your record source, which could be a table or query.
NameFieldNameHere - Refers to the name of the field is going to have the criteria set.
Chr(34) - Single quotes. You could just use '
Me.YourNameControl - Is the name of the control that will have the value used in the criteria.

You would not need this with the multi-primary key though

Dan
Access Development

Thank you very much friend
I'm happy about (multi field index)
but if you please to declare something :
what's (YourNameControl) & (YourDateControlHere) refere to ???

Thanks a lot

waiting friend

 

[SOS]

Chr(34) - Single quotes. You could just use '

Datagopher - not quite correct. Chr(34) is " double quote. And using a ' can have issues if the data includes any so I don't use them and use the Chr(34) instead.

 

[history]

Thank you very much friends
now it's declared ....
Thanks a lot

 

[Datagopher]

Hi SOS,

You're right, it is double quotes. I figured it was single quotes because in VBA, you would use the single quotes around string variables not the double quotes. I'm not exactly sure what you mean by single quotes having issues. The way that a SQL statement for a string variable in VBA would be done would be like below.

Dim strName As String
If Not IsNull(Me!F_Name) Then
    strName = "[First_Name]= '" & Me!F_Name & "'"
End If

Datagopher - not quite correct. Chr(34) is " double quote. And using a ' can have issues if the data includes any so I don't use them and use the Chr(34) instead.

 

[Datagopher]

Glad you got it worked out.

Dan
Access Development

Thank you very much friends
now it's declared ....
Thanks a lot

 

[SOS]

Hi SOS,

You're right, it is double quotes. I figured it was single quotes because in VBA, you would use the single quotes around string variables not the double quotes.

umm, not true again. You CAN use single quotes but you can also use double quotes if you use Chr(34) OR use triple doubles (""") which I don't like.

I'm not exactly sure what you mean by single quotes having issues. The way that a SQL statement for a string variable in VBA would be done would be like below.

Dim strName As String
If Not IsNull(Me!F_Name) Then
    strName = "[First_Name]= '" & Me!F_Name & "'"
End If

The problems come if the name field has a single quote - like for O'Brien, etc. So, then using the Chr(34) doesn't have that issue. But if you use single quotes and the field has a single quote in the data it will then throw an error. So, you would either need to use my method or use the Replace function (which I find more confusing than simply using CHR(34))

 

[Datagopher]

True enough. I forgot about that. Probably because I have not had a need to use that in a very long time since ideally, you would want to be filtering on an ID for the name field in a normalized database.

1. you could set up a multi-field index.

or

2. You can use a DCount on your form's BEFORE UPDATE event to check and cancel if necessary:

If DCount("*","TableNameHere", "[NameFieldNameHere]=" & Chr(34) & Me.YourNameControl & Chr(34) & " And [DateFieldNameHere]=#" & Me.YourDateControlHere & "#") <> 0 Then
   Cancel = True
   MsgBox "That name and date already exist.", vbExclamation, "Error"
End If