Create a Database

using dbText (bound) and
dbDate (unbound) Field Types
(with MaskedEditBox)

by Rick Meyer

1. Start a new standard exe.
2. In Project->Components add Microsoft Masked Edit Control
3. On Form1 put a Data Control named Data1.
4. On Form1 put a Textbox named Text1.
    (Set the Text1 DataSource property to Data1)
5. On Form1 put an MaskEditBox named MaskEdBox1.
6. On Form1 put an MaskEditBox named MaskEdBox2.
    (Set the MaskEdBox2 DataSource property to Data1)

      Form1 should look like the picture above.

Now you are ready for the code. Select all of the following code by clicking on the word Option three times and copy it to the clipboard [Ctrl][Insert]. Then paste it into the code window of Form1 with [Shift][Insert].
When you run the program, you will enter a name, a date, and a phone number. Then click the right arrow on the Data Control to enter a new record. Then to see all the records you have entered, click the left arrow on the Data Control. 
Option Explicit

Private Sub Form_Load()
    Dim dbsNew As Database
    Dim tdfNew As TableDef
    Dim recNew As Recordset
    Dim dbName As String
    dbName = "c:\TxtDB.mdb"
    
    If Dir(dbName) = "" Then
        'Create the database
        Set dbsNew = CreateDatabase(dbName, dbLangGeneral)
        
        'Define the table
        Set tdfNew = dbsNew.CreateTableDef("Text")
        'Define the table's fields
        With tdfNew
            .Fields.Append .CreateField("Name", dbText)
            'Note this field is not a text field
            .Fields.Append .CreateField("Date", dbDate)
            .Fields.Append .CreateField("Phone", dbText)
        End With
        'Add the table to the database
        dbsNew.TableDefs.Append tdfNew
        
        'Place a first blank record in the table
        Set recNew = dbsNew.OpenRecordset("Text")
        With recNew
            .AddNew
            .Update
            .Close
        End With
        
        'Now close the database.
        dbsNew.Close
    End If
    
    Data1.DatabaseName = dbName  'Open the database
    Data1.RecordSource = "Text"  'Table name
    Data1.EOFAction = 2          'New record at EOF
    Text1.DataField = "Name"     'Set Text1 bound field
    MaskEdBox1.Format = "mm/dd/yy"
    MaskEdBox1.Mask = "##/##/##"
    MaskEdBox1.MaxLength = 8
    MaskEdBox2.DataField = "Phone"     'Set bound field
    MaskEdBox2.Mask = "(###) ###-####"
    MaskEdBox2.MaxLength = 14
End Sub

'MaskEdBox1 is not bound, so we must write the field
' if Data1 was clicked while on the MaskEdBox1 field
' since MaskEdBox1_LostFocus is not fired
Private Sub Data1_Validate(Action%, Save%)
    If Action < 5 Then
        If IsDate(MaskEdBox1.Text) Then
            With Data1.Recordset
                .Edit
                !Date = CDate(MaskEdBox1.Text)
            End With
        End If
    End If
End Sub

Private Sub Data1_Reposition()
    Static getout As Boolean
    Static flag As Boolean
        
    'If the data control is clicked past the last record
    ' then prepare for 2 events here (MoveNext & Addnew)
    ' Flag the first and blank fields on the second
    If getout Then
    ElseIf flag Then
        flag = False
        MaskSet MaskEdBox1, ""
        'Although MaskEdBox2 is databound
        ' special code is needed to blank
        ' it out for a new record
        MaskSet MaskEdBox2, ""
        Text1.SetFocus

        'Following is a crazy unexplained error fix
        ' If run without this, then clicking Data1
        ' past the last record will addnew and then
        ' position past EOF - causing an error
        ' condition of no current record
        getout = True ' needed since MoveLast
                      ' will fire Reposition again
        Data1.Recordset.MoveLast
        getout = False
    ElseIf Data1.Recordset.EOF Then
        flag = True
    
    'Otherwise if not EOF then show the date in
    ' MaskEdBox1 since the control is not databound
    ElseIf IsDate(Data1.Recordset!Date) Then
        MaskSet MaskEdBox1, _
            Format(Data1.Recordset!Date, "mm/dd/yy")
    Else
        MaskSet MaskEdBox1, ""
    End If
End Sub

'MaskEdBox1 is not databound so we must provide for
' updating the database separately for this control
Private Sub MaskEdBox1_LostFocus()
    On Error GoTo me1
    If IsDate(MaskEdBox1.Text) Then
        With Data1.Recordset
            .Edit
            'Note CDate() = this is not a text field
            !Date = CDate(MaskEdBox1.Text)
            .Update
        End With
    Else
        MaskSet MaskEdBox1, ""
    End If
    GoTo me2
me1: Resume me2
me2: On Error GoTo 0
End Sub

'Note to blank a MaskEditBox we must first blank
' the mask and then restore it
Private Sub MaskSet(b As MaskEdBox, d As String)
    Dim m$
    m = b.Mask
    b.Mask = ""
    b.Text = d
    b.Mask = m
End Sub
Examine the Form_Load sub to see how creating the database is actually accomplished.

The If Dir(dbName) statement determines if the database file already exists so it isn't recreated. Then it goes through the basic three step process of creating the database, creating a table, and finally creating fields in the table.

Note that the second data field is not a text field. It is a date field. All the bound data entry controls use text fields, so this demo requires special code to ensure that the unbound MaskedEditBox entries will be added to the database.