GoToControl with selection in a Combo Box

  • Thread starter Thread starter CurtisK-Houston, TX
  • Start date Start date
C

CurtisK-Houston, TX

I'm creating a form where I have a combo box at the top part of a form with 5
options.

How can I set it up where if a user selects the 4th option it will
automatically take them to a different Control field at the bottom section of
the form, and if they select the 5th option it will take them to yet another
control on the form. Nothing needs to happen if they select any of the first
three. It would only be for the last two of the 5.

Any help or suggestions would be greatly appreciated. Not sure if this is
done with a macro, event, expression or none of the above.

Thanks in advance!
 
One approach would be to create an event procedure for that combobox's
AfterUpdate event.

In the event procedure, you could use something like (untested):

Select Case cboYourCombobox
Case {whatever values fall 1-3rd}
Me!YourFirstControl.SetFocus
Case {4th value}
Me!YourDifferentControl.SetFocus
Case {5th value}
Me!YourAnotherControl.SetFocus
Case Else
'just in case
End Select

NOTE! This pre-supposes that your combobox will ALWAYS have the same
choices.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Not having been to this site before I could not find the place to send you a
reply to your original post so I ended up sending you an email only to find
the reply button afterwards so you are probably going to get this twice as
I'm basically just cutting and pasting the text from my email on this
message. My sincere apologies.

First of all I really appreciate your prompt response. I'm still a little
confused about some of the language you have here as my knowledge of Visual
Basic is limited. I think your suggestion will work for me because I modified
your text here with what I thought was the right code inserted in and when I
chose either the 4th or 5th option it told me that it could not find the
control I specified so it seems like it is indeed trying to do what I want it
to do.

Here is a little more specific explaination of my Dbase and form. My company
sells electrical supplies. The Dbase is tracking sales opportunities where
our customers give us a bill of material to price for a job or long term
contract. The user enters information on the form about every opportunity we
are given for any customer to price so we can track the progress at every
stage of the bidding process which is where my combo box comes in.

My combo box has 5 options that drive to a field in my table labeled Bid
Stage. The options are in order and exactly as they are listed in the combo
box: New Opportunity Identified, In Process, Pending, Won, Lost

If the user selects any of the first three then they simply need to be taken
to the next field in the form which in my table is labeled Win Probability%,
however if they select a status of Won then I want it to take them directly
to the form field labeled Primary Win Reason. If Lost is selected then they
go to the Primary Loss Reason Control.

I tried entering the value with (_) in place of the spaces but it didn't
like that. I also did not know how you separate multiple values in the first
Case line you gave me. Do you use a comma, semicolon?

Lastly I wasn't sure what the (Me!) was for but assumed it stays in their
because I never get an error message on it. I also deleted the ('just in
case) line at the end and simply had it go "Case Else" and then "End Select"
because I did not think I needed it.

What am I doing wrong.

Thanks so much for your assistance!
 
Jeff,
Being new to this site I originally could not find where to reply to your
original post so I ended up sending you an email only to find the reply
button afterwards so just in case I have basically just cut and pasted the
contents of my email on this reply so you may end up getting this twice. My
sincere apologies.

First of all I really appreciate your prompt response. I'm still a little
confused about some of the language you have here as my knowledge of Visual
Basic is limited. I think your suggestion will work for me because I modified
your text here with what I thought was the right code inserted in and when I
chose either the 4th or 5th option it told me that it could not find the
control I specified so it seems like it is indeed trying to do what I want it
to do.

Here is a little more specific explaination of my Dbase and form. My company
sells electrical supplies. The Dbase is tracking sales opportunities where
our customers give us a bill of material to price for a job or long term
contract. The user enters information on the form about every opportunity we
are given for any customer to price so we can track the progress at every
stage of the bidding process which is where my combo box comes in.

My combo box has 5 options that drive to a field in my table labeled Bid
Stage. The options are in order and exactly as they are listed in the combo
box: New Opportunity Identified, In Process, Pending, Won, Lost

