473,320 Members | 1,695 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

If...Then...Else statement

55
I have two combobox fields with the second combobox (Department) dependent on the first combobox (Ministry). If the value of the first combobox changes the options in the second combobox should change as well. I have used an
Expand|Select|Wrap|Line Numbers
  1. If combobox1.value change Then combobox2.Additem "values"
. What this does is, when I select value for first field then second combobox updates well, but when I try to change value for first combobox without closing the form, combobox2 shows options of chosen combobox1 value and those of the option that I had chosen before. The values I cached. So am wondering if I am using the correct statement or there is a way to modify my code.

Example code:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.Ministry) And Ministry.Value = "PAT" Then
  2. Department.AddItem "xyzs"
  3. Department.AddItem "sxcty"
  4. End If
  5. If Not IsNull(Me.Ministry) And Ministry.Value = "MSP" Then
  6. Department.AddItem "bps"
  7. Department.AddItem "dce"
  8. Department.AddItem "dps"
  9. Department.AddItem "gic"
  10. Department.AddItem "nac"
  11. Department.AddItem "ofp"
  12. Department.AddItem "oop"
  13. Department.AddItem "ste"
  14. End If
Mar 4 '09 #1
8 3396
NeoPa
32,556 Expert Mod 16PB
I'm not sure if you're even on the right lines here Lebbsy.

Check out Example Filtering on a Form. You should find all you need to know in there.

Come back if you have any specific questions related to this.

PS. It could be as simple as calling the Me.Requery, but it will help you to understand it better.
Mar 4 '09 #2
missinglinq
3,532 Expert 2GB
@Lebbsy
The concept you're describing is called Cascading Comboboxes. You shouldn't be trying to use AddItem to fill the second combobox, but rather basing the second combobox's Rowsource on the value of the first one.

Here's a tutorial on the process by Rabbit, one of our experts:

http://bytes.com/topic/access/insigh...mbo-list-boxes

Linq ;0)>
Mar 4 '09 #3
Lebbsy
55
Thanks for the response but that will not do. I have 23 choices for combobox1 and each choice can have between 1 and 15 choices. That is, assume I have the following:
  • Combobox1 value = RSDT Then Combobox2 values are FGHT, GHTYU, SDTU, PDSR
  • Combobox1 value = MUNSE Then Combobox2 values are SPEDT, KGUTY, GSDE
  • Combobox1 value = DESC Then Combobox2 values are FRGTY, SDERT, SDERT
RSDT has 4 choices, MUNSE and DESC have 3 each. On choosing RSDT only the 4 values under it should be shown. If I change to MUNSE only the 3 values under it should be shown, etc. Now the situation I have is: If I open a form and choose DESC, the three values will show ok for the first time. Without closing the form or saving my data, when I select MUNSE, both values for DESC and MUNSE will show whereas I want values for MUNSE only. How can I stop this caching?

Regards,
Mar 4 '09 #4
Lebbsy
55
So missingling how do you suggest I handle values for combobox2?
Mar 4 '09 #5
NeoPa
32,556 Expert Mod 16PB
I would suggest that probably it IS the way to go. However, if you want to continue to use the .AddItem route (RowSourceType="Value List") then there is also a .RemoveItem method associated with a ComboBox control.

As this would need to be called repetitively though (to clear it down completely), it may be easier to say :
Expand|Select|Wrap|Line Numbers
  1. Me.YourCombo.RowSource = ""
Mar 4 '09 #6
missinglinq
3,532 Expert 2GB
How many options is not really the point at all! Using cascading comboboxes is the only rational way to do this. All you need is a table with the two fields, Ministry and Department, then base your comboboxes on the table. Reconstructing the second combobox every time you access it simply makes no sense.

Also, what happens when a new item is added, or someone decides the abbreviations you're now using need to be changed? Your way, you've got to go back into the code, because you've hard wired the items. Using cascading a table and comboboxes you simply add/edit the items in the table. No code changes need to be made.

Linq ;0)>
Mar 4 '09 #7
Lebbsy
55
Thank you guys.. I cascaded the combobox and it worked fine. Just did not want to create a new table but ended up doing it......
Mar 5 '09 #8
missinglinq
3,532 Expert 2GB
It's really the only way to handle this! Storing items, options, etc. in tables is always preferable to hardwiring them. Makes it easy for you or your end users to modify/add to them without going back into the code.

Glad you got it working!

Linq ;0)>
Mar 5 '09 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

27
by: Ron Adam | last post by:
There seems to be a fair amount of discussion concerning flow control enhancements lately. with, do and dowhile, case, etc... So here's my flow control suggestion. ;-) It occurred to me (a...
7
by: Gert Albertse | last post by:
I retrieve the recordset RS("SelectedMultiDisabilityEvent") form a table. The data type is text. When I run the next code: WHILE NOT RS.EOF Response.Write RS("SelectedMultiDisabilityEvent") &...
12
by: Roman Töngi | last post by:
In c++ there does not exist an if-statement as for example in Visual Basic, does it? //VB-analogy: if (cond.) statement; else if (cond.) statement; else statement;
19
by: GMKS | last post by:
Hello all, I have 13 check boxes on a form. I am trying to check all the check boxes to determine if they are true or false when I close the form. At present only the first IF...Then...Else...
2
by: misscrf | last post by:
I have a search form that is great. I have modified it in such a way, that when search results come up I can bring it back to a useful spot, say an entry form or a report. Here is my lemon (...
4
by: louise raisbeck | last post by:
I have this scenario (simplified) function addnewdata () { check for partial match already in db for information entered by user if (partialmatch succeeds) { open new window aspx page (using...
3
by: Amy | last post by:
Hi, I have 6 If Then Else statements I was supposed to write. I did so but I know that they have to be wrong because they all look the same. Could someone take a look at them and point me in the...
4
by: ECathell | last post by:
I had read an article at one time that suggested a pattern to get around deeply nested if..then..else hell... Can anyone point me in that direction? select case statements wont work for me in...
14
by: lawjake | last post by:
I am having a dispute at work over endifs, and cannot find any treatise on this. So I am hoping that I can get a lot of correspondece confirming my belief. Here it is: I believe the following:...
1
by: digidave | last post by:
I am keenly aware that my coding skills are extremely noob but please indulge me a second.. Take a look at these queries.. $sql = "SELECT DISTINCT year FROM _current_floats_config WHERE active =...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.