473,320 Members | 1,969 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...ElseIf....statements

I'm trying to restrict available data for fields based on previous fields.

I have 3 text boxes - Region, County, Branch - When the user picks North in the Region combo-box I want the County combo-box to only show the counties associated with the North Region. In turn I only want the Branches for the selected County to be available in the Branch combo-box.

Basically I'm looking for Region=Central; County=Orange & Seminole & Osceola; Branch=Headquarters & Oviedo & Kissimmee; etc.

I've been able to write the:

If Region = "Central" Then County = "Orange"
ElseIf Region = "Central-East" Then County = "Brevard"
End If

statements but now I need to take it to the next level.

Any assistance would be greatly appreciated.
Sep 1 '06 #1
6 2621
I have an alternative idea (it might sound complex, but really it doesn't seem that hard inm y mind. mind you I haven't tried this myself, but it might be worth a shot to try out):

Make the combo-boxes multi value combo's. As in for the Region combobox have 2 columns, one for a number and the other for the text that the user sees in the combo (in the properties for the combo set the width for the value column to 0 so the user doesnt see it the numbers).
e.g.
Reg | Txt
-----------------
1 | Central
2 | North
etc.
For the 'County' combo make it a 3 column combo, each row has its text, its own identifier value (like in the 'Region' combo), but also a column attributed to values in the 'Region' combo.
e.g.
Reg | Cty | Txt
-------------------------
1 | 1 | Orange
1 | 2 | Seminole
1 | 3 | Osceola
2 | 4 | <county for North>
etc.
Again do the same sort of thing for the final combo, except they refer to the individual values for the 'County' combo (the second column in my diagram).
e.g.
Cty | Br | Txt
--------------------------
1 | 1 | Headquarters
1 | 2 | Oviedo
1 | 3 | Kissimmee
2 | 4 | <branch for Seminole>
etc.

Then in the 'AfterUpdate' function for the 'Region' combo make it so only the rows with the value for that region in their 'Reg' column are shown, and then call the 'AfterUpdate' function for the 'County' combo at the end. Do the same sort of thing for the 'County' combo, but you won't need to call another 'AfterUpdate' function.

Hope that makes some kind of sense.
Sep 1 '06 #2
If anyone wants to correct me on the above idea, add to it, or tell me that it won't work for whatever reason please do.
Sep 1 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
The three lookup tables need to be designed as follows:

tblBranch {BranchID, BranchName, CountyID(FK)}
tblCounty {CountyID, CountyName, RegionID(FK)}
tblRegion {RegionID, RegionName}

Create a cboRegion (Region Combo Box) and make Row Source = SELECT RegionID, RegionName FROM tblRegion. Column Count = 2, Bound Column = 1 and Column Widths = 0 cm; 3 cm

Create a cboCounty (County Combo Box and leave the Row Source blank. Column Count = 2, Bound Column = 1 and Column Widths = 0 cm; 3 cm

Create a cboBranch (Branch Combo Box and leave the Row Source blank. Column Count = 2, Bound Column = 1 and Column Widths = 0 cm; 3 cm

Make sure that the Row Source Type of all the Combo boxes are "Table/Query"

Now in the After Update event of cboRegion put the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboRegion_AfterUpdate()
  2.     Me.cboCounty.RowSource = "SELECT CountyID, CountyName FROM tblCounty WHERE RegionID=" & Me.cboRegion
  3. End Sub
  4.  
  5.  
And in the After Update event of cboCounty put the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboCounty_AfterUpdate()
  2.     Me.cboCounty.RowSource = "SELECT BranchD, BranchName FROM tblBranch WHERE CountyID=" & Me.cboCounty
  3. End Sub
  4.  
  5.  

Hope this makes sense

I'm trying to restrict available data for fields based on previous fields.

I have 3 text boxes - Region, County, Branch - When the user picks North in the Region combo-box I want the County combo-box to only show the counties associated with the North Region. In turn I only want the Branches for the selected County to be available in the Branch combo-box.

Basically I'm looking for Region=Central; County=Orange & Seminole & Osceola; Branch=Headquarters & Oviedo & Kissimmee; etc.

I've been able to write the:

If Region = "Central" Then County = "Orange"
ElseIf Region = "Central-East" Then County = "Brevard"
End If

statements but now I need to take it to the next level.

Any assistance would be greatly appreciated.
Sep 1 '06 #4
I think that's what I said, just in a more coded fashion. Yay!
Sep 3 '06 #5
MMcCarthy
14,534 Expert Mod 8TB
Wasn't disagreeing with you. Just trying to be helpful ...



I think that's what I said, just in a more coded fashion. Yay!
Sep 3 '06 #6
Wasn't disagreeing with you. Just trying to be helpful ...
Never said you weren't disagreeing with me, in fact more the opposite. I was happy that you backed me up actually. and presented it in a different way.
Sep 4 '06 #7

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

Similar topics

3
by: James | last post by:
Hi, I have a form with 2 fields. 'A' 'B' The user completes one of the fields and the form is submitted. On the results page I want to run a query, but this will change subject to which...
7
by: Lauren Quantrell | last post by:
Is there any speed/resource advantage/disadvantage in using Select Case x Case 1 Case 2 etc. many more cases... End Select VS.
5
by: Leon | last post by:
Is there anyway I could clean-up all these ElseIF statements with a select/case or is this the best that can be done ("I can't think of a better way")? Private Sub Update_Click(ByVal sender As...
6
by: Ivan Sergio Borgonovo | last post by:
First thanks to Tom Lane who helped me promptly. Now let's come to the problem: create or replace function testa( ) returns char(32) as ' begin if 1=2 then if 1=2 then
6
by: Angelique | last post by:
All, I'm attempting to get Access to return a number on a form field based on input from three different fields. I'm using the code builder to associate the task with the output field. This is...
3
by: xSmilinx | last post by:
Hey guys.. Im a total beginner to VB :) Im not sure if i was supposed to post this here or in the Access Forums as im using VB in Access. But heres my code: Private Sub English1_GotFocus() ...
3
by: amrhi | last post by:
Hy guys , I try to make conditional statement by using if and elseif . the problem are if i use 2 conditional statement the script run well but if try add more conditinal statement it's not working...
17
by: JRough | last post by:
I'm trying to get error proof code. I have this code which seems to work but now I look at it I think it should be elseif not else and I wonder why it works. It is in the block:...
13
by: scottbouley | last post by:
HELP! I'm trying to check a form for missing values. Two of the fields are required and therefore the corresponding message boxes are VBOkOnly. The other two are for warning purposes and their...
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
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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.