VBA example - Microsoft Access: VBA Select Case


VBA Select Case

In this example, you will learn VBA Select Case Statement to display data in the text boxes automatically when you select the value in the combo box. The example demonstrates Subject form as the figure below:

 VBA example - Microsoft Access Select Case Statement Form VBA 

- The form has four text boxes(SubjectID, Description, Hour, Fee), and one combo box box(SubjectName).

- On the form, there are only two text boxes(SubjectID, Description) that need to enter the data. For SubjectName combo box, you just click the arrow down( VBA example - Microsoft Access Select Case Statement Arrow Down ) to select its item. The value will appear automatically in Hour and Fee text boxes. See the figure:

VBA example - Microsoft Access Select Case Statement View Form 



To have Subject form, create a form in Form Design as the figure:

VBA example - Microsoft Access Select Case Statement Design Form

-  Drag and drop four text boxes and one combo box on the form.

-  Set Name properties of the two text boxes and one combo box:

Text boxes:

 Name: txtHour

 Name: txtFee

Combo Box:

 Name: CboSubjectName

Note: You only need to set the Name properties of two text boxes and one combo box that are used in the VBA code.

After you designed the form already, apply the VBA code below:

Option Compare Database

Option Explicit

 

Private Sub Form_Load()

CboSubjectName.RowSourceType = "Value List"

CboSubjectName.RowSource = "Ms.Access I; Ms.Access II;HTML;" _

& "Java Programming Language;C Programming Language"

End Sub

 

Private Sub CboSubjectName_Change()

Select Case CboSubjectName

Case Is = "Ms.Access I"

txtHour = 20

txtFee = 30 & "$"

Case Is = "Ms.Access II"

txtHour = 25

txtFee = 35 & "$"

Case Is = "HTML"

txtHour = 20

txtFee = 30 & "$"

Case Is = "Java Programming Language"

txtHour = 30

txtFee = 80 & "$"

Case Else

txtHour = 30

txtFee = 50 & "$"

End Select

End Sub



Comments




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.