MS Access: Adding a field from parent table to child table in a subform

Could you move the tab portion of your form down to allow room for your field "TypeOfDevice".

Then when you choose Add Existing Fields, select "Fields available in related tables" and choose TypeOfDevice.under tblTools  ....  but place it in the opening you created by moving the tabs down....not on an individual tab.

Not sure this works with your data but I think it may be what you're after.

DoveTails,

I have inserted a subform in the Dimensions tab. I was able to use the table "tblTools" I can see the info I want.

However I like your idea better. I have inserted a field but I can't see the other related tables. When I right click on the new field I can only see the "tblInspections" table. See new db. See form "frmInspectionDataEntry2"

Where do I click or how do I see related tables in the properties tab?
Instruments2Rev1.mdb

With frmInspectionDataEntry in design view:

     Design Tab > Add Existing Fields

You should see at the bottom of the Field List the option to "Show all tables"  (instead of only the fields in the current record source)

I'm hoping from there you will see the related tables and the field TypeOfDevice from there.

Good luck.

Two things.
There is a knack for putting a control on a tabcontrol so that it stays visible on all tabs.
http://office.microsoft.com/en-us/access-help/using-the-tab-control-on-a-form-HA001230049.aspx#BM9
Next, you need a DLookup for your type of device

Altered sample attached.

As an aside.
If you go back to your answered questions, there is a button to 'ask a related question'
EE the alerts everyone who worked on your previous question :)
Unless you're hiding form me :)
Instruments2Rev1.mdb

DoveTails,

Man! I have looked all over for the "Design Tab" while in the forms design view. I can't find it. I've looked on the tool bar and every place I can think of but don't see it. I have Access 2003 so I should have it correct?

I looked in the properties tabs and don't see it there. I've right clicked on the little black square in the upper left hand corner and right clicked in the form to see those properties.

I am stumped...

Nick,

No I am not hiding... the embarssment is fading. I did not know to ask a related question which I will now start doing thanks for that info.

I am looking at your example now...

Naw,

Tabs in that sense are from the Ribbon in 2007/2010.
2003 has no stinkin' tabs, mister

Open the field list for the equivalent

Nick,

Just looked @ your example but I don't see the items on the right side of the tab form. I can see it in your picture of the field list button but when I open the db I don't see that info displayed. If I go to design view I don't see anything either.


I am reading the link on this that you sent.

I getting sent on a fetch mission. I'll be gone for about 1.5 hrs

Nick,

Thanks for the example. From what I can see that you did it looks like just a text box and then the SQL put into the control source data tab. Is it that simple??

It's not SQL in the control source in this case
It's the DLookup function

Basically, it's a built in function that does the equivalent of pulling a single value from a single field in a single table based on whatever WHERE criteria you can craft.

It has real peformance downsides when used in situations where it gets performed lots.
(ie loops or controls in the detail sections of reports) but this is exactly the use it was designed for

I just tried to do this but my example doesn't work. I put two text boxes in the detail section out side of the tab boxes. I pasted in the DLookup function into the Control Source of the data tab. Is this not the correct way?? And then draged them into the tab boxes.

It doesn't work the way I've done it. Please explain what I doing wrong

<It doesn't work>
Clarify
The Control source needs to be EXACTLY

=DLookUp("ToolName","tblTools","Tool_PK = " & Forms!frmInspectionDataEntry!Tool_PK)

for the one

=DLookUp("TypeOfDevice","tblTools","Tool_PK = " & Forms!frmInspectionDataEntry!Tool_PK)

for the other.

The equals sign is needful

I am copying and pasting your code into a text box I place into the detail section next to the tabbed forms. I right clicked on the box went to properties then to the data tab control source and pasted in the code. Is this how you're supposed to do it?

Where did you get the code if it is not SQL?

Samuel,
Is there a reason you do not wish to place the control on the form but outside of the tab control ?

I'm curious if that is not providing the behavior you are after or if you prefer the look of having it on each tab.

I want to be able to identify the tool that relates to each record. There are several different types of say calipers some are digital and some are manual. Then there are micrometers that are dimensional in nature. And yes I wanted to see it on each tab as it related to that tab so I would know what tool was being looked at.

