469,314 Members | 2,182 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,314 developers. It's quick & easy.

Code to add and verify existing data

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
0 1196

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by David Smith | last post: by
1 post views Thread by Tom Bianchi | last post: by
19 posts views Thread by Ingo Linkweiler | last post: by
232 posts views Thread by robert maas, see http://tinyurl.com/uh3t | last post: by
1 post views Thread by netrudra2 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.