Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
A2010 Navigation Form and Whereclause tip
2,164 views
Skip to first unread message
Patrick Finucane
Nov 26, 2011, 2:09:52 PM11/26/11
to
I learned something new yesterday about the A2010 Navigation Form and
thought I'd pass it on. With forms/subforms in prior versions you
might do something like this to reference an control.
Me.LastName = Forms!MainFormName!SubFormName!Lname
When I created a NavigationForm I wanted to reference the value in one
subform to filter another subform. Similar syntax like the above does
not work.
I created 2 forms; CustomerDatasheet and CustomerDetail and dropped
them into a Naviagtion Form. I wanted to display the CustomerDetail
record of the record in CustomerDatasheet. I placed a msgbox "In
Datasheet" in the OnLoad even in Datasheet, "In Detail" for the
details form.
When I clicked the respective tabs in the NavigationForm I always got
the OnLoad event message. IOW, when a tab is pressed the form for
that tab is opened, the others are closed. Thus you can't reference a
control between subforms in a NavigationForm.
What I did to create a filter was run a macro in the OnCurrent event
of the datasheet to set a Tempvar to the current ID. Then in the
Navigation Where Clause I entered ID = Tempvars!ID. That filtered the
details form correctly.
There may be a better way to perform a filter action like this. But
this method works fine. If you haven't moved to A2010 yet but are
planning to this tip may help you out
thought I'd pass it on. With forms/subforms in prior versions you
might do something like this to reference an control.
Me.LastName = Forms!MainFormName!SubFormName!Lname
When I created a NavigationForm I wanted to reference the value in one
subform to filter another subform. Similar syntax like the above does
not work.
I created 2 forms; CustomerDatasheet and CustomerDetail and dropped
them into a Naviagtion Form. I wanted to display the CustomerDetail
record of the record in CustomerDatasheet. I placed a msgbox "In
Datasheet" in the OnLoad even in Datasheet, "In Detail" for the
details form.
When I clicked the respective tabs in the NavigationForm I always got
the OnLoad event message. IOW, when a tab is pressed the form for
that tab is opened, the others are closed. Thus you can't reference a
control between subforms in a NavigationForm.
What I did to create a filter was run a macro in the OnCurrent event
of the datasheet to set a Tempvar to the current ID. Then in the
Navigation Where Clause I entered ID = Tempvars!ID. That filtered the
details form correctly.
There may be a better way to perform a filter action like this. But
this method works fine. If you haven't moved to A2010 yet but are
planning to this tip may help you out
Neil
Nov 30, 2011, 1:20:40 AM11/30/11
to
"Patrick Finucane" <patrickf...@gmail.com> wrote in message
news:f65f2a03-48b9-471b...@o9g2000vbc.googlegroups.com...
but your syntax for the control reference is wrong. It should be:
Me.LastName = Forms!MainFormName!SubFormName.Form!Lname
You have to actually reference the form within the subform control (.Form
property), not the subform control itself.
Moving on... I haven't worked with navigation forms, so that may be so, that
it closes and opens forms when you move between tabs. So why not just use a
regular form with a tab control? That would keep the forms open for you.
Neil
Patrick Finucane
Nov 30, 2011, 10:01:38 AM11/30/11
to
On Nov 30, 12:20 am, "Neil" <neil.ginsberg+newsgr...@gmail.com> wrote:
> "Patrick Finucane" <patrickfinucan...@gmail.com> wrote in message
> Neil- Hide quoted text -
>
> - Show quoted text -
The Navigation Form is a new beast in A2010. So although your code
would work in older versions it would not work as a Subform in a
Navigation Form.
While searching for a solution I saw many responses/solutions on the
net similar to yours. It is a new feature to Access 2010 and its
concept not understood by those not using A2010. The Navigation Form
is a nice feature but the documentation is extremely limited from MS,
books on Access, and net sources unless one desires really simplistic
examples. Personally, I feel the lack of documentation is due to the
poeple at MS that design Access don't fully understand it themselves
thus they can't adequately describe it to the tech writers. The proof
of this? The lack of examples.
> "Patrick Finucane" <patrickfinucan...@gmail.com> wrote in message
>
> - Show quoted text -
The Navigation Form is a new beast in A2010. So although your code
would work in older versions it would not work as a Subform in a
Navigation Form.
While searching for a solution I saw many responses/solutions on the
net similar to yours. It is a new feature to Access 2010 and its
concept not understood by those not using A2010. The Navigation Form
is a nice feature but the documentation is extremely limited from MS,
books on Access, and net sources unless one desires really simplistic
examples. Personally, I feel the lack of documentation is due to the
poeple at MS that design Access don't fully understand it themselves
thus they can't adequately describe it to the tech writers. The proof
of this? The lack of examples.
Albert D. Kallal
Nov 30, 2011, 11:22:37 AM11/30/11
to
"Patrick Finucane" wrote in message
news:07f3d3b7-ada9-4d12...@y6g2000yqe.googlegroups.com...
>The Navigation Form is a new beast in A2010. So although your code
>would work in older versions it would not work as a Subform in a
>Navigation Form.
Actually the navigation system is really just a Subform control on a form.
The source object of that control is simply being changed.
In fact there has been examples like this posted for access for what,
probably last 15 years?
You can do quite much the same thing as the navigation system with a normal
sub form.
You are simply changing the source object property of the Subform control.
In fact how this works is actually nothing new in terms of a conceptual idea
to access developers at all.
The only real big change in access 2010 means that Subform control can now
display of form or report.
Prior to 2010 you had to use a form, but now sub-form controls can now
display both.
So this is a great tip, and you can now display reports in forms with a
sub-form control.
>The Navigation Form
is a nice feature but the documentation is extremely limited
Perhaps, but then again I think basic mathematics documentation in most
physics texts are also severely lacking.
But then again they assume you have a basic understanding of mathematics.
My point here is that is is a sub-form control and thus what you learned and
know about sub-form controls
over the years applies here.
In fact for the last at least 10+ years even when I have a form with say 5
tab controls each with a sub form, I NEVER had those sub-forms load.
I always used the tab's "change" event to set the source object of the
Subform control behind each tab. The reason why this was done then if I
have 10 tabs, then I do NOT load 10 sub-forms and waste all that delay time
to frustrate the user (and pull data from the server that I do not need
yet). So this form loads the same speed as ONE form with no sub forms. The
form loads fast since in fact none of the sub-forms load loaded or pull data
until the tab is clicked on.
I mean why load 5 or 10 sub forms when the user has not even looked at them
yet? I quite much assumed most developers do this.
So this type of on-demand of loading a Subform has been something that's
part of the landscape of access for many many years.
(and if it was not, it should have been)
Now it's possible some people don't actually understand that a Subform
control placed on a form is simply that of a control like any other form.
The idea that a Subform really exists inside of a form is a false pretext. A
subform is a control sitting on a form and there is a sourceobject property
that will create a instance of the form for this control based on the
sourceobject setting.
If a person understands sub-forms, then this basic understanding should mean
it is obvious that when changing the source object property of this control
the pervious form "instance" that the subform control was displaying no
longer exists.
I do think the syntax of browseto can be difficult when attempting to work
with sub-sub forms, but then again in such cases simply setting the source
object property is likely less work and since that is what we done in the
past for more than 10+ years then there no learning curve anyway.
So to be clear, the navigation system is based changing the source object
of a sub-form control.
From this tid bit of information then just about everything else in terms of
how this will work can be deducted from logic and previous experience one
has with sub-form controls. In other words first principles of reasoning can
be used here:
To loosely quote Spock from star trek:
"When a person drops a hammer, I do not have be told that the hammer will in
fact hit the ground, it is because of my reason and logic I can make this
deduction on my own"
So there is not much of anything new here to Access developers as to how
this works.
In fact the REAL benefits of "browseto" commands that control the new
navigation form is for web publishing. In web land it is not practical to
launch other forms in separate web browsers because all the sudden one of
those forms that are hoping you can reference some form data in is now
browsing on Facebook or purchasing something on Ebay! In other words the
"browser to" command really translates to web based development extremely
well because it allows you to change the current display of a current form
with out actually having to launch another form - this don't have too many
ramifications in client based applications, but for web based it's a
significant requirement.
On the other hand looking at the trends and desktop software, even the
Acronis disk back tools I use now has a navigation setup of buttons along
the left side (and so very many other applications in the marketplace are
adopting this trend and style and layout that we can now build in access).
So when you click on the left side, then the the right side of the screen
changes its display. So the navigation system in access is becoming real
popular trend in software.
However, while the system is more formalized in access 2010, this type of
setup is something that a typical access developer has been able to cobble
together for more than 10 years with relative ease by simply changing the
"source object" setting of a Subform control.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenos...@msn.com
news:07f3d3b7-ada9-4d12...@y6g2000yqe.googlegroups.com...
>The Navigation Form is a new beast in A2010. So although your code
>would work in older versions it would not work as a Subform in a
>Navigation Form.
The source object of that control is simply being changed.
In fact there has been examples like this posted for access for what,
probably last 15 years?
You can do quite much the same thing as the navigation system with a normal
sub form.
You are simply changing the source object property of the Subform control.
In fact how this works is actually nothing new in terms of a conceptual idea
to access developers at all.
The only real big change in access 2010 means that Subform control can now
display of form or report.
Prior to 2010 you had to use a form, but now sub-form controls can now
display both.
So this is a great tip, and you can now display reports in forms with a
sub-form control.
>The Navigation Form
is a nice feature but the documentation is extremely limited
physics texts are also severely lacking.
But then again they assume you have a basic understanding of mathematics.
My point here is that is is a sub-form control and thus what you learned and
know about sub-form controls
over the years applies here.
In fact for the last at least 10+ years even when I have a form with say 5
tab controls each with a sub form, I NEVER had those sub-forms load.
I always used the tab's "change" event to set the source object of the
Subform control behind each tab. The reason why this was done then if I
have 10 tabs, then I do NOT load 10 sub-forms and waste all that delay time
to frustrate the user (and pull data from the server that I do not need
yet). So this form loads the same speed as ONE form with no sub forms. The
form loads fast since in fact none of the sub-forms load loaded or pull data
until the tab is clicked on.
I mean why load 5 or 10 sub forms when the user has not even looked at them
yet? I quite much assumed most developers do this.
So this type of on-demand of loading a Subform has been something that's
part of the landscape of access for many many years.
(and if it was not, it should have been)
Now it's possible some people don't actually understand that a Subform
control placed on a form is simply that of a control like any other form.
The idea that a Subform really exists inside of a form is a false pretext. A
subform is a control sitting on a form and there is a sourceobject property
that will create a instance of the form for this control based on the
sourceobject setting.
If a person understands sub-forms, then this basic understanding should mean
it is obvious that when changing the source object property of this control
the pervious form "instance" that the subform control was displaying no
longer exists.
I do think the syntax of browseto can be difficult when attempting to work
with sub-sub forms, but then again in such cases simply setting the source
object property is likely less work and since that is what we done in the
past for more than 10+ years then there no learning curve anyway.
So to be clear, the navigation system is based changing the source object
of a sub-form control.
From this tid bit of information then just about everything else in terms of
how this will work can be deducted from logic and previous experience one
has with sub-form controls. In other words first principles of reasoning can
be used here:
To loosely quote Spock from star trek:
"When a person drops a hammer, I do not have be told that the hammer will in
fact hit the ground, it is because of my reason and logic I can make this
deduction on my own"
So there is not much of anything new here to Access developers as to how
this works.
In fact the REAL benefits of "browseto" commands that control the new
navigation form is for web publishing. In web land it is not practical to
launch other forms in separate web browsers because all the sudden one of
those forms that are hoping you can reference some form data in is now
browsing on Facebook or purchasing something on Ebay! In other words the
"browser to" command really translates to web based development extremely
well because it allows you to change the current display of a current form
with out actually having to launch another form - this don't have too many
ramifications in client based applications, but for web based it's a
significant requirement.
On the other hand looking at the trends and desktop software, even the
Acronis disk back tools I use now has a navigation setup of buttons along
the left side (and so very many other applications in the marketplace are
adopting this trend and style and layout that we can now build in access).
So when you click on the left side, then the the right side of the screen
changes its display. So the navigation system in access is becoming real
popular trend in software.
However, while the system is more formalized in access 2010, this type of
setup is something that a typical access developer has been able to cobble
together for more than 10 years with relative ease by simply changing the
"source object" setting of a Subform control.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenos...@msn.com
Patrick Finucane
Nov 30, 2011, 1:18:49 PM11/30/11
to
On Nov 30, 10:22 am, "Albert D. Kallal"
> Pleasenospam_kal...@msn.com
Hi Albert:
I was hoping you'd step up to the plate as you are one of the few in
the whole wide world that understands Access 2010.
Unfortunately, your response did not provide any examples. You kind
of prove my point. Access 2010, the new and improved physics.
Hi Albert:
I was hoping you'd step up to the plate as you are one of the few in
the whole wide world that understands Access 2010.
Unfortunately, your response did not provide any examples. You kind
of prove my point. Access 2010, the new and improved physics.
Albert D. Kallal
Nov 30, 2011, 4:22:24 PM11/30/11
to
"Patrick Finucane" wrote in message
news:29204c42-17a2-4575...@g21g2000yqc.googlegroups.com...
>Hi Albert:
>I was hoping you'd step up to the plate as you are one of the few in
>the whole wide world that understands Access 2010.
>
>Unfortunately, your response did not provide any examples. You kind
>of prove my point. Access 2010, the new and improved physics.
However then again this is a new feature.
This will take time for the general community to adopt use and get up to
speed with the new syntax of browse to.
I mean we have 18+ years of previous Access examples built up on the
internet – these things take time and comparing existing amounts of examples
to new features is really not fair at all.
On the other hand a lot of the community doesn't have to use the new system,
and is able to change the source object with standard VBA code anyway.
But lets try a few examples:
I mean, assuming a form called "Main" and we want to swap out a sub-form, we
can use with classic VBA:
Me.custChild.SourceObject = "formB"
Now of course you could use the new browseto and do the same thing with:
DoCmd.BrowseTo acBrowseToForm, "formB", "Main.custChild"
I not sure the people reading this care all that much about the second
example or not.
And heck I suppose we could code this without hard coding the form name
like:
DoCmd.BrowseTo acBrowseToForm, "formB", Me.Name & ".custchild"
So all 3 examples do the same thing;
We JUST changed the sourceobject of a sub from control on the form called
"custChild".
I think at this point, most will drift off and say "nothing to see here"
(and I quite much agree).
For those still reading and not sleeping (I hope!), then of course things
get a little more messy if you take your existing form and drop it inside of
an existing form.
However the real point here is we simply changing the source object of a sub
form control and that this is not a new concept to Access developers.
I mean if you are JUST looking to swap out a sub-form form and filter it
with some drill down and you are NOT needing or wanting the navigation
buttons to change automatic for you?
Then I see little need (or advantage) to use browseto. (it makes no sense).
The navigation system is really a replacement for your main menu or the
switchboard that so many of us lamented over the years.
So if you not needing to have navigation button choices "switch to" when
using "browseto" on a navigation form, then setting source object property
is a better choice in most cases.
If you need filtering, then use the link/master/child settings or even set
filter on the sub form object.
I even often set the sub-form recordSource
(so these are all common and long time approaches that we all used over the
years and should continue to use).
As noted, the only exception or case is when you do not have a CHOICE to
swap out sub-forms and you MUST use browseto (such a case is web based).
There are a few examples here:
http://blogs.office.com/b/microsoft-access/archive/2010/02/23/access-2010-browseto-docmd-and-macro-action.aspx
Also, if you want some more examples then just download some of the web
templates. They are a great source of examples. They quite much ALL have
browseto commands since for web based applications the SourceObject setting
cannot be used like in VBA applications.
However the syntax transfers over quite well to VBA. So if you are just
looking for some examples, you can use the web based ones.
So the use cases are:
You need/want the navigation buttons to "switch" to a new choice for you
when you use browseto.
The "nav buttons" with the name of the form specified becomes active when
you use browseto IF THERE is a nav button with the name of the form you
specified. (if not, then no navigation button highlight does not occur).
Web based:
You rather must use browseto since you as a general rule DO NOT want a new
browser window to open.
As a result, then in web you need browseto for not only filtering, but it
also enables use of the back button in your browser to return to previous
screen where as using openform in web based this does NOT occur.
For VBA based systems I most certainly suggest you use the new navigation
system to layout and stich together your application forms and dump the old
switch board.
However, for that of filtering a sub form in an existing form? Or even
swapping out a sub-form in a an classic VBA application?
Gee, I would continue to do this quite much the way the Access community has
done such UI setups for many moons.
Anyway, the above links gives some syntax examples, so do the templates - I
note most are web based, but that quite much he motivation for this.
Just remember that each form + sub form is like this:
FormName.SubForm
If you have another level, then you need another pair like:
FormName.SubForm>AnotherFormName.AnotherSubForm
So think of one form you specify as a pair.
I should also note one more great tip is that if you use browseto without a
subform, then your current form is closed and replaced - this is VERY useful
in web based, and I not come up with a use in VBA client based, but I do
often see some asking for the ability to close the current form and open a
new one - browseto can do this trick for you.
eg:
DoCmd.BrowseTo acBrowseToForm, "NewFormToOpen"
In the above we are NOT specifying a "MainForm.subform" pair.
For applications that use tabbed interface as opposed to overlapping
windows?
Well, you never know, but we could see a future in Access where browseto is
used as often as openform!
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Access Developer
Nov 30, 2011, 5:46:34 PM11/30/11
to
"Albert D. Kallal" <PleaseNOOO...@msn.com> wrote
> In fact there has been examples like this posted for
> access for what, probably last 15 years?
I used a Tab Control with Subforms to experiment with a similar form of
navigation using either Access 95 or 97 (whenever the Tab Control was
introduced). I did the work for someone, agreeing to charge no more than
"normal" costs, so they didn't have to pay for my learning curve. It turned
out to be usable, but the users reported it was neither any easier nor any
more difficult to use than a normal form-to-form navigation structure, so I
did not pursue it to see if I could improve implementation time to match the
normal development methods we'd used for years.
As I recall, you have 'for ages' been able to use either a SubReport or a
SubForm control on a Report, so maybe (after all these years) adding Reports
to a SubForm on a Form is just "leveling the playing field". My view is that
Reports are for printing, and Forms are for display, so I wasn't exuberant
about interactive Report capabilities when introduced.
Larry Linson
Microsoft Office Access MVP
> In fact there has been examples like this posted for
> access for what, probably last 15 years?
navigation using either Access 95 or 97 (whenever the Tab Control was
introduced). I did the work for someone, agreeing to charge no more than
"normal" costs, so they didn't have to pay for my learning curve. It turned
out to be usable, but the users reported it was neither any easier nor any
more difficult to use than a normal form-to-form navigation structure, so I
did not pursue it to see if I could improve implementation time to match the
normal development methods we'd used for years.
As I recall, you have 'for ages' been able to use either a SubReport or a
SubForm control on a Report, so maybe (after all these years) adding Reports
to a SubForm on a Form is just "leveling the playing field". My view is that
Reports are for printing, and Forms are for display, so I wasn't exuberant
about interactive Report capabilities when introduced.
Larry Linson
Microsoft Office Access MVP
Albert D. Kallal
Dec 1, 2011, 4:59:55 PM12/1/11
to
"Access Developer" wrote in message news:9jnq2b...@mid.individual.net...
>As I recall, you have 'for ages' been able to use either a SubReport or a
>SubForm control on a Report
To be honest, yes I did have that information floating around in my "brain",
but I did have to "recall it" and I not thought about that feature in some
time.
I have rare used this ability (to my own fault and shortcoming).
>so maybe (after all these years) adding Reports to a SubForm on a Form is
>just "leveling the playing field".
A marvelous point, one I had not really thought of....
>My view is that Reports are for printing, and Forms are for display, so I
>wasn't exuberant about interactive Report capabilities when introduced.
100% Agreed.
However, we do seem to be "finally" printing less and less paper.
I know! We said computers would allow us to use less paper for years!
In fact the PC desktop revolution cased a HUGE boom in pulp and paper
industry!
However, I am seeing this change in reduced paper use for "real".
With low cost laptops, netbooks, tablet and smartphones etc and
wider use of email, then I am seeing less paper printing occurring
for the first time in 20 years of this industry.
Paper is not dead, but I think we finally turned a point in which we can
and will use less paper on a daily basis.
>As I recall, you have 'for ages' been able to use either a SubReport or a
>SubForm control on a Report
but I did have to "recall it" and I not thought about that feature in some
time.
I have rare used this ability (to my own fault and shortcoming).
>so maybe (after all these years) adding Reports to a SubForm on a Form is
>just "leveling the playing field".
>My view is that Reports are for printing, and Forms are for display, so I
>wasn't exuberant about interactive Report capabilities when introduced.
However, we do seem to be "finally" printing less and less paper.
I know! We said computers would allow us to use less paper for years!
In fact the PC desktop revolution cased a HUGE boom in pulp and paper
industry!
However, I am seeing this change in reduced paper use for "real".
With low cost laptops, netbooks, tablet and smartphones etc and
wider use of email, then I am seeing less paper printing occurring
for the first time in 20 years of this industry.
Paper is not dead, but I think we finally turned a point in which we can
and will use less paper on a daily basis.
Access Developer
Dec 1, 2011, 6:15:06 PM12/1/11
to
> However, we do seem to be "finally" printing less and less paper.
> I know! We said computers would allow us to use less paper
> for years! In fact the PC desktop revolution cased a HUGE
> boom in pulp and paper industry!
Oh, my, I remember a "kickoff meeting" back in 1984 (maybe 1983) when I was
> I know! We said computers would allow us to use less paper
> for years! In fact the PC desktop revolution cased a HUGE
> boom in pulp and paper industry!
a wage-slave for "a major computer manufacturer" where our guest speaker was
from IBM's (mainframe) Office and the buzzword was "paperless office". As
he lauded how soon that would happen, I thought "Don't sell you paper
company stock _just yet_!"
Larry
Patrick Finucane
Dec 11, 2011, 10:47:49 AM12/11/11
to
On Nov 30, 3:22 pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
> http://blogs.office.com/b/microsoft-access/archive/2010/02/23/access-...
Thank you for you input. I created a form that contained a subform
and was able to reference fields between the two in a NavForm.
It works. The NavForm has a NavigationTargetName (the form's name)
and Name property...like NavigationButton2. All of the embedded forms
has the default name NavigationSubform, the tab index always 1 and
that throws me off. And from there there's NavigationControl0.
It'd be nice, IMO, if we could had a built-infunction called
FormReportPath("Optional Property") we could call, and it would
debug.print the path of the control in a form when running and print
output for Forms!ManForm!FormSubForm.Form... I'll assume that'd save
developers some time.
wrote:
and was able to reference fields between the two in a NavForm.
It works. The NavForm has a NavigationTargetName (the form's name)
and Name property...like NavigationButton2. All of the embedded forms
has the default name NavigationSubform, the tab index always 1 and
that throws me off. And from there there's NavigationControl0.
It'd be nice, IMO, if we could had a built-infunction called
FormReportPath("Optional Property") we could call, and it would
debug.print the path of the control in a form when running and print
output for Forms!ManForm!FormSubForm.Form... I'll assume that'd save
developers some time.
0 new messages