Validate data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form folks use for data entry. I would like to have the system
check to be sure the record they are adding is not already present. So if
record uid 1 - blue- red - green exists if someone is adding a record then it
checks to be sure blue- red- green is not present prior to saving the new
record. How can that be done?
 
I have a form folks use for data entry. I would like to have the system
check to be sure the record they are adding is not already present. So if
record uid 1 - blue- red - green exists if someone is adding a record then it
checks to be sure blue- red- green is not present prior to saving the new
record. How can that be done?
Click to expand...

Do you want to *prevent* duplicates, or just warn the user?

You can use the Form's BeforeUpdate event to do so:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the form's record source query
rs.FindFirst "[fieldX] = '" & Me.FieldX & "'"
If rs.RecordCount = 0 Then
' do nothing, it's a brand new record
Else
MsgBox "This record already exists", vbOKOnly
Cancel = True ' cancel the addition
End If
rs.Close
Set rs = Nothing
End Sub

You can set the Form's Bookmark property equal to the Bookmark property if you
want to jump to the record that has been found.

John W. Vinson [MVP]
 
I do not want them to be able to enter it. I will try what is below. Thanks.

John W. Vinson said:
I have a form folks use for data entry. I would like to have the system
check to be sure the record they are adding is not already present. So if
record uid 1 - blue- red - green exists if someone is adding a record then it
checks to be sure blue- red- green is not present prior to saving the new
record. How can that be done?
Click to expand...

Do you want to *prevent* duplicates, or just warn the user?

You can use the Form's BeforeUpdate event to do so:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the form's record source query
rs.FindFirst "[fieldX] = '" & Me.FieldX & "'"
If rs.RecordCount = 0 Then
' do nothing, it's a brand new record
Else
MsgBox "This record already exists", vbOKOnly
Cancel = True ' cancel the addition
End If
rs.Close
Set rs = Nothing
End Sub

You can set the Form's Bookmark property equal to the Bookmark property if you
want to jump to the record that has been found.

John W. Vinson [MVP]
Click to expand...
 
I set rs.FindFirst "[fieldX] = '" & Me.FieldX & "'" to be FieldX = to a name
of a control on my form. It errors out. I tried leaving fieldX as is and it
errors out. What am I doing wrong?


John W. Vinson said:
I have a form folks use for data entry. I would like to have the system
check to be sure the record they are adding is not already present. So if
record uid 1 - blue- red - green exists if someone is adding a record then it
checks to be sure blue- red- green is not present prior to saving the new
record. How can that be done?
Click to expand...

Do you want to *prevent* duplicates, or just warn the user?

You can use the Form's BeforeUpdate event to do so:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the form's record source query
rs.FindFirst "[fieldX] = '" & Me.FieldX & "'"
If rs.RecordCount = 0 Then
' do nothing, it's a brand new record
Else
MsgBox "This record already exists", vbOKOnly
Cancel = True ' cancel the addition
End If
rs.Close
Set rs = Nothing
End Sub

You can set the Form's Bookmark property equal to the Bookmark property if you
want to jump to the record that has been found.

John W. Vinson [MVP]
Click to expand...
 
I set rs.FindFirst "[fieldX] = '" & Me.FieldX & "'" to be FieldX = to a name
of a control on my form. It errors out. I tried leaving fieldX as is and it
errors out. What am I doing wrong?
Click to expand...

I don't know... because I a) don't know what you did, b) don't know what the
error might have been, and c) don't know any of your fieldnames.

What are the names of the fields in your table?
What is the name of the control on the Form?
What is your actual code?
What event did you use for the code?

John W. Vinson [MVP]
 
Back
Top