By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,768 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

Code to verify if record exists

P: n/a
jpr
Hello,

I have a form on which I have a cmdbutton to copy a couple of fields
into another table (MASTER) using the SSN on the active form as
criteria.

In the active form (based on a tables called LIST) the ssn fields is
named CN while in the table where I want to copy this record, the field
is named SSN. Basically I am running an append query using code but
would like to add a code that will verify if a record with a similar
ssn is already present in the table MASTER. Welcome is my Main Menu. I
have foud this code on a forum and use in another form but just cannot
make it work. Can you help me?

This is the code I use to append data from table LIST to table MASTER.
Note that the SSN goes through certain update queries before it is
copied simply to remove dashed etc.

Dim MyDb As DAO.Database, MyRs As DAO.Recordset
Dim strCode As String
Dim strFilter As String
Dim stDocName As String
Dim stLinkCriteria As String
Set MyDb = CurrentDb
Set MyRs = MyDb.OpenRecordset("MASTER")

stDocName = "LIST"
''''

stLinkCriteria = "[c/n]=" & "'" & Me![C/N] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenQuery ("AppendtoMasterTemp")
DoCmd.OpenQuery ("UpdateSSNDashes")
DoCmd.OpenQuery ("RemoveDashes")
DoCmd.OpenQuery ("AppendMastertemptoMaster")
DoCmd.OpenQuery ("DeleteMasterTemp")

DoCmd.Close
DoCmd.OpenForm ("welcome")

This is the piece of code missing that I cannot figure where should go.

If Not IsNull(DLookup("[SSN]", "MASTER", "[SSN] = '" & Me![C/N] & "'"))
Then
MsgBox "Sorry! A record with this SSN is already in file. Retrive case
from Main Menu.", vbOKOnly, "Warning"
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close
Exit Sub
End If

Thank you.

Nov 6 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
G'day

jpr wrote:
This is the piece of code missing that I cannot figure where should go.

If Not IsNull(DLookup("[SSN]", "MASTER", "[SSN] = '" & Me![C/N] & "'"))
Then
MsgBox "Sorry! A record with this SSN is already in file. Retrive case
from Main Menu.", vbOKOnly, "Warning"
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close
Exit Sub
End If
I normally do something like

count = engine.retrieve( "select count(*) from table where key =
someValue", 0 )
where engine is a module and retrieve runs some sql.

and then enable the proceed button based on count = 0

eg
me.proceedCommand.enabled = (count=0)

Public Function retrieve(sql As String, Optional default As Variant =
"") As Variant
On Error GoTo ouch
Dim recordset As New ADODB.recordset
Dim result As Variant
result = default
With recordset
.ActiveConnection = CurrentProject.connection
.Open sql
If Not .EOF Then
result = .fields(0)
End If
End With
Set recordset = Nothing

normal:
retrieve = result
Exit Function

ouch:
Call Error.handle("engine", "retrieve(" & sql & ")",
Err.description, Err.Number)
Resume normal
End Function

Regards

Keith Hutchison

Nov 6 '06 #2

P: n/a
jpr
Thanks for your reply but I don't understand your code due to my very
poor knoledge of VBasic. Can you please explain to me how and where I
should add the tables names or fields etc? Thanks.
Keith Hutchison wrote:
G'day

jpr wrote:
This is the piece of code missing that I cannot figure where should go.

If Not IsNull(DLookup("[SSN]", "MASTER", "[SSN] = '" & Me![C/N] & "'"))
Then
MsgBox "Sorry! A record with this SSN is already in file. Retrive case
from Main Menu.", vbOKOnly, "Warning"
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close
Exit Sub
End If

I normally do something like

count = engine.retrieve( "select count(*) from table where key =
someValue", 0 )
where engine is a module and retrieve runs some sql.

and then enable the proceed button based on count = 0

eg
me.proceedCommand.enabled = (count=0)

Public Function retrieve(sql As String, Optional default As Variant =
"") As Variant
On Error GoTo ouch
Dim recordset As New ADODB.recordset
Dim result As Variant
result = default
With recordset
.ActiveConnection = CurrentProject.connection
.Open sql
If Not .EOF Then
result = .fields(0)
End If
End With
Set recordset = Nothing

normal:
retrieve = result
Exit Function

ouch:
Call Error.handle("engine", "retrieve(" & sql & ")",
Err.description, Err.Number)
Resume normal
End Function

Regards

Keith Hutchison
Nov 7 '06 #3

P: n/a

jpr wrote:
Thanks for your reply but I don't understand your code due to my very
poor knowledge of VBasic. Can you please explain to me how and where I
should add the tables names or fields etc? Thanks.
1.
Paste the retrieve code in the previous post in a module in your MS
access database

2. To find out is a ssn exists with the master table, where the field
name is ssn, and assuming we have a variable ssn with the value we are
looking for and the field is text

dim count as integer
count = retrieve( "select count(*) from master where ssn = '" & ssn &
"', 0 )
if count 0 then
' do something different because we already have the ssn.
else
' do what we normally would do because we do not have the ssn
end if

Keith

Nov 7 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.