Question: Relationship in FORM

[phatus]

i have 2 table consist the following:
Table1: Field1:Name1, Field2:Number1 and Field3:Address
Table2:Field1:Name1, Field2:Contact1 and Field3:Age

they are both in separate form
Form1:Table1
Form2:Table2

in every table i make a query on them and connect the query to my form

as you notice in both table there is Name1

what i want is if i change the name in Name1 in my Form1, automatically the name in Name1 in my Form2 will change also.. its like updating my Form2...

attached here is my sample database.

can anyone help me on this... thank you....

 

[Beetle]

The solution here is not to redundantly store the same value (in this case Name1) in two different tables. The name should only exist in one of the tables, then relate the two tables by Primary Key/Foreign Key and/or display the name in the second form by retrieving it from the first table.

 

[phatus]

i tried this in Specify Relationship

i put this
in table1 in table2
Employ Primary ID / i also test i put age

the result the form is blank all field was disappear...

can you modify sir my sample database....

thank you very much...

 

[Beetle]

If you want to upload the db to this site, I can take a look at it.

 

[phatus]

attached here is my sample sir...

i erase the name1 in my table2

thank you...

 

[Beetle]

Sorry, should have mentioned I'm working with Access 2003 so you would need to save it as a .mdb file if possible then upload it again. If you can't do that then maybe someone else who can open it as a .accdb file will pick up on this thread.

 

[phatus]

here it is sir .mdb file
thank you again

 

[Beetle]

I have attached your file. I made a few minor changes just so you can see how things generally work. I also removed the age field from the table and added a BirthDate field. You should not store a persons age as it changes every year. Instead you store the birth date and calculate the age based on that. I put a function in a general module (see the Modules section of your app) that calculates the age. The function can be called from queries, form/report controls or VBA code.

 

[phatus]

i look at the database sir... what i mean is the in the FORM2 in Employ the name of the employee is automatically enter is you change/add a name in the field of Employee in FORM1.. its like they are link...
why i want it that way...
because in my real database (Schoolprofile) i have 900 list of name i separate the personal information and the enrolment
like in our sample dbase in form2 the field employ must be linked or connected to the field employee in form1 and must not be editable...
i hope its clear sir... thank you the module age help a lot...

 

[Beetle]

You can add/join Table1 to your query for Form2 an add the name field from there, or you can use DLookup in an unbound text box on Form2 to retrieve the name.

 

[phatus]

thats what i mean sir DLookup in my unbound taxtbox in form 2.. but my problem is i dont know how to do it... i have very limited in access only the basic... can you help me...

thank you

 

[phatus]

i tried this a error appear in my textbox

=DLookup ("employ","Table1query","ID="*")

it means source table1 query, field name is employ and the criteria is IDnumber is equals to employ

i got error in my textbox unbound "error"

 

[Beetle]

You need to have EmployID in the record source for the form, then you reference that in the DLookup, like;

=DLookup("Employ", "Table1", "ID=" & [EmployID])

I have attached another copy of the db with a new form (Form3) demonstrating this.

 

[phatus]

sir its getting me an error im confused what is the [EmployID]? is it a field? or a combination of the Field Employ and Field ID?

my problem lies in my criteria...

thank you

 

[Beetle]

In your original db you had no foreign key in your child table (Table2) so you had no way to join the tables. I added a foreign key field to Table2 and named it EmployID (since it is supposed to represent the ID of the Employ field in Table1, and since the primary key field of Table2 was already named ID it had to be named something else, EmployID seemed logical). I then created a relationship between ID in Table1 and EmployID in Table2.

I also added this field to the query that is used as the Record Source of the new form I created (Form3). The Dlookup in the Control Source of the text box on Form3 refers to this field (EmployID).

You need to do something similar in your copy.

 

[phatus]

thank you very much.. ive learn a lot in this....