How to show data from a diff table

[sg2808]

I have 3 tables, [TblA], [TblB] and [TblC]. How do I show a field which is in [TblA]
in [TblC]. I do not want to save this field in [TblC].

The tables are connected as one to many.

So if a user has selected a record in the Form for [TblC],
I want to show this corresponding field from [TblA].

How can I do this?

Please help.

Thanks.

[nico5038]

Check the code for the DLOOKUP() function to add field content from another table.

[Rabbit]

You could also try a join in the record source. But that may make the query unupdatable.

[harini0590]

by using select statement..
suppose they want details from mark when they select regno number means.. in both table we have to maintain regno...

SELECT stu.regno,stu.n ame,m.mark,m.ma rk2 from student as stu,mark as m where stu.regno=m.reg no

[sg2808]

Could someone write and show the DLOOKUP function using the example above please?? I tried but it is not working.

The data is in table[TblA], The field I want to pull out is - [Control_des]. This also has a field called [Control_ref_num ber].

I want to show the data in a Form for table[TblC]. The PK field of the table is - [2LOD_ID]. This table has also has a field called [Control_ref_no]. The data in this field is same as [Control_ref_num ber].

Let me know if you need more information.

[Mariostg]

Did you check the Help in MS Access, the DLookup function is very well explained there. Additional information needed from you would be exactly what it is you tried and why it did not work (error message, nothing returned...)

[nico5038]

Place a new field on the form (so-called "unbound" field) and enter something like:

for a numeric field use:

=DLOOKUP("fieldTblA","TblA","FieldA_ID=" & Me.FieldC_ID)

for a text field use:

=DLOOKUP("fieldTblA","TblA","FieldA_ID='" & Me.FieldC_ID & "'")

Getting the idea ?

Nic;o)

[sg2808]

I tried your code as:

=DLookUp("Control_description","Control","Control_ref_number=  ' " & [Me].[Control_ref_no] & " ' ")

I tried this by creating a text box and wrote the code starting with a "=".
Howwver, it gave an error as #Name?

Not sure why?

Again, [Control_descrip tion] is the field name in table[Control] which I want to show in the form for table[2LOD_Assurance].

The fields which hold the same value in the above two tables are:
a. [Control_ref_num ber] in table[Control]
b. [Control_ref_no] in table[2LOD_Assurance]

Could you please advise again?

Many thanks,

[nico5038]

When the Control_ref_num ber is defined as numeric, use:

=DLookUp("Control_description","Control","Control_ref_number=" & [Me].[Control_ref_no])

Else check the field/table names used.

[sg2808]

Thanks Nico, but I am not sure why this is not working? I have checked the Table and Field names which are fine.
Is there any prerequisite or conditions required for this to work?

[Rabbit]

Outside of VBA, you can't use ME. Remove that and the code should work fine.

[sg2808]

I removed the "[Me]." from the code but I then got an error, "#Error" in the text box.

[sg2808]

Just to add, the field is a text field and is not a numeric field? Will this change the code? If yes, what will it be?

Thanks,

[sg2808]

It is now finally working, the code used is:

=DLookUp("Control_description","Control","Control_ref_number ='" & [Control_ref_no] & "'")

[nico5038]

Guess it would have worked too with:

=DLookUp("Control_description","Control","Control_ref_number=  ' " & [Me]![Control_ref_no] & " ' ")

The dot (.) is instructing Access that it's a property and no field reference...
It's often in the details :-)