How to INSERT UPDATE and DELETE Records From a Table (Executing SQL Statements in Access VBA Code) Part 2 - Adding VBA Code Part 2 VBA Code: Option Compare Database Option Explicit Private Sub cboAccSQL_AfterUpdate() Call EnableTextboxes cmdExecute.Enabled = True cmdClear.Enabled = True Me.Refresh Me.Requery ---DoCmd.Requery Dim strSQL As String strSQL = SELECT * FROM Explanations & _ WHERE [SQL Statement]= & Me.cboAccSQL.Value & ; Me.RecordSource = strSQL Forms![Command Form]!txtExplanation.ControlSource = Explanation If cboAccSQL = INSERT Then cmdExecute.Caption = SQL INSERT chkDelete.Value = 0 chkDelete.Enabled = False txtID.Value = (Auto Number) txtID2.Visible = False txtID.Enabled = False txtProduct.Enabled = True txtQuantity.Enabled = True txtProduct.SetFocus Label1.Visible = False Label2.Visible = False ElseIf cboAccSQL = DELETE Then cmdExecute.Caption = SQL DELETE chkDelete.Enabled = True chkDelete.Value = 0 txtID.Enabled = True txtID.SetFocus txtProduct.Value = Disabled txtQuantity.Value = Disabled txtProduct.Enabled = False txtQuantity.Enabled = False Else cmdExecute.Caption = SQL UPDATE chkDelete.Enabled = False chkDelete.Value = 0 txtID.Enabled = True txtID2.Visible = False txtProduct.Enabled = True txtQuantity.Enabled = True txtID.SetFocus Label1.Visible = False Label2.Visible = False End If End Sub Private Sub chkDelete_AfterUpdate() txtID.SetFocus End Sub Private Sub chkDelete_Click() If Me.chkDelete.Value = -1 Then Checked Label1.Visible = True Label2.Visible = True txtID2.Visible = True Else Label1.Visible = False Label2.Visible = False txtID2.Visible = False End If End Sub Private Sub cmdClear_Click() If IsNull(cboAccSQL) Or Len(cboAccSQL & vbNullString) = 0 Then cboAccSQL.SetFocus Exit Sub Else Call Form_Load Call EnableTextboxes Me.cboAccSQL.Value = cboAccSQL.SetFocus Label1.Visible = False Label2.Visible = False txtID2.Visible = False Me.Refresh Me.Requery ---DoCmd.Requery End If End Sub Private Sub cmdExecute_Click() On Error GoTo ExecuteErr If IsNull(cboAccSQL) Or Len(cboAccSQL & vbNullString) = 0 Then MsgBox No SQL Statement selected, please select from the list, vbInformation, Select SQL Command [Combo Box] Call DisableTextBoxes cboAccSQL.SetFocus cmdExecute.Enabled = False cmdClear.Enabled = False Exit Sub Else Dim strSQL As String strSQL = cboAccSQL Select Case strSQL Case Is = INSERT Call sqlInsert Case Is = UPDATE Call sqlUpdate Case Is = DELETE Call sqlDelete End Select End If ErrorExit: Exit Sub ExecuteErr: If Err.Number 0 Then MsgBox Error Number : & Err.Number & vbCrLf & vbCrLf & _ Description : & Err.Description, vbCritical, Execute SQL Statement Error! Resume ErrorExit End If End Sub Private Sub Form_Load() Me.RecordSource = Forms![Command Form]!txtExplanation.ControlSource = chkDelete.Value = 0 chkDelete.Enabled = False cmdExecute.Caption = Execute Me! ---Sorry sir! End Sub Private Sub DisableTextBoxes() Dim con As Control Dim text As TextBox For Each con In Me.Controls If TypeOf con Is TextBox Then Set text = con If Left(text.Name, 4) txtE Then With text .Value = .Enabled = False End With End If End If Next con End Sub Private Sub EnableTextboxes() Dim con As Control Dim text As TextBox For Each con In Me.Controls If TypeOf con Is TextBox Then Set text = con If Left(text.Name, 4) txtE Then With text .Enabled = True .Value = End With End If End If Next con End Sub
Posted on: Sat, 18 Oct 2014 05:08:52 +0000
Trending Topics
Recently Viewed Topics
© 2015