It appear now that this will not be a feature that will be used but I wanted to know how to do it.

Samuel,
Makes sense to me.  I like the way Nick setup the DLookUp.  Your method of working with the text box sounds correct to me, I hope you got that working.

Here's a site I use for functions you may find helpful:
http://www.techonthenet.com/access/functions/domain/dlookup.php

Cheers

Fire up the VBA editor, and fire up the Help
Search for DLookup.

There are others like it, DCount, DSum, DMax, DFirst.
They are functions.

Just as you can set the Control source of a textbox to ="My Name Is Sam" and it will display My Name is Sam, you can set the control source = to a function

This works too

Private Function FindTheToolName() as string
dim rs as recordset
set rs = currentdb.openrecordset("select ToolName from tblTools where Tool_PK = " & me.tool_PK, dbopendynaset)
FindTheToolName = rs!Toolname
End Function

If this function exists then Text34's controlsource can be

=FindTheToolName()

The DLookup function takes arguments
The first is the field you want to lookup
The second is the table you are looking in
The third is a valid WHERE statement without the WHERE.

I could see from your failed sample what you were trying to get.
The third argument is optional.
If it is left off then the function returns the first value it finds.

I know that you wanted it correlated to the form, so building it was no stretch.

Now, are you having grief getting the box to return the correct value instead of #NAME
or
Difficulty placing it on the form the way you'd like?

It is still returning #NAME, but I am going to have to let it go. I will come back to it later. I have to keep this project moving. Thanks for the info about the VBA editor and other functions. The info about record sets reminds me of connecting Dreamweaver forms to tables and such so now I have a feel for what is going on.

DoveTails,

Thanks sooo much for the link to the functions that really is helpful.

Where are you guys at time wise? I am on an island in Puget Sound, Washington state the island is called Whidbey Island.

Mountain Daylight
One hour ahead
If you could drive directly north from Spokane for 10 hours or so, you'd come past my shop.
Go North from Spokane on 395 to the border
Actually drive 20 hours on the 3, 93,95,16 and 40 until you come to Grande Prairie Alberta.
Same idea

When the DLookup returns #NAME it usually means a typo or a problem with the WHERE part.
You start troubleshooting by taking the third argument out

=DLookUp("ToolName","tblTools")

If that returns #NAME, you either don't know your fieldnames or your tablenames :)

Then work on the thrid argument

Nick sounds like a nice place to live but likely a little cold for me. I have been to British Columbia several times and really enjoyed myself you guys have a very nice country.

I will come back to this issue and check that I am sure it's something I miss typed or did not copy all the way.

Thanks again for your help.

No Problem.

It's a dry cold :)
The good news is that any given week of the year it can be above 32º F
The bad news is that in almost any given week of the year it can be below 32º F

Still, fireworks for Canada on July 1st take place at 12:30 AM that day.
My wife, who comes from the east coast at 45º latitude said
"How dumb is that"
I said, "Well dear, it's not really dark yet, but it's as close as it'll get"

July 1st you can play softball til midnight without lights.
The cold is a minor annoyance to be able to have 18 hr long summer days with 75º weather

On Pacific Time, but living in the mountains of Northern Idaho.   Friends just across the time line give us a hard time ...  "You live in the mountains....the ocean is a state away....you should be Mountain time."  
So I try to visit the coast once a year.

Nick,
Those temps don't sound that bad. I like our 12 hour days here in the summer too. We general have temp winter 35 to 55 and summer 50 to 75.

DoveTails,
When I worked in the Postal Service I tried to transfer to a small post office in the Shoshone Falls area but I had to settle for Whidbey Is in Washington state. This state is ok but I really wanted to go to Idaho.

Well I'm getting back in my into my rat cage and going to work.

Just uploading a copy for me to use @ home this weekend. This is the current version and seems to be just what everyone wants. I still have to add in the lower left of each form a subform, query, excel sheet or something to show the Min, Max, Range, Accuracy, St Dev & average of all of the measurements readings from each of the various the inspection checks as it related to each type of instrument.

I am hoping to be asking a question on what everyone would recomend on how best to do this tomorrow. I have other tasks to take care of today.  Till tomorrow.
Instruments5-11-11.mdb