?
Solved

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

Posted on 2011-05-10
28
Medium Priority
?
542 Views
Last Modified: 2012-05-11
On this form “frmInspectionDataEntry” it would be nice to have a field called “TypeOfDevice” from the table “tblTools” that shows in each of the tabs so you know the tool you are looking at.

There are several different dimensional tools as there are several different types of other tools in each of the other tabs.

Table “tblTools” is a parent of “tblInspections” correct? So shouldn’t you be able to put this field “TypeOfDevice” on each of the different tabs??

I have tried to do this but it is not working. Should I be using a query?
 

Instruments2Rev1.mdb
0
Comment
Question by:James Coats
  • 13
  • 10
  • 5
28 Comments
 
LVL 5

Expert Comment

by:DoveTails
ID: 35730391
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.
0
 

Author Comment

by:James Coats
ID: 35730697
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
0
 
LVL 5

Expert Comment

by:DoveTails
ID: 35731078
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 26

Expert Comment

by:Nick67
ID: 35731307
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
0
 

Author Comment

by:James Coats
ID: 35731350
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...
0
 

Author Comment

by:James Coats
ID: 35731412
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...
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35731430
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
demo of how to open it
0
 

Author Comment

by:James Coats
ID: 35731554
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.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 35731646
Did I post the wrong rev?

try, try again
Instruments2Rev1.mdb
Tab-with-constant-controls.jpg
0
 

Author Comment

by:James Coats
ID: 35731677
I getting sent on a fetch mission. I'll be gone for about 1.5 hrs
0
 

Author Closing Comment

by:James Coats
ID: 35732520
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??
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35732712
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
0
 

Author Comment

by:James Coats
ID: 35732996
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35733037
<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
0
 

Author Comment

by:James Coats
ID: 35733219
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?
0
 
LVL 5

Expert Comment

by:DoveTails
ID: 35733228
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.
0
 

Author Comment

by:James Coats
ID: 35733475
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.
0
 
LVL 5

Expert Comment

by:DoveTails
ID: 35733634
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35733639
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()


0
 
LVL 26

Expert Comment

by:Nick67
ID: 35733680
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?
0
 

Author Comment

by:James Coats
ID: 35733813
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35733858
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35733892
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
0
 

Author Comment

by:James Coats
ID: 35733942
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35734061
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
0
 
LVL 5

Expert Comment

by:DoveTails
ID: 35734117
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.
0
 

Author Comment

by:James Coats
ID: 35737558
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.

0
 

Author Comment

by:James Coats
ID: 35749622
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
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question