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
jpr
Hello,
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
appendquery.

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
form.

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
Else
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 & "'"))
Then
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
DoCmd.Close
End If
Forms!eforms.lstPreInterview.Value = Null
DoCmd.Close
DoCmd.OpenForm ("Eforms")
DoCmd.RunMacro ("CloseEforms")
DoCmd.OpenForm ("Eforms")
End If

Thanks.

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.