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

Code to add and verify existing data

P: n/a
I need some help. I have a form named MASTER based on a table also
called MASTER.
A control of my form in names SSN which stores the client SSN.

On the same form I have placed a subform which has its record source to
a table named 21.

What I am trying to do is:
When I enter a new record in my form MASTER, the code should:
1) Verify if in table 21 a record with that SSN already exists.
If YES, skip the code and do not add the SSN. If NO, well, run the

2) At the same time, verify if a record with that SSN already exists in
table MASTER. If NO, add the data, if YES, abort the code and exit the

The form EForms is a menu form I use to access the records.
This is the code. Something is not going right, I think with the end if
functions. Any help? I have placed the code in the AfterUpdate event of
my control SSN.

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 = "MASTER"

stLinkCriteria = "[SSN]=" & "'" & Me![SSN] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

If Not IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
If IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Appendssa21tax")
End If
End If

If Not IsNull(DLookup("[SSN]", "MASTER", "[SSN] = '" & Me!SSN & "'"))
MsgBox "Sorry! A record with this SSN is already in file. Retrive case
from E-Forms Menu.", vbOKOnly, "Warning"
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If
Forms!eforms.lstPreInterview.Value = Null
DoCmd.OpenForm ("Eforms")
DoCmd.RunMacro ("CloseEforms")
DoCmd.OpenForm ("Eforms")
End If


May 31 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.