Add and update data to multiple tables

[jpr]

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?

[salad]

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.Recordsetclo ne
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.LastModifie d

set rstTemplate = Currentdb.openr ecordset("Templ ate",dbopendyna set)
rstTemplate.Add New
rstTemplate!SSN = Me.SSN
rstTemplate.Upd ate

set rstForms = Currentdb.openr ecordset("Forms ",dbopendynaset )
rstrstForms.Add New
rstrstForms!SSN = Me.SSN
rstrstForms.Upd ate

Me.BookMark = rst.Bookmark
rst.close
rstTemplate.clo se
rstForms.Closet
set rst = Nothing
set rstTemplate = Nothing
set rstForms = Nothing

else
Cancel = True
Endif

[jpr]

Hello, thank you for your help.
I will try to summarize what I am trying to achieve with my database
which will handle a list of clients of a small insurance company.

My program has three main tables:

MASTER
TEMPLATES
FORMS

All three tables have different fields with the exception of the field
ID which is the primary key (auto number) and the field SSN which I
have decided to place in all three tables.
Each record (in my case each Client) will be recognized by the SSN and
duplicates are not allowed.

I will use a form named MASTER with its record source to the table
MASTER, to add a new record or to modify existing ones. To add a new
record I use the following code behind a command button from a Menu
form:

DoCmd.OpenForm ("Master")
DoCmd.GoToRecor d , , acNewRec

To modify a record I use a list box with source to a query.

What I am trying to achieve is a way to save my data at the same time
in the above three tables.

In the tables TEMPLATES and FORMS only the SSN will be saved unless
that SSN already exists (I will need a code to verify if that SSN has
already been entered. If no, the SSN can be added to TEMPLATES and
FORMS tables).
In the table MASTER any new or modified data should be saved. The SSN
field in my MASTER form is the first field to be completed and has a
code that on the after update checks if a record with that SSN has
already been entered. If yes, it closes the form.

The code I have simply saves the data into the table MASTER.

What I would like to save the data and then return to the record I had
active in my form MASTER.

Is there a way I can do this by adding a code some where?
Thanks.

[salad]

jpr wrote:

Hello, thank you for your help.
I will try to summarize what I am trying to achieve with my database
which will handle a list of clients of a small insurance company.

My program has three main tables:

MASTER
TEMPLATES
FORMS

All three tables have different fields with the exception of the field
ID which is the primary key (auto number) and the field SSN which I
have decided to place in all three tables.
Each record (in my case each Client) will be recognized by the SSN and
duplicates are not allowed.

I will use a form named MASTER with its record source to the table
MASTER, to add a new record or to modify existing ones. To add a new
record I use the following code behind a command button from a Menu
form:

DoCmd.OpenForm ("Master")
DoCmd.GoToRecor d , , acNewRec

To modify a record I use a list box with source to a query.

What I am trying to achieve is a way to save my data at the same time
in the above three tables.

In the tables TEMPLATES and FORMS only the SSN will be saved unless
that SSN already exists (I will need a code to verify if that SSN has
already been entered. If no, the SSN can be added to TEMPLATES and
FORMS tables).
In the table MASTER any new or modified data should be saved. The SSN
field in my MASTER form is the first field to be completed and has a
code that on the after update checks if a record with that SSN has
already been entered. If yes, it closes the form.

The code I have simply saves the data into the table MASTER.

What I would like to save the data and then return to the record I had
active in my form MASTER.

Is there a way I can do this by adding a code some where?
Thanks.

Hmmmm...Maybe in the AfterUpdate event of the form open up a recordset
for Templates and Forms and search for the SSN in each. If they don't
exist, do an AddNew, set SSN to the MasterSSN, and then Update.

I wouldn't know how you plan to update a master SSN if it changes...lets
say from 111-222-3333 to 111-222-3334? You'll need to update the
Templates and Forms too.

I kinda understand your dilemma. It's a bit difficult to describe fully
all of the nuances w/o getting the reader overwhelmed and still make sense.