' Option Buttons with the Data Control
'
' The Option Buttons that ship with VB are not dataaware and can
' not be linked to the Data Control. This program shows how to
' use Option Buttons in conjunction with the Data Control.
'
' Jon Vote, Idioma Software Inc.
'
' www.idioma-software.com
'
' Note - This program requires the Skycoder.mdb database.
' It can be created using Access or the Visual Data Manager that
' comes with Visual Basic based on the field definitions below
' or downloaded from the following location:
'
' www.skycoder.com/Submissions/Skycoder.mdb
'
' The database used in this project is defined as follows:
'
' Name: Skycoder.mdb
' Location: In the same path as the program.
' Table: Skycoders
' FIelds:
' Name Type Comments
' -----------------------------
' UniqueKey Long Auto Increment, Indexed Primary Unique
' FirstName Text(50)
' LastName Text(50)
' Sex Integer
'
'
' 1) Create or download the database described above and
' place it in the same path as where this program will be created.
' 2) Create a new project. Form1 will be created by default.
' 3) Add a Data control to the bottom of the Form.
' 4) Add two Label, Textbox pairs to the Form such that each
' Label has a Textbox to its right.
' 5) Add an Option Button to the Form.
' 6) Click on the Option Button and select Edit|Copy from the Menu.
' 7) Click on the Form and select Edit|Paste from the Menu.
' 8) Answer 'Yes' when asked if you want to create a Control Array.
' 9) Paste another Option Button to the Form.
'10) Add three Command Buttons to the buttom of the Form.
'11) Set the properties of the Data Control and the Textboxes as follows:
'
' Data1
' DatabaseName (Navigate to the Skycoder.mdb database from step 1)
' RecordSource Skycoders
'
' Text1
' DataSource Data1
' DataField FirstName
'
' Text2
' DataSource Data1
' DataField LastName
'
'
' 5) Paste the following code into the declarations section of Form1.
'
'
'
' --- Begin code for Form1
'
' Option Buttons with the Data Control
'
' The stock option buttons that ship with Visual Basic
' are not data aware. Here's how to use them in
' conjunction with other controls that are
' data aware
'
' Jon Vote, Idioma Software Inc.
'
' 02/2002
'
Option Explicit
Private m_bIsDirty As Boolean
Private m_bUpdateInProcess As Boolean
Private m_bCancelUnload As Boolean
'Add
Private Sub Command1_Click()
On Error GoTo err_Command1_Click
If Data1.EditMode <> dbEditAdd Then
Data1.Recordset.AddNew
Call EnableIf(True)
Text1.SetFocus
End If
Exit Sub
err_Command1_Click:
MsgBox Err.Description
Data1.UpdateControls
Text1.SetFocus
End Sub
'Update
Private Sub Command2_Click()
On Error GoTo err_Command2_Click
'Make sure something in the database
If ScreenIsValid Then
m_bUpdateInProcess = True
If Data1.EditMode = dbEditAdd Then
Data1.UpdateRecord
Data1.Recordset.Bookmark = Data1.Recordset.LastModified
Else
Data1.UpdateRecord
End If
m_bUpdateInProcess = False
PutOptionValues
End If
Exit Sub
err_Command2_Click:
MsgBox Err.Description
End Sub
Private Sub Command3_Click()
On Error GoTo err_Command3_click
If Data1.EditMode <> dbEditAdd Then
With Data1.Recordset
If Not .EOF Then
If Not .BOF Then
If MsgBox _
("Delete current record - are you sure?", vbYesNo) = vbYes Then
m_bUpdateInProcess = True
Data1.Recordset.Delete
If EmptyRecordset Then
SetDefaults
Else
Data1.Recordset.MoveFirst
End If
Data1.Refresh
Data1.UpdateControls
m_bUpdateInProcess = False
PutOptionValues
End If
End If
End If
End With
End If
Exit Sub
err_Command3_click:
MsgBox Err.Description
End Sub
Private Sub Data1_Reposition()
'0 = dbEditNone No editing operation is in progress.
'1 = dbEditInProgress The Edit method has been invoked, and the current record is in the copy buffer.
'2 = dbEditAdd The AddNew method has been invoked, and the current record in the copy buffer is a new record that hasn't been saved in the database.
If Data1.EditMode = 0 Then
GetOptionButtonValues
End If
End Sub
Private Sub Data1_Validate(Action As Integer, Save As Integer)
' Action codes:
' vbDataActionCancel 0 Cancel the operation when the Sub exits
' vbDataActionMoveFirst 1 MoveFirst method
' vbDataActionMovePrevious 2 MovePrevious method
' vbDataActionMoveNext 3 MoveNext method
' vbDataActionMoveLast 4 MoveLast method
' vbDataActionAddNew 5 AddNew method
' vbDataActionUpdate 6 Update operation (not UpdateRecord)
' vbDataActionDelete 7 Delete method
' vbDataActionFind 8 Find method
' vbDataActionBookmark 9 The Bookmark property has been set
' vbDataActionClose 10 The Close method
' vbDataActionUnload 11 The form is being unloaded
'
' Prompt user if they are unloading form with unchanged data
If Action = vbDataActionUnload Then
If Save = -1 Or m_bIsDirty Then
Save = 0
If MsgBox("Cancel without updating changes?", vbOKCancel) <> vbOK Then
m_bCancelUnload = True
Exit Sub
End If
End If
Else
If Action = vbDataActionAddNew Then
SetDefaults
Else
PutOptionValues
End If
End If
End Sub
Private Sub Form_Load()
m_bCancelUnload = False
Me.Caption = "Option Buttons with the Data Control"
Label1.Caption = "First Name: "
Label2.Caption = "Last Name: "
Option1(0).Caption = "Male"
Option1(1).Caption = "Female"
Option1(2).Caption = "Not Spec."
Command1.Caption = "&Add"
Command2.Caption = "&Update"
Command3.Caption = "&Delete"
SetDefaults
'Always reset this at run time since you don't
'know where the program is running on the
'target machine
Data1.DatabaseName = App.Path & "\" & "Skycoder.mdb"
Data1.Caption = Data1.RecordSource
Call EnableIf(Not EmptyRecordset())
End Sub
Private Sub GetOptionButtonValues()
Dim bWeGottaErr As Boolean
On Error GoTo err_GetOptionButtonValues
'We are mapping an integer to 3 option buttons
'make sure no overflow
bWeGottaErr = False
If Not m_bUpdateInProcess Then
With Data1.Recordset
If Not EmptyRecordset Then
If Not Data1.Recordset.EOF Then
If IsNull(!Sex) Then
bWeGottaErr = True
ElseIf (!Sex < 0) Or (!Sex > Option1.Count - 1) Then
bWeGottaErr = True
Else
Option1(!Sex) = True
End If
End If
End If
End With
End If
m_bIsDirty = False
Exit Sub
err_GetOptionButtonValues:
MsgBox Err.Description
End Sub
Private Sub PutOptionValues()
Dim i As Integer
If Not m_bUpdateInProcess Then
If Not EmptyRecordset() Then
With Data1.Recordset
If Not .EOF Then
.Edit
For i = 0 To Option1.Count - 1
If Option1(i).Value Then
!Sex = i
Exit For
End If
Next i
.Update
End If
End With
Data1.Recordset.Bookmark = Data1.Recordset.LastModified
m_bIsDirty = False
End If
End If
End Sub
Private Sub SetDefaults()
EnableIf False
Option1(0).Value = True
DoEvents
Text1.Text = ""
Text2.Text = ""
m_bIsDirty = False
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cancel = m_bCancelUnload
m_bCancelUnload = False
End Sub
Private Sub Option1_Click(Index As Integer)
'Modified
m_bIsDirty = True
End Sub
'Message to user and FALSE if an error
Private Function ScreenIsValid() As Boolean
ScreenIsValid = True
Exit Function
'Let's make sure something was filled in for the name
Text1.Text = Trim$(Text1.Text)
Text2.Text = Trim$(Text2.Text)
If Text1.Text & Text2.Text = "" Then
MsgBox "Please Enter a Name"
Text1.SetFocus
ScreenIsValid = False
End If
End Function
Private Sub EnableIf(ByVal bEnable As Boolean)
Dim i As Integer
Command2.Enabled = bEnable
Command3.Enabled = bEnable
Text1.Enabled = bEnable
Text2.Enabled = bEnable
For i = 0 To Option1.Count - 1
Option1(i).Enabled = bEnable
Next i
End Sub
'Returns TRUE if recordset is empty
Private Function EmptyRecordset()
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(Data1.DatabaseName)
Set rs = db.OpenRecordset(Data1.RecordSource)
EmptyRecordset = (rs.BOF And rs.EOF)
Set rs = Nothing
Set db = Nothing
End Function
' --- End code for Form1 ---