saving a record from a form to multiple tables

[Guest]

I want to be able to save some of the fields from a form to a second table as
the record is saved to the main table, is this possible? and if so how is it
done?

Thanks in advance.


Mabeline

 

[John W. Vinson]

I want to be able to save some of the fields from a form to a second table as
the record is saved to the main table, is this possible? and if so how is it
done?

Thanks in advance.

Well, first - is it desirable? It's almost NEVER either necessary nor
appropriate to store data redundantly! What's this second table and why do you
feel that you need to save the data twice?

That said... you can use the Form's BeforeUpdate event to do this. Open a
Recordset based on the second table, and use the AddNew method to create a new
record; populate it from the form, and use the Update method to save it. See
the VBA help for Recordset and AddNew for sample code.

John W. Vinson [MVP]

 

[Guest]

Hi John,

Thanks for your suggestion. Haven't tried your suggestion yet. Will do so
over this weekend and will respond with the outcome.

I'm not creating a record with exactly the same data in the second table, it
is just one field at this stage. So there is no redundancy. The second table
is to allow me to add data later using the field duplicated from the first
table as the key. I am trying things this way to hopefully make it easier
when adding the later data.

If it doesn't work the way I am thinking then I will look at alternatives.


Thanks
Mabeline.

 

[John W. Vinson]

I'm not creating a record with exactly the same data in the second table, it
is just one field at this stage. So there is no redundancy. The second table
is to allow me to add data later using the field duplicated from the first
table as the key. I am trying things this way to hopefully make it easier
when adding the later data.

If you're creating an empty "placeholder" record to be filled in later...
DON'T! That's even worse than storing numbers redundantly!

Instead, use a Form based on the table you're updating now, with a Subform
based on the second table; use this field as the master/child link field. Then
when you *do* have data for the second table, it will automatically fill in
the ID.

John W. Vinson [MVP]

 

[Guest]

Hi John,
I understand what you are saying. Have already set this type of system up in
other areas so will look at the best way for me to do it in this instance.

In the meantime the following is the VB code that I wrote to try to make the
original idea work but it gets an error when I am trying to set the illnesses
table tag number field to that of the value in the form. Could you take a
look anyway and let me know why this is happening. I thought the forms etc
reference would do what I wanted. Hope this is understandable:

Private Sub SaveRecord_BeforeUpdate(Cancel As Integer)
Dim Cnxn As ADODB.Connection
Dim rstIllnesses As ADODB.Recordset
Dim strCnxn As String
Dim strSQL As String

Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Testing';Integrated Security='SSPI';"
Cnxn.Open strCnxn ('Testing' is the name of the Database as well as
the main table could this be causing a conflict?)
Set rstIllnesses = New ADODB.Recordset
strSQL = "Illnesses"
rstIllnesses.Open strSQL, strCnxn, adOpenKeyset, adLockOptimistic,
adCmdTable
rstIllnesses.AddNew
rstIllnesses.Tag Number = [Forms]![testing]![SaveRecord] (This is where
the error occurs)
rstIllnesses.Update
blnRecordAdded = True
rstIllnesses.Close
Cnxn.Close
End Sub

Sorry to be a pain.

Regards
Mabeline.

 

[Guest]

Thanks very much John for the suggestion. I have used the sub form and it is
working really well.

Thanks again.

Mabeline