473,544 Members | 911 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Cascading Combo Boxes on continuous forms in Access 2000 - SOMEONE PLEASE HELP

Hi everyone,

Please let there be someone out there who can help.

I have two BOUND combo boxes on a continuous form, the second being
dependent on the first. I have no problem getting the second combo to
change depending on what values the user selects in the first box, it's
just that every time the user changes the first combobox, the second
combobox FOR EVERY RECORD goes blank.

The second combo does, however save the correct ID to the table, so
this is a bonus.

I have googled around and found that there is only 1 combobox shared
between all the records. Yes, I am not the only person to have asked
this question, BUT there does not seem to be an intelligible solution.

I found this advice on one thread:

"
I make the combo box only large enough where you see the drop down
arrow. About 1/4 inch wide.

Next, I move combo box arrow (on the right side) of the field EmpName.
Now I have a combo box and the field I need next to it (that unless
your in design view
looks just like a combo box) 0 column EmpID 1 column EmpName.

On the afterupdate event of the combo box I'll put something to the
effect
Me![EmpName] = Combo1.Column(2 )

Now I have the bound column 1 empid in the table and EmpName showing on
the
screen.

This way when it requeries I get the values I want and the Name
stays on the screen. The only draw back is that when your typing John
Smith
you must type in the full name or pick from the list.

"
tried it with the example below and found that if the textbox is
unbound then the textbox on every record changes. However I do not
want to save the any text descriptions, just the IDs and display the
text for the user.

An extremely small example:

tblPetTypes
PetTypeID (autonumber) PK
PetTypeDesc

tblPetBreeds
PetBreedID (autonumber) PK
PetTypeID FK
PetBreedDesc

tblPetSales
SalesID (autonumber) PK
PetTypeID FK (can I get rid of this?)
PetBreedID FK (ideally from a pets drop down box)
Date
Amount

Following the advice above I:

* created a BOUND continuous form (select * from tblSales)

* put a BOUND combo box (cboPetTypes) listing:

1 cats
2 dogs
3 birds

* put a BOUND combo box putting the breeds according to the category
chosen
(select PetBreedID, PetBreedDesc from tblPetBreeds where PetTypeID =
forms!myForm!cb oPetTypes). so user may get:

eg
Persian
Siamese

or
Terrier
Great Dane

or
Parrot
Finch

from tblPetBreeds depending on what they chose in cboPetTypes.

