linking two tables??

[SyBeerianTyGRRRR]

Hi folks,

Can anyone tell me if and how it is possible to enter some of the the same data from a form into two separate tables.
For example:

I have a Form called

frmPlmkrsDbse

that has a table called

tblPlmkrsDbse

as its record source. This table contains fields such as

ContactID (which is the primary key),
FirstName,
LastName,
StreetAddress,
Town,
City,
StateOrProvince

etc etc.

When i enter this data into the form/table i would like the same data to be simultaneously entered into another table called

tblContacts

(which is where i store all my client contact details).

This second table has some field names that correspond exactly with the first table and would need to store exactly the same data.

I cannot seem to achieve this using the Relationships Window method. The problem seems to be in the primary keys of the two tables not being the same???

I cannot make this happen as the two tables will never contain exactly the same number of records.

Can anyone help, it would be much appreciated.

Thanks v. v. much

 

[nrugado]

Are you locked into having two tables?

If not just have one table with an additional column yes/no indicating if this record in tblPlmkrsDbse is also a "contact".

Hope this helps.

 

[SyBeerianTyGRRRR]

Thanks nrugado for your quick reply.

Unfortunately the answer is yes i am locked into having two tables. The reasons for this relate to other elements and functions of the database i am working on. You are correct that what you suggest would ordinarily be the simple answer but to go down this road would mean altering numerous other components/code etc (and i am basically looking for a short cut that won't necessitate this!!) Lazy i know but there it is!!

Can you suggest any other methods based on what i am trying to achieve?? Or can anyone else help.

All the same very many thanks for the post buddy.

 

[nrugado]

Yes, you can use an update query to take data from one table and update it to another table. You would need a common identifier between the two tables. If you don't can you add one?

 

[SyBeerianTyGRRRR]

Thanks once again nrugado,

Can you elaborate on this as queries are still an area that i am learning to master. Hope this isn't too much trouble, will understand if it is too long winded to relate. Just let me know if thats the case and i will try to crack it myself.

Cheers mate

All the best.

 

[nrugado]

Do you have an e mail that I could send an example to?

 

[willir]

SyBeerianTyGRRRR

You can do this with code in the form using Afteyr Insert event.


This is an example using DAO...


dim dbs as DAO.database, rst as DAO.recordset

set dbs = currentdb()

set rst = dbs.openrecordset("tblContacts&quot

with rst
.addnew
' use field names on contact table, I am guessing here
!FirstName = me.FirstName
!LastName = me.LastName
' etc....
.update
end rst

rst.close


Now here is a question for you...
What are you going to do if John Doe moves? Are you going to update the address in both tables?

Richard


end with

 

[SyBeerianTyGRRRR]

Thanks very much to nrugado and willir,

Sorry i didnt reply earlier but i've been away from pc for today. Will try your code out willir i assume this needs to be attached to the relevant text boxes in the form on their After Insert Event Procedure??? nrugado my e-mail is

stunic.b@blueyonder.co.uk

just in case you want to send me an example of your method, i would be most grateful if you would as i may still use it or perhaps elsewhere on my db.

Thanks again to all who have responded,

Cheers

 

[SyBeerianTyGRRRR]

Hey guys i wonder if u r still watching this thread. If so i have a problem with willir's code method. I have attached the code below to the Forms After Insert Event procedure:

""""Start of Code""""

Private Sub Form_AfterInsert()
Dim dbs As DAO.database, rst As DAO.Recordset

Set dbs = CurrentDb()

Set rst = dbs.openrecordset("tblContacts&quot

With rst
.addnew
!Salutation = Me.Salutation
!FirstName = Me.FirstName
!LastName = Me.LastName
!CompanyName = Me.CompanyName
!StreetAddress = Me.StreetAddress
!Village = Me.Village
!Town = Me.Town
!City = Me.City
!County = Me.County
!PostalCode = Me.PostalCode
.update
end rst

rst.Close
End Sub

""""End of Code""""

The problem is that after i have entered the data into frm Plmkrs when i go to save the record it returns a syntax error on the line:

end rst

I'm afraid i do not know enough VBA to be able to correct this. Can willir or anyone point me in the right direction and also let me know if they can see any other major errors or problems with this code achieving what i want???

P.S. I have already checked the box in VBA Tools / References to Microsoft DAO 3.6 Object Library as this was initially returning the "user-defined type error message.

Many thanks in advance, hope someone is still viewing this thread!!!

Cheers

 

[daklem]

SyBeerianTyGRRRR,

The last line of the With ... End With statement is wrong. It should be End With not End rst

Good luck

 

[willir]

Shoot me!! ;-(