Storing and retrieving dates with Mask Edit Box

[dlpastel]

I am trying to figure out the best way to store and retrieve dates. I keep getting errors when I run the following code.:

(For retrieving records from the database and putting them into the mask edit control)
Sub EditRecord(aForm As Form, aRecordset As ADODB.Recordset)
Dim c As Control
For Each c In aForm
If TypeOf c Is MaskEdBox Then
If IsNull(aRecordset.Fields(c.DataField).Value) = False Then
c.Text = aRecordset.Fields(c.DataField).Value
End If
End If
Next c
End Sub

(For saving records from the mask edit box to the database)

Sub SaveRecord(aForm As Form, aRecordset As ADODB.Recordset)
'Save a new record or update a old one.
Dim c As Control
For Each c In aForm

If TypeOf c Is MaskEdBox Then
aRecordset.Fields(c.DataField).Value = (c.Text)
End If

Next c

Set aRecordset.ActiveConnection = dbCTS

aRecordset.UpdateBatch 'adAffectAll


I keep getting "Invalid Property" error when I try to run the GetRecord sub routine. There also can be problems if there is no data either in the database or the mask edit box Or if the mask edit box has IncludePrompts set to yes.
In short, I am trying to figure out what would be the most standard way to enter, save and redisplay datees

Thanks,
Dan

 

[MarkSweetland]

The mask property has a delimiter (usually a "/" for a date) and you can't assign a value to the text property that does not conform. For the most part, I find it easier to do away with the mask until the maskeditbox has the focus which prevents empty or NULLs in the data from causing an error while populating:

Private Sub txtCompDate_GotFocus()
On Error Resume Next
    txtCompDate.Mask = "##/##/##" [green]' set mask for two digit year[/green]
    [green]' Select entire date when has focus [/green]
    txtCompDate.SelStart = 0
    txtCompDate.SelLength = Len(txtCompDate.Mask)
End Sub

Private Sub txtCompDate_LostFocus()
On Error Resume Next
    txtCompDate.Mask = "" [green]' clear the mask [/green]
    If txtCompDate.Text = "__/__/__" Then 
        txtCompDate.Text = ""  [green]' clear text if text=mask/prompt[/green]
    End If
End Sub

.. and then when poputating the text I format it accordingly:

[green]' format date regardless of locale setting[/green]
txtCompDate.Text = Format(rs("compDate"), "mm/dd/yy")

If the data contains a NULL or zero-length string, the Format function will return a zero-length string and since the mask property is not set, causes no error.



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[jebenson]

Could you post the GetRecord subroutine, and indicate which line of code is causing the error?

To check for no data in the recordset you can do this:

If Not aRecordset.EOF And Not aRecordset.BOF Then
(a recordset with no data will return True for both EOF and BOF)

or this:

If aRecordset.RecordCount > 0 Then


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[dlpastel]

Thanks for the help!

Dan