Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 5th, 2006, 04:13 PM
Authorized User
Join Date: Mar 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default refer to subform values

I have two forma separated by tabs both within a Main Form.
Should it make a difference in coding having the subforms located on Tabs?

MainForm Name = BigCats
Subform1Name = Territory
A Field in Subform1Name = Location1

SubForm2Name = Weight

Using the following line of code, I get the following error,

XLSheet.Range("A1") = Forms![BigCats]![Territory].Form![Location1]

    Error >>> "Method Range of Object _ Worksheet Failed."

Am I missing something obvious here?
Any Ideas on clearing up this issue would be appreciated.

Old December 6th, 2006, 08:21 AM
Authorized User
Join Date: Feb 2005
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts

Hi. I use a lot of tab strips in my applications and the first time I needed to refer to a value in a subform, in a page, in a tab strip, in a form (you know where I'm getting wuth this?) it was not evident.

The trick is to use fully qualified identifiers. For instance:

Forms!MainFormName!tabObjectName.Pages("pagName"). Controls("sfrmSubformName").Form!ctlControlName

The only object names that do not change here are Forms, Pages and Controls. I suggests you try this in the Immediate Window to see if you can read a value in a field.

Of course if your code is inside that form you can shorten it with:

Me!tabObject.Pages("pagName").Controls("sfrmSubfor mName").Form!ctlControlName

You can be clever and use something like:

Dim sfrm as Access.Form
Set sfrm = Me!tabName.Pages("pagName").Controls("SubformName" ).Form

Then use sfrm!FirstName & " " & sfrm!LastName

This exploring this avenue. Let us know if it helps

Old December 7th, 2006, 12:13 PM
Authorized User
Join Date: Mar 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks for replying.

Just befor you commented I found I was incorrectly clicking on the tab and looking at properties for the SubformName Instead of clicking on the tab, then clicking on the subform (which in design mode looks like a rectangle) then looking at that set of properties for the Subform name. So the format below works, it's just that you have to be sure you're looking at the correct set of properties for the subform name.

Forms![MainFormName]![SubformName].Form![Field] hence...
Forms![BigCats]![Territory].Form![Location1] this works as well

Me.Subformname.Form![Field] Hence...

Thanks for the lifejacket if I needed it.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Subform not displaying values after adding updates jlangdon Access 1 April 23rd, 2008 03:00 PM
Reset Subform to Default Values kindler Access 1 January 6th, 2006 06:20 PM
How to disable subform untill values selected in m method Access 3 June 29th, 2005 07:22 AM
Filter Subform B Based on Field in Subform A SerranoG Access VBA 3 June 18th, 2004 12:23 AM
how to refer another form khautinh General .NET 2 March 17th, 2004 01:29 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.