Access 2010 Navigation form

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
I have a main Navigation form and within it, on one of the tabs, I have a subform that has a detail view on it, then a subform on that one with a datasheet view for line items. I’m trying to get a field on the datasheet to toggle visibility depending on an option button selection on SubForm 1. I am using Master/Child links between the two forms.

Here’s what I’ve been trying with no success (the controls that aren’t commented are on subform1 and work fine):

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> fra_SD_Dest_Click()<br><SPAN style="color:#007F00">'   Single Location selected</SPAN><br>    <SPAN style="color:#007F00">'   Display Destination Type</SPAN><br>    <SPAN style="color:#007F00">'   Enable ShipTo Location on Detail form</SPAN><br>    <SPAN style="color:#007F00">'   Disable ShipTo location on LineItem form</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Me<br>    <SPAN style="color:#007F00">'   Ship to a Single Location</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> .fra_SD_Dest.Value = 1 <SPAN style="color:#00007F">Then</SPAN><br>            .cbo_SD_Dest.Visible = <SPAN style="color:#00007F">True</SPAN><br>            .cbo_SD_Dest.Value = 3<br>            .cbo_SD_ShipTo.Enabled = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#007F00">'[Forms]![frm_Dashboard]![NavigationSubform].[Form]![cbo_SLI_ShipTo].Enabled = False</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>    <SPAN style="color:#007F00">'   Multiple Location selected</SPAN><br>        <SPAN style="color:#007F00">'   Hide Destination Type</SPAN><br>        <SPAN style="color:#007F00">'   Disable ShipTo Location on Detail form</SPAN><br>        <SPAN style="color:#007F00">'   Enable ShipTo location on LineItem form</SPAN><br>            .cbo_SD_Dest.Visible = <SPAN style="color:#00007F">False</SPAN><br>            .cbo_SD_ShipTo.Enabled = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#007F00">'[Forms]![frm_Dashboard]![NavigationSubform].[Form]!Me.[cbo_SLI_ShipTo].Enabled = False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Here are the form names:

Navigation form: “frm_Dashboard”
Subform1: “frm_SalesDetails”
Subform2 on “frm_SalesDetails”: “frm_SalesLineItems”

The field I’m trying to toggle is: “cbo_SLI_ShipTo”

Any ideas on the proper syntax for that? If it’s just a plain form/subform relationship I can get it to work, but this whole Navigation form referencing throws me off completely.

Thanks!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Smitty, the Me keyword in this call is incorrect because you are providing the full path.

[Forms]![frm_Dashboard]![NavigationSubform].[Form]!Me.[cbo_SLI_ShipTo].Enabled = False

Instead, use

[Forms]![frm_Dashboard]![NavigationSubform].[Form]![cbo_SLI_ShipTo].Enabled = False

Denis
 
Upvote 0
Hi Smitty,

> "Navigation form referencing"

the thing to remember with Navigation forms is that only ONE tab is active is active at any given time -- you reference the (only) subform (for instance) that is showing. The syntax is different than for a regular form. I recommend using TempVars to store values that might come from Navigation forms (often from combos) since TempVars can be referenced in code, queries, and on controls. TempVars retain their value (supposedly; I have found exceptions) as long as the database is open
__________
TempVars

using VBA to add or change a tempvar:
Code:
   TempVars.Add "tvVariableName", valueOrVariablename

if the tempvar already has a value, it will be changed

you can retrieve the value like this:

Code:
  TempVars!tvVariableName
-- as ControlSource... =[TempVars]![tvVariableName]
-- in query... [TempVars]![tvVariableName]
-- in code, you can also use TempVars("tvVariableName")

using TempVars will reduce the headaches -- then it will matter not if the control is on a mainform, subform; on a navigation form or not ;)

instead of choosing specific names, if use will be temporary, I recommend names like tvBoo (boolean), tvLong (long) ... if you truly do want to store this tempvar specifically, like tvCustID, that is ok too -- but ask yourself first before you create a unique name, "will I use this after what I am about to do next?"

If I want a variable for longer than briefly, I use database properties, which don't get lost even when the database closes. They, however, need a wrapper function to use in queries. As I construct dynamic SQL in code, this never affects me. I wrote a wrapper but rarely call it ;)
 
Last edited:
Upvote 0
example wrapper function:

Code:
Public function GetMyTempVar(psTempVarName as string) as variant
'Crystal (strive4peace)
   GetMyTempVar = TempVars(psTempVarName)
end function
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top