Validating data using VB code in an unbound form

[Walter Bennett]

Hello All!!!

Im using an unbound form to add records to a table using
recordsets in VB...

I want to learn how to check for duplicate entries using
code before I add the recordset...

More specifically, I want to check the data entered into
the "drvssn" field of the unbound form against the data
stored in the table to make sure the data being entered
into the unbound form will not generate a duplicate
entry...

The name of the unbound form im using to validate and
enter data into the "tblDrivers" table is: "frmDrivers"

Using DCount was suggested, but when I add this code, I
get a runtime error 3420 Object Invalid or no longer set.

The debugger references my line of code:
rctDriverAdd.AddNew

When I remove the code for 9.) Validate for duplicate SSN
using DCount*** everything works OK... NO error...

Any ideas anyone?? Please feel free to modify my code as
you see fit...

Thx., & Rgds.,
Walter

My code reads as follows:

Private Sub Command29_Click()
Dim db As Database
Dim rctDriverAdd As Recordset

'*** Create a pointer to the database and a pointer to
the table ***
Set db = CurrentDb()
Set rctDriverAdd = db.OpenRecordset("tblDrivers",
dbOpenTable)

' ***Validation Sequence***
'8.) Validate SSN

If IsNull(drvssn) Or Len(drvssn) = 0 Then
MsgBox "Please enter the S.S.N."
drvssn.SetFocus
rctDriverAdd.Close
db.Close
Exit Sub
End If
'9.) Validate for duplicate SSN using DCount***

If DCount("*", "tbldrivers", "drvssn = """ &
Me.drvssn & """") > 0 Then
' Duplicate record found***
MsgBox "The S.S.N. you have entered is in already used"
drvssn.SetFocus
rctDriverAdd.Close
db.Close
Else
' Do nothing, record is not a duplicate***
Exit Sub
End If
rctDriverAdd.AddNew
rctDriverAdd!drvssn = drvssn
DoCmd.Close
rctDriverAdd.Close
db.Close
End Sub

 

[berk]

Here is a much easier way.

Dim dbs as database
dim rst as recordset
set dbs = currentdb
set rst = dbs.openrecordset("Select * from tbldrivers
where drvssn ='" & me.drvssn & "'")

if rst.recordcount > 0 then
msgbox "SSN already used"
end if

Then you can put the rest of your code in to do whatever.

 

[Brendan Reynolds]

Not sure why DCount() isn't working for you, but I prefer to do something
like the following. Please note that this is untested 'air-code', from
memory. It may not by syntactically correct, it is intended to demonstrate
the general approach rather than the detailed syntax, which can be found in
the help files. That said ...

Dim db As DAO.Database
Dim rst As DAO.Recordset
dim boolDuplicate As Boolean

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Count(*) As TheCount FROM SomeTable WHERE
SomeField = " & Me!SomeControl)
boolDuplicate = rst.Fields("TheCount") <> 0
rst.Close
if boolDuplicate Then
'it's a duplicate
Else
'it's not
End If

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Tim Ferguson]

Using DCount was suggested, but when I add this code, I
get a runtime error 3420 Object Invalid or no longer set.

Yes, but on a different line! There is an answer is the same thread.

Tim F

 

[Walter]

This works great!!!

Only thing is that I need to set a recordset pointer for
everyfield that I need to validate for a duplicate
entry...

Example:

set rctDupchkssn = dbs.openrecordset (Select * from
tbldrivers where drvssn ='" & me.drvssn & "'")

set rctDupchkdln = dbs.openrecordset (Select * from
tbldrivers where drvssn ='" & me.drvdln & "'")
Etc...

Other than having to type alot of code, it works GREAT!!
Thanx

If you have a way to avoid having to set recordset
pointers for each field being validated, I sure would
appreciate the info....

Again... Thx...
Rgds.,
Walter