If...Then...Else Statement Help

[Guest]

Hello,

I have 30 branch names that are divided by Regions. What I want to do is
when the user clicks on the CBO to selct a Branch Name, I want the Regions
Field to automatically input the correct region. How can I do this for
multiple branches under one region?

Example:
Let's say Mangum, Katy, 290, TC Jester are in the West Region, How can I
code the If, then , else statement to say if one of those branch names in the
group is selcted then Region is West, Elseif so on and so on until I finish
all the Regions.

I can do this one Branch name at time but never did groups so I would
appreciate any help you could give.

Thank you in advance!!!

 

[OldPro]

Hello,

I have 30 branch names that are divided by Regions. What I want to do is
when the user clicks on the CBO to selct a Branch Name, I want the Regions
Field to automatically input the correct region. How can I do this for
multiple branches under one region?

Example:
Let's say Mangum, Katy, 290, TC Jester are in the West Region, How can I
code the If, then , else statement to say if one of those branch names in the
group is selcted then Region is West, Elseif so on and so on until I finish
all the Regions.

I can do this one Branch name at time but never did groups so I would
appreciate any help you could give.

Thank you in advance!!!

If they are linked in a table, then use a dlookup( ) function to
retrieve the proper region. Dlookup( ) can be called and the textbox
updated in the afterupdate event of the combobox.
textbox1 = dlookup("Region","tblBranches","[Branch]=' " & combo1 & " '
")

 

[Guest]

Hello Old Pro,

Thank you for your response. They are not linked in a table. The database is
a flatsheet database with one main table. Just to make it easier for the
processors to enter a region I thought if it can prefill once the Branch CBO
is clicked or updated, it would save them the trouble of having to keep
referring to their cheat sheet.

Any other ideas?

Hello,

I have 30 branch names that are divided by Regions. What I want to do is
when the user clicks on the CBO to selct a Branch Name, I want the Regions
Field to automatically input the correct region. How can I do this for
multiple branches under one region?

Example:
Let's say Mangum, Katy, 290, TC Jester are in the West Region, How can I
code the If, then , else statement to say if one of those branch names in the
group is selcted then Region is West, Elseif so on and so on until I finish
all the Regions.

I can do this one Branch name at time but never did groups so I would
appreciate any help you could give.

Thank you in advance!!!

If they are linked in a table, then use a dlookup( ) function to
retrieve the proper region. Dlookup( ) can be called and the textbox
updated in the afterupdate event of the combobox.
textbox1 = dlookup("Region","tblBranches","[Branch]=' " & combo1 & " '
")

 

[OldPro]

Hello Old Pro,

Thank you for your response. They are not linked in a table. The database is
a flatsheet database with one main table. Just to make it easier for the
processors to enter a region I thought if it can prefill once the Branch CBO
is clicked or updated, it would save them the trouble of having to keep
referring to their cheat sheet.

Any other ideas?

If they are linked in a table, then use a dlookup( ) function to
retrieve the proper region. Dlookup( ) can be called and the textbox
updated in the afterupdate event of the combobox.
textbox1 = dlookup("Region","tblBranches","[Branch]=' " & combo1 & " '
")- Hide quoted text -

- Show quoted text -

If it isn't being looked up from a table, then where do you want it to
find the information? If it is a relatively small number of branches,
then you could use a switch statement to select the region and hard
code the region for each branch. You would have to modify the code
everytime a branch opened or closed. This is NOT recommended.
Your best bet is to create a lookup table.

 

[Guest]

I'm sorry, the fields Branch Name and Regions fields are currently linked to
a table of their own but not linked to each others table. Am I making sense?

Thanks!!!

Hello Old Pro,

Thank you for your response. They are not linked in a table. The database is
a flatsheet database with one main table. Just to make it easier for the
processors to enter a region I thought if it can prefill once the Branch CBO
is clicked or updated, it would save them the trouble of having to keep
referring to their cheat sheet.

Any other ideas?

On Nov 9, 3:13 pm, Stockwell43 <[email protected]>
wrote:
Hello,

I have 30 branch names that are divided by Regions. What I want to do is
when the user clicks on the CBO to selct a Branch Name, I want the Regions
Field to automatically input the correct region. How can I do this for
multiple branches under one region?

Example:
Let's say Mangum, Katy, 290, TC Jester are in the West Region, How can I
code the If, then , else statement to say if one of those branch names in the
group is selcted then Region is West, Elseif so on and so on until I finish
all the Regions.

I can do this one Branch name at time but never did groups so I would
appreciate any help you could give.

Thank you in advance!!!

If they are linked in a table, then use a dlookup( ) function to
retrieve the proper region. Dlookup( ) can be called and the textbox
updated in the afterupdate event of the combobox.
textbox1 = dlookup("Region","tblBranches","[Branch]=' " & combo1 & " '
")- Hide quoted text -

- Show quoted text -

If it isn't being looked up from a table, then where do you want it to
find the information? If it is a relatively small number of branches,
then you could use a switch statement to select the region and hard
code the region for each branch. You would have to modify the code
everytime a branch opened or closed. This is NOT recommended.
Your best bet is to create a lookup table.

 

[OldPro]

I'm sorry, the fields Branch Name and Regions fields are currently linked to
a table of their own but not linked to each others table. Am I making sense?

Not really.
"the fields Branch Name and Regions fields are currently linked to a
table of their own" seems to indicate they share a table while "not
linked to each others table" indicates they do not. How do we know
which branches are in which regions? Where is their relationship
indicated?

 

[Guest]

Morning,

Ok, let me clarify a bit.

I have one main table which among other fields it contains one for Branch
and one for Region. I have the branch names in a separate table which I have
a CBO linked to so I can add new names when needed and the user selects the
branch from the CBO on each new loan. The same is for the region field as
well.

To find out which branch is in which region, the manager will give me a
cheat sheet that the department is currently using. So I was going to use the
following code:

If Me.Branch = "Mangum" Then
Me.Region = "West

ElseIf Me.Branch = "Pasadena" Then
Me.Region = "South"

ElseIf Me.Branch = "Hempstead" Then
Me. Region = "West"

So on and so on until I get all 53 branch names coded. As you can see I
coded two branches for the same region and it will work fine but, I was
wondering if there was a way to code it more or less to say

If Me.Branch = "Mangum" Or " Hempstead" Then
Me.Region = "West"

This way it cuts down on the coding and easier to update. Does that help? If
not, I appreciate your time in trying to help and will go with it the long
way but will still get the same results. I was hoping for a short cut plus
something I can use going forward if I run into this again.

Thanks!!!!

 

[J_Goddard via AccessMonster.com]

Hi -

In the separate table with the branch names, add the region as well, so the
table more or less matches the 'cheat sheet'.

You should then take the Region field out of the main table - it's now
redundant because the region data is in the separate table.

Now your form can use the Dlookup function to determine the Region name once
the Branch name has been selected from the Combo Box.

John

Morning,

Ok, let me clarify a bit.

I have one main table which among other fields it contains one for Branch
and one for Region. I have the branch names in a separate table which I have
a CBO linked to so I can add new names when needed and the user selects the
branch from the CBO on each new loan. The same is for the region field as
well.

To find out which branch is in which region, the manager will give me a
cheat sheet that the department is currently using. So I was going to use the
following code:

If Me.Branch = "Mangum" Then
Me.Region = "West

ElseIf Me.Branch = "Pasadena" Then
Me.Region = "South"

ElseIf Me.Branch = "Hempstead" Then
Me. Region = "West"

So on and so on until I get all 53 branch names coded. As you can see I
coded two branches for the same region and it will work fine but, I was
wondering if there was a way to code it more or less to say

If Me.Branch = "Mangum" Or " Hempstead" Then
Me.Region = "West"

This way it cuts down on the coding and easier to update. Does that help? If
not, I appreciate your time in trying to help and will go with it the long
way but will still get the same results. I was hoping for a short cut plus
something I can use going forward if I run into this again.

Thanks!!!!

On Nov 13, 8:32 am, Stockwell43
<[email protected]> wrote:

[quoted text clipped - 7 lines]

which branches are in which regions? Where is their relationship
indicated?

 

[OldPro]

Hi -

In the separate table with the branch names, add the region as well, so the
table more or less matches the 'cheat sheet'.

You should then take the Region field out of the main table - it's now
redundant because the region data is in the separate table.

Now your form can use the Dlookup function to determine the Region name once
the Branch name has been selected from the Combo Box.

John

John's suggestion is valid and a better programming solution than what
you had outlined. That being said, it is easy to hardcode the
branches to the regions:
if cboBranch = "Mangum" Or cboBranch ="Hempstead" then
txtRegion="West"
endif

 

[Guest]

Hi Guys,

The code and instructions work great and I saved it for future use. However,
because I have about ten or so branches in each area, how do I add them to
the next line without the code turning red on me? When I hit the enter key
the code turns red but if I keep it on one line I don't have enough space?

Thanks!!

 

[Douglas J. Steele]

To continue from one line to another, you need a space and an underscore:

If cboBranch = "Mangum" Or cboBranch ="Hempstead" Or cboBranch = "xxx" _
Or cboBranch = "Something Else" Then

Note that to continue in the middle of a string, you need to end the string
and concatenate to what's on the next line:

If cboBranch = "This is a very, very, very long " & _
"possibility" Then

or

If cboBranch = "This is a very, very, very long " _
& "possibility" Then

 

[OldPro]

Hi Guys,

The code and instructions work great and I saved it for future use. However,
because I have about ten or so branches in each area, how do I add them to
the next line without the code turning red on me? When I hit the enter key
the code turns red but if I keep it on one line I don't have enough space?

Thanks!!






- Show quoted text -

Divide a line in two by tacking on a space and an underline character:
if cboBranch = "Mangum" _
Or cboBranch ="Hempstead" then
txtRegion="West"
endif

 

[J_Goddard via AccessMonster.com]

There is one thing to consider if you decide to use code instead of a table:
What happens when management decides to reorganize the regions, or new
branches are opened? Then you have to change all the code. It's much easier
just to change the table.

John

Hi Guys,

[quoted text clipped - 28 lines]

- Show quoted text -

Divide a line in two by tacking on a space and an underline character:
if cboBranch = "Mangum" _
Or cboBranch ="Hempstead" then
txtRegion="West"
endif

 

[Guest]

Excellent, Works like a charm. You guys are all great and I truely appreciate
all the help!!Thank you so much!