Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 17th, 2012, 10:51 AM
Registered User
Points: 13, Level: 1
Points: 13, Level: 1 Points: 13, Level: 1 Points: 13, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Question ComboBox Selection triggers another event

Hi Everyone,
I am trying to program Access 2007 VBA to make visible an option frame based on the selection in a combo dropdown.
Essentially I need the following to happen;

when a certain selection is made in the comboBox, an option Frame will become visible or another action based on another choice, etc.

I know about the CASE statement but not sure how to code it.

so far I have something like; (where EventType) is the current name of the combo box but nothing happens when I choose Call in the dropdown.

if me.EventType.value = "Call" then
me.frame16.visible = true
end if


This way if the user clicks that an event was a phone call, the options for the result of that call will be displayed.

It seems very simple but I am not experienced and am not sure of what eventhandler to use; AfterUpdate, MouseDown, MouseUp, etc.


Could someone possibly give me an example of what I need to do, where I need to add the code and why. I really want to learn this.

many thanks for all your kind effort and time spent on my question.

many thanks,

Darren

Last edited by DDEWILDE; April 17th, 2012 at 10:53 AM.. Reason: Forgot to add a comment
Reply With Quote
  #2 (permalink)  
Old April 17th, 2012, 01:33 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Try this...

Hello Darren,

Thank you for posting your question about VBA Case statements here! Specifically, you asked:

Quote:
when a certain selection is made in the comboBox, an option Frame will become visible or another action based on another choice, etc. I know about the CASE statement but not sure how to code it.
...
Could someone possibly give me an example of what I need to do, where I need to add the code and why.
Ok, so first, it is important to understand what TYPE of data the ComboBox control returns for this example, because it can return the value that it shows, or a value that is an ID of the value it is showing, depending on how the ComboBox is setup. In this example, the ComboBox I'm using only displays a value list, but depending on what data your ComboBox is attached to, this solution could be slightly different.

As for the solution, if you make a new form in Design view in Access and then drop a ComboBox on to the form, it will launch the ComboBox wizard. In this case, I chose the "Value List" option on the wizard and typed in the values "Value 1" and "Value 2" for the values to be displayed in the ComboBox. This means that the ComboBox will return these specific string values (which I will show below). I then added 2 more frame controls to the form, named "Frame1" and "Frame2". Now that we have all of the controls we need on the form for this example. Next, click on the ComboBox control to select it, and then open the Property Sheet (F4) to see the properties for the control. On the "Event" tab of the property sheet, find the "On Change" event and click the "..." button for it, and choose the "Code Builder" option on the Build Event dialog. This will open VBE and create an "On Change" Sub for the ComboBox control. Place the following code inside of this "On Change" Sub:

Code:
    Dim strOption As String
    
    ' Get the value selected from the ComboBox, in this case a String
    strOption = Me.Combo0
    
    ' Use the Case statement to make the proper Frame visible, based on the
    ' value selected in the ComboBox.
    Select Case strOption
        
        Case "Value 1":
            Me.Frame1.Visible = True
            Me.Frame2.Visible = False
        
        Case "Value 2":
            Me.Frame1.Visible = False
            Me.Frame2.Visible = True
        
        Case Else:
            MsgBox "Option not recognized!"
    
    End Select
Now you should be ready to run the form. Save everything and switch the new form to Form view and try selecting values in the ComboBox. You should see the Frame controls being switched between visible and invisible!

Anyway, I hope that all helps, but if you have any other questions or need more help with this example, please just let me know! Good luck and thanks again for posting!

Sincerely,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
  #3 (permalink)  
Old April 18th, 2012, 06:55 PM
Registered User
Points: 13, Level: 1
Points: 13, Level: 1 Points: 13, Level: 1 Points: 13, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear GiGriffith,

Thank you so much for your help. It worked perfectly. There is only one thing I am stuck on, it is related to the last post but possible I might need to start a new thread..I hope you can help me avoid that.

I played about with the form after getting the combo selection to work as you advised. One of the things I wanted to have was a color bar and an icon to represent the selection. I drew the controls as boxes, filled them with the required colors, added the images to represent the choice then added the code to the end of the combo On_Change VBA code you provided to hide unwanted icons and the two other color bars. (On editing I am thinking I need to delete the two other color bars and use VBA to change the back color of one bar depending on the choice.

The prob I am now having is that although the code works perfectly and the icons and color bars change with the selection, they do not change as I scroll through other records. In other words the same icon and the same color bar remain on the next record(s) even though the combo selections were different and I coded the on change event to hide any unecessary icons.

Any ideas what I am doing wrong. My apologies if I have been cheeky trying to ask another question on an already answered question.

Thanks so much again, I really appreciate your help,

Darren

Last edited by DDEWILDE; April 18th, 2012 at 07:00 PM.. Reason: Corrections
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing combobox based on selection iamme Visual Basic 2005 Basics 0 January 31st, 2007 09:04 PM
How to catch mouse selection on a combobox? jeffcflam Pro VB 6 0 May 24th, 2006 07:22 AM
ComboBox Selection Question vtonny1 Access 2 February 11th, 2006 01:14 PM
using triggers for triggering email event sopranos138 Classic ASP Basics 0 December 25th, 2004 01:30 PM
USER SELECTION FROM A COMBOBOX AxeSess Access VBA 3 February 15th, 2004 06:48 AM



All times are GMT -4. The time now is 02:02 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.