jpr wrote:
Hello,
I know that this is not the rule but need some help. My datbase has
three tables:
MASTER
TEMPLATES
FORMS
I have a form which is based on a table named MASTER.
I have a primary key set to autonumber and a field SSN which I would
like to add from this form at the same time also into the tables
TEMPLATE and FORMS.
I would like a code that verifies if in table MASTER, a record with
that SSN already exists. If YES, then it should only update the
modified data, save the records, and reopen the form on the recordset I
was working on based on that SSN (basically the last one opened).
If the SSN does not exist, well I would like a code that would append
all the data into the table MASTER and the SSN field into the same
field of the tables TEMPLATES and FORMS.
Any help?
I don't know if I understand your question. I would think SSN would be
a lookup/filter field. IOW, it does not have a control source.
In the BeforeUpdate event of SSN, you would check for the existence of
that SSN in Master. If so, move to that record. If it doesn't exist,
ask the op if they want to add the SSN. Update, then go to that record.
I have no idea what you want to update/add. Consider the following a
template.
Dim rstTemplate As Recordset
Dim rstForms As Recordset
Dim rst As Recordset
set rst = Me.Recordsetclone
rst.findfirst "SSN = '" & Me.SSN & "'"
if Not rst.NoMatch 'record found
'go to that record
Me.Bookmark = rst.Bookmark
ElseIF msgbox("SSN not found. Do you want to add it?",_
vbYesNo,"Add it?") = vbYes then
rst.AddNew
rst!SSN = Me.SSN
rst.Update
rst.bookmark = rst.LastModified
set rstTemplate = Currentdb.openrecordset("Template",dbopendynaset)
rstTemplate.AddNew
rstTemplate!SSN = Me.SSN
rstTemplate.Update
set rstForms = Currentdb.openrecordset("Forms",dbopendynaset)
rstrstForms.AddNew
rstrstForms!SSN = Me.SSN
rstrstForms.Update
Me.BookMark = rst.Bookmark
rst.close
rstTemplate.close
rstForms.Closet
set rst = Nothing
set rstTemplate = Nothing
set rstForms = Nothing
else
Cancel = True
Endif