If the user selects any of the first three then they simply need to be taken
to the next field in the form which in my table is labeled Win Probability%,
however if they select a status of Won then I want it to take them directly
to the form field labeled Primary Win Reason. If Lost is selected then they
go to the Primary Loss Reason Control.

I tried entering the value with (_) in place of the spaces but it didn't
like that. I also did not know how you separate multiple values in the first
Case line you gave me. Do you use a comma, semicolon?

Lastly I wasn't sure what the (Me!) was for but assumed it stays in their
because I never get an error message on it. I also deleted the ('just in
case) line at the end and simply had it go "Case Else" and then "End Select"
because I did not think I needed it.

What am I doing wrong.

Thanks so much for your assistance!
 
Access is very, very literal. If you spell the name of your control one way
and then refer to it in the Select Case statement with a slightly different
spelling, Access won't find it (and will give an error message much like the
one you saw).

For the correct syntax on the Select Case statement, open a code module
(like the AfterUpdate event of your combobox) and search HELP for Select
Case.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Here is the code I have entered based on the parameters I gave you earlier.

Private Sub Stage_AfterUpdate()
Select Case cboStage
Case "Won"
Me!Primary Win Reason.SetFocus
Case "Lost"
Me!Primary Loss Reason.SetFocus
Case Else
Me!Win Probability%.SetFocus
End Select

End Sub

I keep getting the following Compile error 'Expected: ='.

I have been looking in the help and on the internet for help on how to
correctly enter the syntax but can't seem to find anything that points me in
the right direction. Do you have any advice? I can't find anything on the
'Me!' at the beginning of some of the lines. The labels in " " specify
options that can be selected in my combo box while I assume the 'Else'
statement covers the option for anything else they may select in the combo
box.
 
Here is the code I have entered based on the parameters I gave you earlier.

Private Sub Stage_AfterUpdate()
Select Case cboStage
Case "Won"
Me!Primary Win Reason.SetFocus
Case "Lost"
Me!Primary Loss Reason.SetFocus
Case Else
Me!Win Probability%.SetFocus
End Select

End Sub

I keep getting the following Compile error 'Expected: ='.

I have been looking in the help and on the internet for help on how to
correctly enter the syntax but can't seem to find anything that points me in
the right direction. Do you have any advice? I can't find anything on the
'Me!' at the beginning of some of the lines. The labels in " " specify
options that can be selected in my combo box while I assume the 'Else'
statement covers the option for anything else they may select in the combo
box.
 
Jeff,
I ultimately figured it out through lots and lots of trial and error.

Thanks for steering me in the right direction. I never would have figured it
out without your help!

Best Regards,
Curtis King-Houston, TX
 
Consider posting your solution back here. Other's may benefit from what you
learned.

(out of curiousity, did those control names with "spaces" in them cause the
problem? They usually do. A square bracket "[" and "]" surrounding control
names with spaces or disallowed special characters can usually help. Even
more help is to not use spaces or special characters or reserved words in
naming fields or controls!)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Here was the code that did the trick for me:

Private Sub Stage_AfterUpdate()
Select Case cboStage
Case Is = Won
Me![Primary Win Reason].SetFocus
Case Is = Lost
Me![Primary Loss Reason].SetFocus
Case Else
Me![Win Probability (%)].SetFocus
End Select

End Sub

Thanks again for all of your help!


Jeff Boyce said:
Consider posting your solution back here. Other's may benefit from what you
learned.

(out of curiousity, did those control names with "spaces" in them cause the
problem? They usually do. A square bracket "[" and "]" surrounding control
names with spaces or disallowed special characters can usually help. Even
more help is to not use spaces or special characters or reserved words in
naming fields or controls!)

Regards

Jeff Boyce
Microsoft Office/Access MVP



CurtisK-Houston said:
Jeff,
I ultimately figured it out through lots and lots of trial and error.

Thanks for steering me in the right direction. I never would have figured
it
out without your help!

Best Regards,
Curtis King-Houston, TX
Click to expand...
Click to expand...
 
Back
Top