Move a record to another table

[kishanpatel50]

ok ive read some responses to people having trouble or need to know how to move a record from one table to another...
i have two tables. One with member and another with thier siblings.
i have two forms. one with the member details which contain a command box to open the other form for their siblings. the siblings form is a tabular form so when i double clikc the siblings form i can all the children but when i open it from a specific member only their children are shown.
each member has a memberID (primary key). but the siblings have the same ID as their father(member)

i have put a commmand box in the siblings form next to every sibling. what i want is that when i click this button the record moves to the members column.

i only know the very basics of VB...so a step by step explination would be helpful...

this is very specific and if more information is required please ask here or ask for my msn address.

Thank you in advance

 

[neileg]

A few things:

Siblings are brothers and sisters. You mean children or offspring.

You should only have one table for all people, members and children. A field will indicate whether they are members or not and to chnage a child record to a member record, all you need to do is change that field.

Every record should have its own unique primary key. A child record should hold the primary key of the father record as a foreign key.

 

[kishanpatel50]

thanks for your reply but
there was a reason why i cant have them all in one table because the member table contains other things which i dont need to mention right now.

i dont want to merge the two tables...

 

[kishanpatel50]

anyway i think ive found a way but am having some trouble with the coding..

Private Sub Command16_Click()
INSERT INTO MembersAndDaughters (MemberName, MemberFatherName, MemberGrandFatherName, MemberSurname)
VALUES SiblingName, SiblingFatherName, SiblingGrandFatherName, SiblingSurname
End Sub

i get an error saying Expected: end of statement
and it highlights MembersAndDaughters

im really noobie at this so what am i missing?

wat i want is when i press the botton in the siblings form...it copies the record next to the box i clicked to the MembersAndDaughters Table..the two tables dont have the same field names so am having some trouble.

 

[neileg]

thanks for your reply but
there was a reason why i cant have them all in one table because the member table contains other things which i dont need to mention right now.

i dont want to merge the two tables...

But that is the right way to do it.