Referencing subforms of Access 2010 Navigation Control

solved4u

New member
Local time
Today, 11:44
Joined
Oct 17, 2012
Messages
6
I'm trying to refer, using vba, from my one form to a subform many levels below:

My current structure is:

Navigation Control Form > NavigationSubform > Custom Form1 > Navigation Control Form > NavigationSubform > Custom Form2

From a button on 'Custom Form 1' I'm trying to reference 'Custom Form 2' to perform a requery (ie. ___ [CustomForm2].Requery) on 'Custom Form2'.

I have tried for days to resolve this and this is my last resort does anyone have any suggestions for the syntax.

Thanks in Advance
 
No this doesn't help, I found this when surfing for a solution but it didn't work.

Thanks for trying to help, do you have any other suggestions?
 
So CustomForm1 and CustomForm2 are in the main Navigation Form?
 
I should probably write it as : Navigation Control Form1 > NavigationSubform > Custom Form1 > Navigation Control Form2 > NavigationSubform > Custom Form2

I have a Navigation Control form that has the normal Navigation Subform with a form (Custom Form1) within it.
On this form I have a seperate navigation form within it with the normal Navigation Subform that has a form (Custom Form2) within it.

Hope this clarifies, thanks again for your help.
 
Boblarson, I tried this the method shown in your link.

When I try to get to 'Custom Form2' using 'Build' I cannot actually see it in loaded forms. All I can see is the name of the NavigationSubform above it -(frmSubNavigationForm).

Also the icon for the NavigationSubform(frmSubNavigationForm) is a report (green ringbinder) icon in 'Loaded Forms' which doesn't show anything in Expression Categories.

When I look at both the NavigationSubform(frmSubNavigationForm) All Forms it shows the normal form icon.

Thanks in advance for your help.
 
The subform control name is what you use and not the subform name unless they are both named the same. Male sure you open the top level form before doing this and start at the top most form under loaded. But if you can upload your database (with fake data) I can, or someone else can, try.
 
If I set the the following:

Dim FormToRequery As Form
Set FormToRequery = Forms![frmMainNavigationForm]![NavigationSubform]![frmSubNavigationForm].Form

I get FormToRequery equals frmSubNavigationForm

If I replace the second line with Forms![frmMainNavigationForm]![NavigationSubform]![frmSubNavigationForm]![frmSageQuotes].Form

or replace with Forms![frmMainNavigationForm]![NavigationSubform]![NavigationSubform]![frmSageQuotes].Form

I get an error that it cannot find frmSageQuotes. Access 2010 has a limit of 7 for subforms has my configuration exceeded this limit somehow.

Is there anything I can do to a subform that would cause it to requery the subforms it contains,as a work around?

Unfortunately the database is due to go live with lots of data and linked tables, which would take me ages to clear out (I first noticed this problem when testing the database).

Your your help is greatfully apreciated.
 
Forms 7 deep :eek:
If i need to go more than the 3 deep then i forget about using a sub form.
I dynamically link two subs-forms. So depending on what the record the user clicks on in sub form A - depends on what is viewed in sub form B. Controlled by their common main form. If you get my drift.

form A
Code:
Private Sub Form_Current()
    Call Update_link
End Sub

Private Sub Update_link()
    Me.Parent.txtLink = Me.ID
End Sub

Then form B
Code:
Link Master fields: txtlink
Link child fields: ID

In the main form you need a txtbox called: txtLink (can be hidden):)

The field ID is common to both tables :o
Hope this helps
 
which would take me ages to clear out (I first noticed this problem when testing the database).
If the database is split, which it should be, then just upload a copy of the frontend and we can do our best to assist. If it is not split you had better do so. But if it isn't split and you want to clear a copy of it out quickly and easily, just download and use my free database reset tool:
http://downloads.btabdevelopment.com/Tools/DatabaseReset.zip

You can choose to not reset any tables which have lookup information but the default is all of them.
 
Is there any way to send this database to you directly, just in case I have missed the removal of the odd logo?

Also the file is 2.55Mb will this be ok?
 
I'll PM you an email address. And make sure to run compact and repair first and then zip the file.
 
I'm having the same trouble myself. Any advice much appreciated. Here is what my VBA reference would be, if I just went down through the 'hierachy' using full-stops to get to the form with the value/control I want...
With frmNavigationLeft.frmNavigationTop.frmClients.sfrmReferrals.Variable/ControlName

i.e I have a Main Form ( frmClients ) holding a Subform ( sfrmReferrals ), these are both on a Navigation Form ( frmNavigationTop ), which itself is a 'sub' form of another Navigation Form ( frmNavigationLeft ). Please help if you can.
 
I'm having the same trouble myself. Any advice much appreciated. Here is what my VBA reference would be, if I just went down through the 'hierachy' using full-stops to get to the form with the value/control I want...
With frmNavigationLeft.frmNavigationTop.frmClients.sfrmReferrals.Variable/ControlName

i.e I have a Main Form ( frmClients ) holding a Subform ( sfrmReferrals ), these are both on a Navigation Form ( frmNavigationTop ), which itself is a 'sub' form of another Navigation Form ( frmNavigationLeft ). Please help if you can.

Did you use the method I said to use in post #5 to get that path? I ask because it doesn't look like it based on what you have there. It doesn't use the names you would think in a normal subform situation and I haven't done it enough to have memorized the pattern. But each time I've used this method:
http://www.btabdevelopment.com/ts/refer2sfrms
I have been able to get the correct path (but you do have to have the top level form open and then you select a control or something just to work your way down and then double click on the last portion to add it to, say the control source, just to get the path. You can then delete that control or delete the control source after you have copied it).
 
I have the same problem, but in Access 2007 VBA. I have used the Builder in the Query definition, and copied its string into the VBA code. My observation over time is that there are subtle differences between the syntax in a Query and the syntax in VBA.

I need a reference to the RecordsetClone for Form.SubForm1.SubForm2.SubForm3

Code:
Dim MyRS3 As Recordset
Set MyRS3 = Forms![frmABTabOperations]![frmOperationPO].Form![frmOperationPOF].Form![frmOperationPOS].Form.RecordsetClone

This results in:

"You entered an expression that has an invalid reference to the property Form/Report."

I have tried many variations on the theme, but cannot get it corrrect.

Ideally, I would like to express it along these lines:

Code:
Set MyRS3 = Me.Parent![frmOperationPO]![frmOperationPOF]![frmOperationPOS].Form.RecordsetClone

The reason is that the line of code is attached to subform on a tab at the same level as the frmOperationPO subform.

It is a pity that there is no equivalent of the Query Builder within the VBA environment. I guess that is because there is a disconnect between VBA and the other database objects, although many controls etc are visible in drop down lists. For some reason, obtaining dynamic reference support for subform constructs seems but a dream.

Any help would be much appreciated.
 
When I remove the 2 lower level subforms from the reference, the Set statement is OK.

Code:
    Set MyRS3 = Forms![frmABTabOperations]![frmOperationPO].Form.RecordsetClone

Does that suggest that property references are limited to 1 subform?

Removing only the lowest subform still resulted in the error.
 
I hope that this helps someone. I split the reference and it is now OK.

Code:
Dim MyFormA As Form
Dim MyFormB As Form
    Set MyFormA = Me.Parent![frmOperationPO].Form
    Set MyFormB = MyFormA![frmOperationPOF].Form![frmOperationPOS].Form
    Set MyRS5 = MyFormB.RecordsetClone
 

Users who are viewing this thread

Back
Top Bottom