Creating New Records in one Table from another table

[Bec]

Ok, here's my problem...... I have a contacts table which includes, ID, FirstName, LastName, Category, Region, Date of Birth, etc. Then i have a Personal Details Table which lists their home addresses and a business table that lists their business addresses.

The problem is.... some of our contacts have their home addresses as preferred mailing addresses, others have their business addresses. I have a yes/no field in the table that says "MailingAddress?". When this checkbox is ticked i want a new record to be created in a table "tbl_MailingAddress"

Any ideas would be much appreciated, and please be gentle....it's been a while since i used Access and VBA.

Thanks
Bec :-)

[christinamasalha]

Ok, here's my problem...... I have a contacts table which includes, ID, FirstName, LastName, Category, Region, Date of Birth, etc. Then i have a Personal Details Table which lists their home addresses and a business table that lists their business addresses.

The problem is.... some of our contacts have their home addresses as preferred mailing addresses, others have their business addresses. I have a yes/no field in the table that says "MailingAddress?". When this checkbox is ticked i want a new record to be created in a table "tbl_MailingAddress"

Any ideas would be much appreciated, and please be gentle....it's been a while since i used Access and VBA.

Thanks
Bec :-)

why dont you use the add new method for the recordset object.
for example:

Dim myConnection As ADODB.Connection
Dim tbl_MailingAddress_Table  As New ADODB.Recordset
 
Set myConnection = CurrentProject.Connection
tbl_MailingAddress_Table.ActiveConnection = myConnection
 
tbl_MailingAddress_Table.CursorLocation = adUseClient
tbl_MailingAddress_Table.CursorType = adOpenDynamic
tbl_MailingAddress_Table.LockType = adLockOptimistic
tbl_MailingAddress_Table.Open "tbl_MailingAddress", , , , adCmdTable
 
tbl_MailingAddress_Table.AddNew
tbl_MailingAddress_Table.Fields(0) = value of field one
tbl_MailingAddress_Table.Fields(1) = value of field two
tbl_MailingAddress_Table.Fields(2) = value of field three
 
...........and so on..............
tbl_MailingAddress_Table.Update
 
tbl_MailingAddress_Table.close
Set tbl_MailingAddress_Table = Nothing
Set myConnection = Nothing

The code is general at the moment, make sure to study it and fix it to fit your needs,
with something like that code you can take anyvalue you want add it to the recordset (Table you want) and then its saved when the update method is used.
This is how i do it atleast :)


Hope this helps you,
Christina