A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Possible to link fields in a form to narrow selections?



 
 
Thread Tools Display Modes
  #1  
Old April 24th, 2010, 03:48 PM posted to microsoft.public.access.forms
MEL
external usenet poster
 
Posts: 249
Default Possible to link fields in a form to narrow selections?

I'm working on making a form more user friendly -
What I'd like to do is to link multiple fields and to narrow selections in
field2 and field3 based off of what a user selects in field1.

Example:
I have field1 Principal with 10 options.
I have field2 PrimaryCategory with 20 options, however, there are only 2
valid options per Principal.
I have field3 Brand with 50 options, however, there are only 2-5 valid
options per Principal.
Right now, all options for field2 and field3 show up in the combo box
regardless of what is chosen in field1.
What I'd like is once user makes selection in field1, for field2 and field3
to automatically show ONLY the valid options for that specific selection in
field1.

Is this possible? Can anyone give me a starting point for figuring out how
to set this up? Would an ifthen statement work here - and if so, do I put
that in the code on field2 and field3?

I'm working in both Access 2003 and 2007, depending on if I'm doing the work
at the office or at home.

I appreciate any help!
  #2  
Old April 24th, 2010, 05:34 PM posted to microsoft.public.access.forms
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Possible to link fields in a form to narrow selections?

On Sat, 24 Apr 2010 07:48:03 -0700, Mel
wrote:

Absolutely this is possible.
The query for the second dropdown should "look back" at the first one
to see which Principal was selected, and only select rows for that
principal. Something like:
select CategoryID, CategoryName
from tblCategories
where PrincipalID = Forms!myForm!myPrincipalDropdown

Then in the Principal dropdown's AfterUpdate event write one line:
myCategoryDropdown.Requery

(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP


I'm working on making a form more user friendly -
What I'd like to do is to link multiple fields and to narrow selections in
field2 and field3 based off of what a user selects in field1.

Example:
I have field1 Principal with 10 options.
I have field2 PrimaryCategory with 20 options, however, there are only 2
valid options per Principal.
I have field3 Brand with 50 options, however, there are only 2-5 valid
options per Principal.
Right now, all options for field2 and field3 show up in the combo box
regardless of what is chosen in field1.
What I'd like is once user makes selection in field1, for field2 and field3
to automatically show ONLY the valid options for that specific selection in
field1.

Is this possible? Can anyone give me a starting point for figuring out how
to set this up? Would an ifthen statement work here - and if so, do I put
that in the code on field2 and field3?

I'm working in both Access 2003 and 2007, depending on if I'm doing the work
at the office or at home.

I appreciate any help!

  #3  
Old April 24th, 2010, 05:42 PM posted to microsoft.public.access.forms
Beetle
external usenet poster
 
Posts: 1,254
Default Possible to link fields in a form to narrow selections?

This is commonly known as "Cascading Combo Boxes".

A google search on that term will return a multitude of links
to pages explaining how to set them up.

--
_________

Sean Bailey


"Mel" wrote:

I'm working on making a form more user friendly -
What I'd like to do is to link multiple fields and to narrow selections in
field2 and field3 based off of what a user selects in field1.

Example:
I have field1 Principal with 10 options.
I have field2 PrimaryCategory with 20 options, however, there are only 2
valid options per Principal.
I have field3 Brand with 50 options, however, there are only 2-5 valid
options per Principal.
Right now, all options for field2 and field3 show up in the combo box
regardless of what is chosen in field1.
What I'd like is once user makes selection in field1, for field2 and field3
to automatically show ONLY the valid options for that specific selection in
field1.

Is this possible? Can anyone give me a starting point for figuring out how
to set this up? Would an ifthen statement work here - and if so, do I put
that in the code on field2 and field3?

I'm working in both Access 2003 and 2007, depending on if I'm doing the work
at the office or at home.

I appreciate any help!

  #4  
Old April 24th, 2010, 09:50 PM posted to microsoft.public.access.forms
MEL
external usenet poster
 
Posts: 249
Default Possible to link fields in a form to narrow selections?

Thanks so much to both of you!
Knowing what this is called should help me find resources to accomplish this
- and your example should help as well, Tom.



  #5  
Old April 29th, 2010, 06:39 PM posted to microsoft.public.access.forms
MEL
external usenet poster
 
Posts: 249
Default Possible to link fields in a form to narrow selections?

I have successfully set this up for my table -
However, I do have one glitch that I'm hoping for some advise on.

When I am in the form and make my selection for "Principal", I am
immediately sent to the very first form record.
If I navigate back to the new record and do not change the "Principal"
selection, then I can continue to enter data and the two fields that update
from "Principal" do show the correct selections.
If the "Principal" selection is changed here, then I am sent back to the
first record once again.

In the After Update line of Principal, I have simply "Requery".
(I have tried with no luck to go into the "Requery" macro and specify the
specific fields that I want to requery - not sure if this is related to my
issue here.)

Thanks again for any suggestions/tips/etc.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.