Validating data 3

[maximo1970]

I have a form that inputs data into a single table, one of the fields needs to be unique. I need to check after the field has been updated on the form that it is unique and produce an error message if it is not. Is this possible at all?

 

[DrSimon]

The simplest thing is to index the field in the table as unique. Access will then prevent any further entries.

 

[maximo1970]

I've already done that and Access produces and error only when the form is either closed or attempts to save the data to the table. Which is not what I'm after, I need to perform a check before the form is either saved or closed as the field is the first of many that need to be updated and the users will get a little pissed if they have to re-type the entry.

I may have to change my direction on this if it's not possible, but I'm sure it is.

 

[DrSimon]

Yes it is possible and there must be lots of ways of doing this, here is how I tend to approach this. You need to use the Before Update event in VBA on the field concerned. In the example below the Table is called 'A' and the field you are concerned about is 'ID'
Obviously you can change the message after msgbox . The use will need to press Escape to clear the field before continuing. POst again if you need any more help
Simon Rouse

Private Sub ID_BeforeUpdate(Cancel As Integer)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim SQLStr As String
Set dbs = CurrentDb
SQLStr = "SELECT A.ID FROM A WHERE ((A.ID)=""" & [ID] & """"
Set rst = dbs.OpenRecordset(SQLStr)
If rst.RecordCount > 0 Then
MsgBox "It's already there!"
DoCmd.CancelEvent
End If
rst.Close
dbs.Close

End Sub

 

[maximo1970]

Thanks Simon, that's what I've been looking for. I just coun't get it work how I wanted it.

Thanks again

Garry

 

[Pappalito]

I could not get this solution to work on my Db until I removed the data type lines Dim dbs As DAO.Database
Dim rst As DAO.Recordset. Once they were removed, it worked. Thanks for the post Dr. Simon.

 

[PHV]

Pappalito, you had to reference Microsoft DAO 3.x library.
Anyway, what about something like this ?
Private Sub ID_BeforeUpdate(Cancel As Integer)
If DCount("[Unique field]", "[Table name]", "[Unique field]='" & [Control name] & "'") > 0 Then
MsgBox "Duplicate !"
DoCmd.CancelEvent
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244