Validating data before adding a recordset

  • Thread starter Thread starter Walter
  • Start date Start date
W

Walter

Hello all!!

I am trying to teach myself Access, and VB for
applications, unfortunately, there is only so much you
can understand from a book...

Here's my question:

I am adding records to a table using recordsets... This
way, I am able to validate the data using VB code before
adding the recordset to the table...

The table I am adding the recordset to is set to
accept "No duplicates", so when I unknowingly try to add
a duplicate record I get an error...

What I want to do is check the value of the field against
all other values already stored in same field of the
table before I add the recordset to the table.

This way, I can validate the data in the field before it
is added, thus avoiding the error...

Your help is greatly appreciated....

Thx., & Rgds.,
Walter
 
Walter said:
I am trying to teach myself Access, and VB for
applications, unfortunately, there is only so much you
can understand from a book...

Here's my question:

I am adding records to a table using recordsets... This
way, I am able to validate the data using VB code before
adding the recordset to the table...

The table I am adding the recordset to is set to
accept "No duplicates", so when I unknowingly try to add
a duplicate record I get an error...

What I want to do is check the value of the field against
all other values already stored in same field of the
table before I add the recordset to the table.

This way, I can validate the data in the field before it
is added, thus avoiding the error...
Click to expand...


A couple of ways to go about this. One is to use the
DLookup fuction:

If IsNull(DLookup("somefield", "thetable", _
"keyfield = " & keyvalue)) Then
' not a dup
Else
' this is a dup
End If

Another way is to execute an Insert Into query to add all
the fields and check to see if it failed:

Set dbs = CurrentDb()
strSQL = "INSERT INTO thetable (fielda, fieldb, . . .) " _
& "VALUES(" & valuea & "," valueb & "," . . . & ")"
db.Execute strSQL
If db.RecordsAffected = 0 Then
'it was a dup
Else
'not a dup, already added to the table
End If

Checking RecordsAffected is a very coarse way to do this.
More refined is to check specific errors:

. . .
On Error Goto ErrorHandler
db.Execute strSQL, dbFailOnError
'not a dup, already added to the table
. . .
Done:
rs.Close : Set rs = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
Se;ect Case Err.Number
Case ???
'it was a dup
Resume somelabel
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume Done
End Select
End Sub
 
Or you could use the following:

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset(“tblTableâ€, dbOpenTable)

rst.Index = “PrimaryKey†'or what ever index you need to seek on

rst.Seek “=â€, 123 'value of the primarykey to find
If rst.NoMatch Then 'value has not been found
rst.AddNew
rst!Number = "123" 'add the key value, not required for autonumber
Else
rst.Edit
End If

rst!Field2 = "ABC"
rst!Field3 = "DEF"
rst.Update

rst.Close
Set rst = Nothing
 
Hello,

I tried utilizing seek as you suggested, but I need more
help with the syntax...

The table that im adding recordset to is: tblDrivers
The field that I want to check for duplicates is: drvssn
The unbound form that I am using is: frmDrivers
The field in the unbound form is also: drvssn

Thx for your help...
Rgds.,
Walter
 
The table that im adding recordset to is: tblDrivers
The field that I want to check for duplicates is: drvssn
The unbound form that I am using is: frmDrivers
The field^Wcontrol in the unbound form is also: drvssn
Click to expand...

If DCount("*", "tbldrivers", _
"drvssn = """ & me.drvssn & """") > 0 Then
' it is already there

Else
' go ahead

End if

If the drvssn is a numeric field then you can get rid of all the embedded
quote marks.



Hope that helps


Tim F
 
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 SSN field of the
unbound form will not generate a duplicate entry...

The name of the unbound form 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...

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

'9.) Validate for duplicate SSN using DCount***

ElseIf DCount("*", "tbldrivers", "drvssn = """ &
Me.drvssn & """") > 0 Then
MsgBox "You have entered a S.S.N. that is already in
use"
drvssn.SetFocus
rctDriverAdd.Close
db.Close
Else
Exit Sub
End If
rctDriverAdd.AddNew
rctDriverAdd!drvssn = drvssn
DoCmd.Close
rctDriverAdd.Close
db.Close
End Sub
 
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
Click to expand...

Well, in that case the problem is with the rctDriverAdd object, not the
DCount() call...

I suspect that it is not being opened legally but I cannot remember the
last time I tried to open a dbOpenTable recordset so I don't know why or
how it might fail. What are you opening it for anyway? You don't seem to do
anything with it except for the .Addnew at the end and that would be
quicker and safer with a "INSERT INTO ..." command anyway. All the
recordset seems to be doing is slowing down the procedure and screwing the
network. If you really want to do it this way, you'll need some kind of

If rctDriverAdd Is Nothing Then ...

but I don't know what you'd do if it is indeed empty.

Hope that helps


Tim F
 
Well spotted Tim - the recordset is actually closed at that point! See below
....

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 <--- recordset closed

db.Close
Else
' Do nothing, record is not a duplicate***
Exit Sub
End If

rctDriverAdd.AddNew <--- attempt to call AddNew on closed recordset

--
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.
 
Well spotted Tim - the recordset is actually closed at that point!
Click to expand...

It wasn't me. The VBA interpreter actually found the problem, and told the
OP, and he told us!

Best wishes


Tim F
 
Back
Top