Code to add and verify existing data
- From: "jpr" <jprma@xxxxxx>
- Date: 31 May 2006 01:22:22 -0700
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.
.
- Prev by Date: Re: Looking for Expression using date conditions.
- Next by Date: Re: Refreshing Referenced Module
- Previous by thread: query
- Next by thread: Make linked table have a relative path
- Index(es):
Relevant Pages
|
