VBA example - Microsoft Access: Db Execute


Db Execute

In this example, you will learn VBA Select Case Statement and Database.Execute Method. Using Select Case Statement to populate data in the text boxes automatically when you select the value in the combo box. And using Database.Execute Method to Save, Update and Delete records in the table by executing an SQL statement. The example demonstrates the Subject form as the figure below:

 VBA example - Microsoft Access VBA Select Case Statement and Database.Execute Method Subject Form  

- When the form loaded, all data in text box and combo box are blank, Update and Delete command button are disable, and populate all data in the list box.

- If you click Save without entering the SubjectID, the message will appear as the figure:

VBA example - Microsoft Access VBA Select Case Statement And Database.Execute Method Message1 

 - If you click Save without selecting the SubjectName, the message will appear as the figure:

VBA example - Microsoft Access VBA Select Case Statement And Database.Execute Method Message1 

- For the Description text box is optional, you can left it blank.

- If you want to to update or delete the records in the table, double click any record in the list box; the record will display in the text boxes and combo box as the figure below:

VBA example - Microsoft Access VBA Select Case Statement and Database.Execute Method Subject View Form 

- After you update or delete the records if you want to add new record/records, click Clear command button.

- The form has four text boxes(SubjectID, Description, Hour, Fee), one combo box box(SubjectName), one list box(LstData), and four command buttons(Save, Update, Delete, Clear).

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

VBA example - Microsoft Access VBA Select Case Statement and Database.Execute Method Subject Design Form

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

-  Set Name properties of Text Boxes, Combo Box, and List Box:

Name: txtSubjectID

Name: CboSubjectName

Name: txtHour

Name: txtDescription

Name: txtFee

Name: LstData

-  Set Name and Caption properties of Command Buttons:

Name: CmdSave

Caption: Save

Name: CmdUpdate

Caption: Update

Name: CmdDelete

Caption: Delete

Name: CmdClear

Caption: Clear

 

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

Option Compare Database

Option Explicit

Dim Db As Database

 

Private Sub Form_Load()

Set Db = CurrentDb

Call ClearData

DisableCommandButton

'Populate subject names in combo box

CboSubjectName.RowSourceType = "Value List"

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

& "Java Programming Language;C Programming Language"

'Populate all records from TblSubject to list box

lstData.RowSourceType = "Table/Query"

lstData.ColumnHeads = True

lstData.ColumnCount = 5

lstData.RowSource = "Select * from TblSubject"

End Sub

 

Private Sub CboSubjectName_Change()

DisableTextbox 'Disable hour and fee textboxes

Select Case CboSubjectName 'Setting values to hour and fee textboxes

    Case Is = "Ms.Access I" 'based on selected subject name

       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

 

Private Sub cmdSave_Click()

'Required fields can't be blank

If txtSubjectID = "" Then

   MsgBox "Please enter SubjectID.", vbInformation, "Enter Data"

   txtSubjectID.SetFocus

Exit Sub

End If

 

If CboSubjectName = "" Then

   MsgBox "Please select the SubjectName in combox box.", vbInformation, "Choose SubjectName"

   CboSubjectName.SetFocus

   CboSubjectName.Dropdown

Exit Sub

End If

 

If ExistingField(txtSubjectID) = True Then

   MsgBox "SubjectID cannot duplicate.", vbInformation, "Try Other SubjectID"

   txtSubjectID.SetFocus

   txtSubjectID.SelLength = Len(txtSubjectID)

Exit Sub

End If

'Save record

Db.Execute "INSERT INTO TblSubject VALUES('" & txtSubjectID & "','" & CboSubjectName & "'," _

& "'" & txtHour & "','" & txtDescription & "'," & txtFee & ")"

lstData.Requery

End Sub

 

Private Sub CmdUpdate_Click()

'Update record

Db.Execute " UPDATE TblSubject SET SubjectName='" & CboSubjectName & "'," _

& "Hour='" & txtHour & "',Description='" & txtDescription & "'," _

& "Fee=" & txtFee & " WHERE SubjectID=" _

& "'" & lstData.Column(0, lstData.ListIndex + 1) & "'"

lstData.Requery

End Sub

 

Private Sub CmdDelete_Click()

'Delete record

Db.Execute " DELETE FROM TblSubject WHERE SubjectID='" & lstData.Column(0, lstData.ListIndex + 1) & "'"

lstData.Requery

End Sub

 

Private Sub cmdClear_Click()

'Clear controls

Call ClearData

txtSubjectID.Enabled = True

cmdSave.Enabled = True

DisableCommandButton

End Sub

 

Private Sub lstData_DblClick(Cancel As Integer)

'Display one record in the text boxes and combo box

'when the user double-clicks the record

txtSubjectID = lstData.Column(0, lstData.ListIndex + 1)

CboSubjectName = lstData.Column(1, lstData.ListIndex + 1)

txtHour = lstData.Column(2, lstData.ListIndex + 1)

txtDescription = lstData.Column(3, lstData.ListIndex + 1)

txtFee = lstData.Column(4, lstData.ListIndex + 1)

CmdUpdate.Enabled = True

CmdDelete.Enabled = True

DisableTextbox

txtSubjectID.Enabled = False

cmdSave.Enabled = False

End Sub

 

Sub DisableTextbox()

txtHour.Enabled = False

txtFee.Enabled = False

End Sub

 

Sub DisableCommandButton()

CmdUpdate.Enabled = False

CmdDelete.Enabled = False

End Sub

 

Sub ClearData()

txtSubjectID = ""

CboSubjectName = ""

txtDescription = ""

txtHour = ""

txtFee = ""

End Sub

 

Function ExistingField(SubjectID As String) As Boolean

'Check duplicate subjectid

Dim Result As Variant

Result = DLookup("SubjectID", "TblSubject", "SubjectID='" & SubjectID & "'")

If Result <> "" Then

ExistingField = True

End If

End Function



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.