current event and data sheet form and Dcount

  • Thread starter Thread starter 123
  • Start date Start date
1

123

thank you

I want to use Dcount to disply msgbox if field ID is enter before
(dublicated) and this code will be in data sheet form with ((current))
event..
Data sheet form properties: allow addition= no and the data sheet form is
sub form
I have try but my code display message for every record:
Private Sub Form_Current()
On Error Resume Next
If (DCount("*", "tblname", "ID= ID") >0) Then
msgbox "this number is enter"
End If

End Sub
==
I don't want to use active control because I want to scroll the data sheet
from any control...
 
Hi,

I don't quite understand your question. Are you saying How do I stop a user
entering a duplicate field ID into my table? If so, simply set the field ID
indexed property (in the forms underlying table) to Yes(No Duplicates). In
fact, from the sound of it, is field ID used as the record ID? If so, it
could be the Primary Key and this property would be set to no duplicates
anyway.



HTH,

Neil.
 
Thank you :
but this not I want I want only to give user attention to remember that this
field enter before this time
not prevent duplicate..
again
when user scroll the record in data sheet form and move from record to
another if the code find that ID enter 2 time for example give me msgbox say
you have enter this number before:
I hope i pass information.
thank you
data sheet form properties allow addition =false
 
If you just want a warning message to advise a duplicate value would be
entered then right click on your form and go into design view. Right Click
on the textbox which contains the field ID and then click on properties.
Under the Event tab, click in the field labeled Before Update. You will
notice a button appears to the right hand side with 3 dots ..., click on
there and then select Code Builder and then click OK. Paste the code below
into the 2 lines that have been created.

The line below is created automatically:

Private Sub fieldID_BeforeUpdate(Cancel As Integer)

Paste the following lines below the line that is created above:

Dim rsClone As DAO.Recordset
' Clone the form recordset
Set rsClone = Me.RecordsetClone
' Find a match for the value the user has just entered
rsClone.FindFirst "fieldID = " & Me.fieldID
' Check the result of FindFirst
If Not rsClone.NoMatch Then
' A match has been found so inform user of a duplicate value
MsgBox "'" & Me.fieldID & "' has already been entered.", _
vbOKOnly + vbInformation, "Duplicate Data"
End If

The code above, needs you to reference the DAO object library in order to
run correctly. To do this, goto the menu bar at the top and click on Tools
and then click on References. Scroll down the list in this window until you
see the Microsoft DAO reference. On my computer it is Microsoft DAO 3.6
Object Library. You may have this version or a previous version, but the
code will work with a previous version also. Click the checkbox next the
this description (on the left) and then click OK. Now close that window (can
goto File and then Close and return to Access). This will now warn the user
that a duplicate value is going to be entered.

HTH,

Neil.
 
Thank you for you answer:
This Work Excellent under before update event thank you
=======================================
I want to ask you can I use this code in lost focus event or form delete
because my form will not using for data entry its only as displaying data
form will be (allow addition =false).
thank you
======================================
 
You can use the code where ever you need to as long as the code is in an
event in the forms module. The Me keyword in the code means 'This Form'. You
could actually replace Me with the name of the form and the code could be
used anywhere (but the fom would need to be open for the code to run
successfully) but there would be no logic in doing this as the check is
really for the form only.

Dim rsClone As DAO.Recordset
' Clone the form recordset
Set rsClone = Forms!frmSomeForm.Controls
' Find a match for the value the user has just entered
rsClone.FindFirst "fieldID = " & Forms!frmSomeForm!fieldID
' Check the result of FindFirst
If Not rsClone.NoMatch Then
' A match has been found so inform user of a duplicate value
MsgBox "'" & Forms!frmSomeForm!fieldID & "' has already been
entered.", _
vbOKOnly + vbInformation, "Duplicate Data"
End If

HTH,


Neil.
 
Thank for your time:
Dim rsClone As DAO.Recordset
' Clone the form recordset
Set rsClone = Me.RecordsetClone
' Find a match for the value the user has just entered
rsClone.FindFirst "fieldID = " & Me.fieldID
' Check the result of FindFirst
If Not rsClone.NoMatch Then
' A match has been found so inform user of a duplicate value
MsgBox "'" & Me.fieldID & "' has already been entered.", _
vbOKOnly + vbInformation, "Duplicate Data"
End If
=====================================================================
When use the above code with current event of data sheet form the msgbox
display for every record (single record = unique) What I want display if
there duplicate only

- It is work only with (before update event.)

- not work with data sheet form (current event) when move from record to
record. using arrow key.

again thank you for your time

=====================================================================
The last code you send is
Dim rsClone As DAO.Recordset
' Clone the form recordset
Set rsClone = Forms!frmSomeForm.Controls
' Find a match for the value the user has just entered
rsClone.FindFirst "fieldID = " & Forms!frmSomeForm!fieldID
' Check the result of FindFirst
If Not rsClone.NoMatch Then
' A match has been found so inform user of a duplicate value
MsgBox "'" & Forms!frmSomeForm!fieldID & "' has already been
entered.", _
vbOKOnly + vbInformation, "Duplicate Data"
End If
=============================
display this error:
run time error 13
type mismatch
========================
 
Back
Top