When the User Interface controls are not bound to a Data Control, all of the responsibility for display, edit and update of the data is left to the application. Screen logic, Button Logic and additional edit checks are detailed below.
Here is the basic screen logic. One subroutine moves information to the screen from the database, and the other moves information to the database from the screen. A function was written to help handle the situation when a NULL string might be loaded into a TextBox. The blnUpdating indicator is set to True to prevent the edit logic from firing when the TextBoxes are not being changed by the user.
Private Sub ScreenToDB()
dtaAddress.Recordset.Fields("Name").Value = txtName.Text
dtaAddress.Recordset.Fields("Address").Value =
txtAddress.Text
dtaAddress.Recordset.Fields("City").Value = txtCity.Text
dtaAddress.Recordset.Fields("State").Value = cboState.Text
dtaAddress.Recordset.Fields("Zip").Value = txtZip.Text
dtaAddress.Recordset.Fields("Phone").Value = txtPhone.Text
dtaAddress.Recordset.Fields("EMail").Value = txtEMail.Text
dtaAddress.Recordset.Fields("Notes").Value = txtNotes.Text
End Sub
Private Sub DBToScreen()
blnUpdating = True
txtName.Text = GetField("Name")
txtAddress.Text = GetField("Address")
txtCity.Text = GetField("City")
cboState.Text = GetField("State")
txtZip.Text = GetField("Zip")
txtPhone.Text = GetField("Phone")
txtEMail.Text = GetField("EMail")
txtNotes.Text = GetField("Notes")
blnUpdating = False
NoChange
End Sub
Private Function GetField(strField As String) As String
Dim strValue As String
If IsNull(dtaAddress.Recordset.Fields(strField).Value) Then
strValue = " "
Else
strValue =
dtaAddress.Recordset.Fields(strField).Value
End If
GetField = strValue
End Function
A couple of functions that help with the editing are detailed. The first function will examine every TextBox and make sure that the value is not blank. If it is, the cursor is positioned at that control. This is all done without any specific reference to the control.
Private Function ErrorsDetected() As Boolean
Dim blnError As Boolean
blnError = False
Dim c As Control
For Each c In Me.Controls
If TypeOf c Is TextBox Then
If c.Text =
Space(Len(c.Text)) Then
blnError = True
SetCursor c
Beep
Exit For
End If
End If
Next c
ErrorsDetected = blnError
End Function
Private Function SetCursor(c As Control) As Integer
c.SelStart = 0
c.SelLength = Len(c.Text)
c.SetFocus
End Function
The Reposition and Validate Events are fired as a result of a move from one row to another. The Move methods along with the Bookmark method will first fire Validate, and if the edit checks are met, the Reposition will be fired.
Private Sub dtaAddress_Reposition()
' The bookmark on a cancel causes a reposition
If blnAddMode Then
Exit Sub
End If
If blnUpdateMode Then
Exit Sub
End If
If dtaAddress.Recordset.EOF Then
Exit Sub
End If
If dtaAddress.Recordset.BOF Then
Exit Sub
End If
DBToScreen
End Sub
Private Sub dtaAddress_Validate(Action As Integer, Save As Integer)
blnError = False
If DataChanged Then
Else
Exit Sub
End If
If ErrorsDetected Then
If vbDataActionUnload Then
MsgBox "You must
correct the errors or cancel before exiting", vbInformation, _
"Errors Detected"
End If
Action = vbDataActionCancel
blnError = True
End If
End Sub
The logic that remains is the Button Control logic and the steps specific to the Add - Update / Cancel sequence that is similar to the Edit - Update / Cancel sequence. The Button Control sets the Command Buttons based on any change to data in a TextBox or to other Command Button sequences (Add or Delete requests). Each of these events will set the mode and call the Button logic.
A Bookmark must be saved whenever a change to the user interface is detected, or when an Add is selected. The saved location is restored as part of a Cancel request, either from the Add or the Cancel sequence.
Here is the code related to the Add sequence:
Private Sub cmdAdd_Click()
vBookmark = dtaAddress.Recordset.Bookmark
' Save the location in
case of Cancel
blnAddMode = True
blnUpdating = True
txtName.Text = ""
' Just Blank the screen as there is no
txtAddress.Text = ""
' Automatic update as with Bound Controls
txtCity.Text = ""
cboState.Text = ""
txtZip.Text = ""
txtPhone.Text = ""
txtEMail.Text = ""
txtNotes.Text = ""
blnUpdating = False
cmdAddUpdate.Enabled = SetButtons
AddButtons
End Sub
Private Sub cmdAddCancel_Click()
blnAddMode = False
NoChange
dtaAddress.Recordset.Bookmark = vBookmark
' Triggers Reposition
BrowseButtons
End Sub
Private Sub cmdAddUpdate_Click()
dtaAddress.Recordset.AddNew
' NOW add the row
If blnError Then
Exit Sub
End If
ScreenToDB
' Build the Row
dtaAddress.Recordset.Update
' And Update the Row
blnAddMode = False
NoChange
dtaAddress.Recordset.MoveLast
' Position to Added row
BrowseButtons
End Sub
The edit logic is similar. The application is put in Edit Mode whenever a change is made to a TextBox Control. Note that on an Update, the Validation code is invoked, and if an error is detected, the Update operation is Cancelled and a flag set indicating the error.
Private Sub cmdUpdateCancel_Click()
blnUpdateMode = False
NoChange
dtaAddress.Recordset.Bookmark = vBookmark
BrowseButtons
End Sub
Private Sub cmdUpdateUpdate_Click()
dtaAddress.Recordset.Edit
ScreenToDB
dtaAddress.Recordset.Update
' An error in Update (via Validation)
' Cancels the Update
If blnError Then
MsgBox "You must correct the errors",
vbInformation, "Errors Detected"
Exit Sub
End If
blnUpdateMode = False
NoChange
BrowseButtons
End Sub
The specifics of the Button control and Mode settings are demonstrated in the completed example.
Download the Completed Example