* select a pet type from first combo, second combo for every record
goes blank :(

How do I fix this?

Please provide specific steps to solve this problem including any code
or form or control settings. Otherwise, please advise if cascading
combos cannot be used on continuous forms.

Cheers
Edwinah63

Mar 8 '06 #1
9 6744
Rog
Ediwnah,

I suppose you requery your PetBreed combo box at some point in order to
have the correct entries that belong to the selected PetType? The
problem with continuous forms is that you requery the whole form, not
just the current record.

Google "problems with requery on continuous form" for a workaround
posted by Wayne Gillespie.

Rog

Mar 8 '06 #2
Rog
Ediwnah,

I suppose you requery your PetBreed combo box at some point in order to
have the correct entries that belong to the selected PetType? The
problem with continuous forms is that you requery the whole form, not
just the current record.

Google "problems with requery on continuous form" for a workaround
posted by Wayne Gillespie.

Rog

Mar 8 '06 #3
Hey Edwinah,

Try this:

Add a bound text box over the second comb box, and make it the same
size & font setting as the underlying combo box, except make the text
box width the same as the text portion of the combo box. Make the text
box locked and disabled.

That way the stored value for each record will be shown in the text
box, but the user has to use the combo box to change values. The fact
that there are two controls on the form should be not obvious to the
users, and the text box value will cover up the blank combo box values.

Let me know if this works out,

Rusty Morr

Hi everyone,

Please let there be someone out there who can help.

I have two BOUND combo boxes on a continuous form, the second being
dependent on the first. I have no problem getting the second combo to
change depending on what values the user selects in the first box, it's
just that every time the user changes the first combobox, the second
combobox FOR EVERY RECORD goes blank.

The second combo does, however save the correct ID to the table, so
this is a bonus.

I have googled around and found that there is only 1 combobox shared
between all the records. Yes, I am not the only person to have asked
this question, BUT there does not seem to be an intelligible solution.

I found this advice on one thread:

"
I make the combo box only large enough where you see the drop down
arrow. About 1/4 inch wide.

Next, I move combo box arrow (on the right side) of the field EmpName.
Now I have a combo box and the field I need next to it (that unless
your in design view
looks just like a combo box) 0 column EmpID 1 column EmpName.

On the afterupdate event of the combo box I'll put something to the
effect
Me![EmpName] = Combo1.Column(2 )

Now I have the bound column 1 empid in the table and EmpName showing on
the
screen.

This way when it requeries I get the values I want and the Name
stays on the screen. The only draw back is that when your typing John
Smith
you must type in the full name or pick from the list.

"
tried it with the example below and found that if the textbox is
unbound then the textbox on every record changes. However I do not
want to save the any text descriptions, just the IDs and display the
text for the user.

An extremely small example:

tblPetTypes
PetTypeID (autonumber) PK
PetTypeDesc

tblPetBreeds
PetBreedID (autonumber) PK
PetTypeID FK
PetBreedDesc

tblPetSales
SalesID (autonumber) PK
PetTypeID FK (can I get rid of this?)
PetBreedID FK (ideally from a pets drop down box)
Date
Amount

Following the advice above I:

* created a BOUND continuous form (select * from tblSales)

* put a BOUND combo box (cboPetTypes) listing:

1 cats
2 dogs
3 birds

* put a BOUND combo box putting the breeds according to the category
chosen
(select PetBreedID, PetBreedDesc from tblPetBreeds where PetTypeID =
forms!myForm!cb oPetTypes). so user may get:

eg
Persian
Siamese

or
Terrier
Great Dane

or
Parrot
Finch

from tblPetBreeds depending on what they chose in cboPetTypes.

* select a pet type from first combo, second combo for every record
goes blank :(

How do I fix this?

Please provide specific steps to solve this problem including any code
or form or control settings. Otherwise, please advise if cascading
combos cannot be used on continuous forms.

Cheers
Edwinah63


Mar 8 '06 #4
On 8 Mar 2006 05:11:34 -0800, "Rog" <de********@sta te.gov> wrote:
Ediwnah,

I suppose you requery your PetBreed combo box at some point in order to
have the correct entries that belong to the selected PetType? The
problem with continuous forms is that you requery the whole form, not
just the current record.

Google "problems with requery on continuous form" for a workaround
posted by Wayne Gillespie.

Rog


A small sample db (A2K) demonstrating the technique can be downloaded from -
http://www.bestfitsoftware.com.au/ut...usComboA2K.zip
Wayne Gillespie
Gosford NSW Australia
Mar 8 '06 #5
Hi Wayne,

Thanks for your reply, I downloaded the zip file as you suggested but
have been unable to open it with either Access 2, 97 or 2000.

Could you advise which version it is? or post the code and instructions
on the thread?

Regards

Edwinah63
Wayne Gillespie wrote:
On 8 Mar 2006 05:11:34 -0800, "Rog" <de********@sta te.gov> wrote:
A small sample db (A2K) demonstrating the technique can be downloaded from -
http://www.bestfitsoftware.com.au/ut...usComboA2K.zip


Mar 9 '06 #6
Hi Rog,

thanks For your reply, I do indeed requery cboPetBreeds in the after
update event of cboPetTypes.

Edwinah63

Mar 9 '06 #7
Hi Wayne,

I take it your sample db is an Access 2000 db, but I have been unable
to open it (message comes back that it an unrecognized file format or
created with a later version.) Could you confirm that the sample was
made with A2k?

I found a reference of one of your previous posts to putting a text box
beside the combo box but it mentioned BINDING the text box to my
underlying table. Wouldn't this mean that in addition to the
petbreedid that I want to save to my Sales table, that I would also
have save the breed description to my Sales table as well?

Edwina

Mar 9 '06 #8
On 8 Mar 2006 18:29:24 -0800, "Edwinah63" <ed*****@custom ercare.com.au> wrote:
Hi Wayne,

Thanks for your reply, I downloaded the zip file as you suggested but
have been unable to open it with either Access 2, 97 or 2000.

Could you advise which version it is? or post the code and instructions
on the thread?

Regards

Edwinah63


Try this copy (Access 2000)
http://www.bestfitsoftware.com.au/ut...usComboA2K.zip
Wayne Gillespie
Gosford NSW Australia
Mar 9 '06 #9
On 8 Mar 2006 18:42:50 -0800, "Edwinah63" <ed*****@custom ercare.com.au> wrote:
Hi Wayne,

I take it your sample db is an Access 2000 db, but I have been unable
to open it (message comes back that it an unrecognized file format or
created with a later version.) Could you confirm that the sample was
made with A2k?
There was a problem with the file. I have put a new version up. Yes it is A2k
format.

I found a reference of one of your previous posts to putting a text box
beside the combo box but it mentioned BINDING the text box to my
underlying table. Wouldn't this mean that in addition to the
petbreedid that I want to save to my Sales table, that I would also
have save the breed description to my Sales table as well?

Edwina


Assuming you have a table containing the breed description, add this table to
your form's recordscource with a join between petbreedid in both tables. There
is no need to include the breed description in the sales table.

See tblTestOptions in the recordsource of the sample file.
Wayne Gillespie
Gosford NSW Australia
Mar 9 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
4255
by: Heather | last post by:
Hi I am desparately looking for advice in relation to storing the results after selecting items from two combo boxes on a Referral form. The first combo box 'ctl Type' displays a full list of Agency Types, then the 2nd combo box displays a list of Organisations, depending upon the Agency Type Selected. The first combo box 'ctlType'...
1
2700
by: Robert Neville | last post by:
The solution to my dilemma seems straight-forward, yet my mind has not been forthcoming with a direct route. My Project form has a tab control with multiple sub-forms; these distinct sub-forms relate addresses (multiple addresses); companies, contacts, and tasks to each project (one to many). My challenge lies with the task sub-form which...
3
5328
by: Mike Jakes | last post by:
I hope that someone can offer a little advice on this one - I've searched the group but can't find an answer. I think that I'm doing something really stupid or missing something trivial, but see what you can make of this... I have a main form "Events" that contains a tab control. The tab control has 7 pages. The 7th page (named "Boats")...
4
64541
Rabbit
by: Rabbit | last post by:
Cascading Combo/List Boxes This tutorial is to guide you in the creation of Cascading combo/list boxes. That is when you have multiple combo/list boxes where the selection of an option in one determines the available options in the other. TERMINOLOGY Row Source: The table/query from which the Combo Box or List Box gets its values. Note:...
2
2275
by: SPOILED36 | last post by:
I am building a database to track attendance. I have one main form with multiple subforms. Within one of the subforms name sfrDailyAttendance, I also have cascading combo boxes (cboCategory and cboException) to filter difference absences (ie.Regular, Overtime, etc). cboCategory is filtered as follows: !!... Because I have the subform set...
4
3466
klarae99
by: klarae99 | last post by:
Hello, I am working on an Access 2003 Database. The tables that pertain to this issue are tblOrg, tblState, tblCity, and tblZip. I have posted the table structure with only the pertinant fields below. tblOrg OrgID, AutoNumber, PK ZipID, Number, FK tblState StateID, AutoNumber, PK
7
5820
by: Toireasa | last post by:
Hi, Newbie Access developer here, and my first post on this forum, so I might not get everything right - thanks in advance for your help and your patience! I'm using Access 2007, in XP. I'm currently trying to set up a whole pile of cascading combo boxes of different levels of complexity, so I started with the easiest set - and can't even...
8
9292
by: 20028431 | last post by:
This is driving me mad! I have been trying for 3 days now to work out how to do this with no success. I have tables for Customer, Bookings, Entertainment, Act, Agent, and several others. I have created a form to view existing bookings with Customer details as mainform and Bookings and Entertainment as subforms and this works fine. I...
0
7429
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7368
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7777
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7713
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5301
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3420
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1844
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
671
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.