VBA example - Microsoft Access: if else display data in listbox


VBA if else display data in listbox

In this example, you will learn VBA If...Else...Statement with SELECT statement to show the data in the list box. To display the data in the list you need to enter the number of list column and SELECT statement. And, you need a form as the figure below:

 VBA example - Microsoft Access If Else Statement SQL Statement VBA 

- The form has two text boxes(Column Number and SQL statement), two command buttons(Execute and Clear) and one list box(List Show Data).

- When you click Execute SQL command button without entering the integer number in the text box "column number" and SELECT statement in the text box "SQL statement", the two messages will appear like the figure:

VBA example - Microsoft Access If Else Statement Message Box1 

VBA example - Microsoft Access If Else Statement Message Box2 

- When you want to clear data from Column Number and SQL Statement text boxes, click Clear command button.

- For example, you want to display three column including BooKID, Title, and ISBNAfter from TblBook. After you entered the number 3 in the column number text box and the SELECT statement "SELECT BookID, Title, ISBN FROM TblBook" in the SQL statement text box  and click Execute SQL command button, the form will display the data in the list as the figure:

VBA example - Microsoft Access If Else Statement SQL Statement VBA Form View Data 

To have an SQL Select form, create a form in Form Design as the figure:

VBA example - Microsoft Access If Else Statement SQL Design Form

-  Drag and drop two text boxes, two command buttons, and one list box control on the form.

-  Set Name and Caption properties of the two command button controls:

Command button1:

 Name: CmdExecuteSQL

 Caption: ExecuteSQL

Command button2:

 Name: CmdClear

 Caption: Clear

-  Set Name of the two text box controls:

Name: txtColumnNumber

Name: txtSQLStatement

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

Option Compare Database

Option Explicit

 

Private Sub Form_Load()

txtColumnNumber = ""

txtSQLStatment = ""

lstStuentData.RowSourceType = "Table/Query"

lstStuentData.ColumnHeads = True

End Sub

 

Private Sub CmdExecuteSQL_Click()

If txtColumnNumber = "" Then

MsgBox "Please type number into column number.", vbInformation

txtColumnNumber.SetFocus

Exit Sub

Else

    If IsNumeric(txtColumnNumber) = False Then

    MsgBox "You can type only number.", vbInformation

    txtColumnNumber.SetFocus

    txtColumnNumber.SelLength = Len(txtColumnNumber)

    Exit Sub

    End If

End If

 

If txtSQLStatment = "" Then

MsgBox "Please type SQL statement.", vbInformation

txtSQLStatment.SetFocus

Else

lstStuentData.ColumnCount = CLng(txtColumnNumber)

lstStuentData.RowSource = txtSQLStatment

End If

End Sub

 

Private Sub CmdClear_Click()

txtColumnNumber = ""

txtSQLStatment = ""

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.