You are here:

Using MS Access/Referencing a field between tabs on a tab control

Advertisement


Question
Julie:
You had offered some help to me in the past, but I was pulled off the project for many months before I was allowed back on it, so I never got to thank you for that. However, I have another question now.

I have created a form with a tab control on it. Each tab then has at least one sub-form. I have multiple tabs that I need to pull summary data off and show on the main tab. Basically, each tab is a separate task and each contains a calculated field to summarize the number of days taken. I want those values to appear on the first tab, which contains an overall summary of the "project".

My issue is that I can't figure out how to reference the calculated field on each page. Every item on each tab is uniquely named. Here's an outline (names changed to protect the innocent!)

Main form: Project
Tab Control: Tasks
Tab names:
Main tab = tabSummary
Other tabs = tabTask_A, tabTask_B, etc.
Relevant sub-forms: subFrmTask_A, subFrmTask_B, etc.
Calculated controls: These are on the sub-forms. Let's call these Task_A_Days and Task_B_Days

I need to reference Task_A_Days and Task_B_Days and include the value from each one on tabSummary.

I've tried Me![Task_A_Days] as well as Forms![Project]![Task_A_Days] and a few derivatives of these, but keep getting a generic error (usually #Name). These are based on internet searches that I've tried which indicate I do not need to reference the tab control if each item is uniquely named.

I'm thinking I somehow need to reference the parent form before going to the other tab, but I'm not sure of the format.

Thanks for any assistance.

MC

Answer
Hi Michael,

My apologies for the delay.

I would use the following:

=Forms![Project]![subFrmTask_A].Form![Task_A_Days]

See if that works for you.

As a hint, to find the path (syntax).  Open a new query in design view, close the table window, then right click in the cell next to 'Criteria' and choose the 'Build' option.

The expression builder window will open.  Drill down on the first window until you find the form (or subform) that you want. Easier if you already have the form open. Select that form, then double click on the relevant field in the second column, this will put the path in the top window.  Copy and paste and use this where you need it.

I hope this helps. I use this hack often.  :0)

Regards

Julie

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Julie Misson

Expertise

My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.

Experience

I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the www.simply-access.com website.

Education/Credentials
Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2016 About.com. All rights reserved.