By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

How to show data from a diff table

P: 91
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.
Apr 16 '12 #1
Share this Question
Share on Google+
17 Replies


nico5038
Expert 2.5K+
P: 3,072
Check the code for the DLOOKUP() function to add field content from another table.
Apr 16 '12 #2

Rabbit
Expert Mod 10K+
P: 12,316
You could also try a join in the record source. But that may make the query unupdatable.
Apr 16 '12 #3

P: 19
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.name,m.mark,m.mark2 from student as stu,mark as m where stu.regno=m.regno
Apr 17 '12 #4

P: 91
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_number].

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_number].

Let me know if you need more information.
Apr 17 '12 #5

100+
P: 332
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...)
Apr 17 '12 #6

nico5038
Expert 2.5K+
P: 3,072
Place a new field on the form (so-called "unbound" field) and enter something like:

for a numeric field use:
Expand|Select|Wrap|Line Numbers
  1. =DLOOKUP("fieldTblA","TblA","FieldA_ID=" & Me.FieldC_ID)
for a text field use:
Expand|Select|Wrap|Line Numbers
  1. =DLOOKUP("fieldTblA","TblA","FieldA_ID='" & Me.FieldC_ID & "'")
Getting the idea ?

Nic;o)
Apr 19 '12 #7

P: 91
I tried your code as:

Expand|Select|Wrap|Line Numbers
  1. =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_description] 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_number] in table[Control]
b. [Control_ref_no] in table[2LOD_Assurance]

Could you please advise again?

Many thanks,
Apr 23 '12 #8

nico5038
Expert 2.5K+
P: 3,072
When the Control_ref_number is defined as numeric, use:
Expand|Select|Wrap|Line Numbers
  1. =DLookUp("Control_description","Control","Control_ref_number=" & [Me].[Control_ref_no])
Else check the field/table names used.
Apr 23 '12 #9

P: 91
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?
Apr 26 '12 #10

Rabbit
Expert Mod 10K+
P: 12,316
Outside of VBA, you can't use ME. Remove that and the code should work fine.
Apr 26 '12 #11

P: 91
I removed the "[Me]." from the code but I then got an error, "#Error" in the text box.
Apr 27 '12 #12

P: 91
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,
Apr 27 '12 #13

P: 91
It is now finally working, the code used is:

Expand|Select|Wrap|Line Numbers
  1. =DLookUp("Control_description","Control","Control_ref_number ='" & [Control_ref_no] & "'")
Apr 27 '12 #14

nico5038
Expert 2.5K+
P: 3,072
Guess it would have worked too with:
Expand|Select|Wrap|Line Numbers
  1. =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 :-)
Apr 27 '12 #15

NeoPa
Expert Mod 15k+
P: 31,186
@Nico

Do you appreciate that the resultant string from your code will have spaces around the value and between the single-quotes (')?
Also, the question doesn't make it very clear unfortunately, but from a quick perusal I assume the code being played with is to set a field within a query. If that is true then it is essentially SQL, so wouldn't recognise the word "Me". I may be misinterpreting this, but I guess if it starts with an "=" it must be somewhere outside of VBA. It's very unlike you to make such errors so I'm half expecting I've missed an important point, but I'd be interested to hear what it is if so - even if I do end up embarrassed :-D
Apr 27 '12 #16

nico5038
Expert 2.5K+
P: 3,072
Hi NeoPa,

The spaces were caused by my Dutch keyboard settings (they require an additional space to be pressed to show just a ' or ", else I get a accented character like:

AS the questioner stated: "I tried this by creating a text box and wrote the code starting with a "="."
I assumed he tried this from an unbound form field.
My assumption about the [Me] is however wrong, I did test it now and found out that Access doesn't recognize the [Me] object in such fields, as it does in the VBA.

So my previous comment should be ignored :-)

Nic;o)
Apr 28 '12 #17

NeoPa
Expert Mod 15k+
P: 31,186
Hi Nico :-)

It sucks that the keyboard settings cause that :-(, at least that's clear now though. Well done for clarifying.
Apr 28 '12 #18

Post your reply

Sign in to post your reply or Sign up for a free account.