Preventing data entry duplicates

[mor]

Hi Guys,

New programmer here so please be kind on my terrible errors!

I'm trying to prevent duplicate data being entered into a given database and need some help. I've wrote the following code in the after update event of the "date_vl" control, which is after the input of the product control.


Dim criteria As String
critère = _
"Tbl_VL.product = " & Me.product & " " & _
"AND Tbl_VL.vl_date = " & Me.vl_date

If DCount("VL_id", "Tbl_VL", critère) > 0 Then
MsgBox "Il y existe déjà un enregistrement pour ce produit et la date associée."
Me.Undo
End If

End Sub


The idea is that the code references the Tbl_
Dim criteria As String
criteria = _
"Tbl_VL.produit = " & Me.produit & " " & _
"AND Tbl_VL.vl_date = " & Me.vl_date

If DCount("VL_id", "Tbl_VL", criteria) > 0 Then
MsgBox "Il existe déjà un enregistrement pour ce produit et la date associée."
Me.Undo
End If


End Sub


The idea is that if the product and the associated date exist in table "Tbl_VL", then a message box will appear. Yet when I enter data on purpose that should trigger the coding, nothing happens.

Any help would be greatly appreciated.

MOR

 

[pbaldy]

For starters, it looks like you need some delimiters:

http://access.mvps.org/access/general/gen0018.htm

Also, I'd use the before update event and use this to stop the update:

Cancel = True

 

[mor]

Thank you very much

 

[pbaldy]

Happy to help!

 

[mor]

I've tried running this woth the following code yet I'm getting hit with error #3464: Type of data is incompatible in the criteria expression.

Also why is it better to run in the before update? Thank you!

Private Sub vl_date_AfterUpdate()


Dim criteria As String
criteria = _
"(Tbl_VL.produit = '" & Me.produit & "')" & _
"AND (Tbl_VL.vl_date = #" & Me.vl_date & "#)"

If DCount("VL_id", "Tbl_VL", criteria) > 0 Then
MsgBox "Il existe déjà un enregistrement pour ce produit et la date associée."
Me.Undo
End If


End Sub

 

[pbaldy]

You haven't said what the data types of the 2 fields in the criteria are. You're treating the first as text and the second as a date. Also, when you rewrote it you added parentheses for some reason, but dropped the space before AND.

 

[mor]

Yeah sorry I was playing around trying to get it to work. How do I threfore tell VBA what the two data types are? The field Tbl_VL.produit is a text field and the field Tbl_VL.vl_date is a date field and then I have contained this all within a string?

Sorry about this... I ahve deleted the brackets and reintroduced the spacing!

 

[pbaldy]

Does this work?

criteria = "Tbl_VL.produit = '" & Me.produit & "' AND Tbl_VL.vl_date = #" & Me.vl_date & "#"

 

[mor]

Dim criteria As String
criteria = "Tbl_VL.produit = '" & Me.produit & "' AND Tbl_VL.vl_date = #" & Me.vl_date & "#"

If DCount("VL_id", "Tbl_VL", criteria) > 0 Then
MsgBox "Il existe déjà un enregistrement pour ce produit et la date associée."
Me.Undo
End If

No for some reason it returns the same error.

 

[pbaldy]

Can you post the db here? Or use this to see what the criteria is:

http://www.baldyweb.com/ImmediateWindow.htm

 

[mor]

It won't let me post the database as there is a security token missing?

But running the print returns this:


Tbl_VL.produit = 1 AND Tbl_VL.vl_date = #01/07/2013#

The code now runs as I've removed the apostrophes for the first field but it doesn't actually filter the results. I put in a specific value that should trigger the code (with a same date and product number) to return the message box saying that the record exists but it actually passes. Any help or would you need to see the database ?

 

[pbaldy]

The code you posted should have put apostrophes around the 1, which would be necessary for a text field. Are you sure we have the right code here?

 

[pbaldy]

See if this helps post the db:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

Also, if you use non-US date formats:

http://allenbrowne.com/ser-36.html#Format

 

[mor]

Hi PBaldy,

I've just run the code and you were right, the VBA recognises the code as an american date format and passes the date entered as an american date. Therefore, if I had a date in my table of 01/07/2013 and then re-entered this date into a control (as seen before) then the afterupdate event vba code recognises the date as 07/01/2013 and declares that this date doesn't exist in the database and passes it as a result! The date then enters the database as 01/07/2013!

How can I overcome this?

Thanks,
MOR

 

[pbaldy]

Did Allen's method not work?

 

[mor]

Dim criteria As String
criteria = "Tbl_VL.produit = '" & Me.produit & "' AND Tbl_VL.vl_date = #" & Me.vl_date & "#"

If DCount("VL_id", "Tbl_VL", criteria) > 0 Then
MsgBox "Il existe déjà un enregistrement pour ce produit et la date associée."
Me.Undo
End If


How do I get VBA to read the date as the user inputs it i.e European format input is then translated to american format before being interpreted. At the moment the european input date is being directly read as an american date.

Thanks,
MOR

 

[pbaldy]

Did you not see the second link in post 13?

 

[mor]

Yeah I struggled to understand it but will have a play around, but thank you very much for pointing me in the right direction. I would never have figured this out by myself!