Calendar tool creating random records

[jmarcrum]

Hey everyone!

I have a subform form with a textbox, 2 command buttons, and an ActiveX calendar. The form loads with Calendar.Value = Now(). The textbox is blank. The two buttons are Save, and Cancel.

Here's my problem, when I click a date on the calendar, it assigns the value to the textbox, which has a control source of ReadDate. If I click the close button on the main form and I look at my recordsource table, a record with the date that was last selected from the ActiveX calendar has been created...Why? I didn't tell it to save?

Thanks in advance for your help!!
Joseph

[JustJim]

Hey everyone!

I have a subform form with a textbox, 2 command buttons, and an ActiveX calendar. The form loads with Calendar.Value = Now(). The textbox is blank. The two buttons are Save, and Cancel.

Here's my problem, when I click a date on the calendar, it assigns the value to the textbox, which has a control source of ReadDate. If I click the close button on the main form and I look at my recordsource table, a record with the date that was last selected from the ActiveX calendar has been created...Why? I didn't tell it to save?

Thanks in advance for your help!!
Joseph

Hi,

Don't make me out to be Bill G or something, but that's not a bug, it's a feature!

Access "saves" on the fly. Whenever you close a form, or do anything that would require the underlying recordset to be modified, Access does it for you. There are several ways around this, but you have to know what you want to do with your data (or what you want your users to do with their data).

Please feel free to post further here with what you want to happen and why. We'll get you on the right path.

Jim

[jmarcrum]

Hi,

Don't make me out to be Bill G or something, but that's not a bug, it's a feature!

Access "saves" on the fly. Whenever you close a form, or do anything that would require the underlying recordset to be modified, Access does it for you. There are several ways around this, but you have to know what you want to do with your data (or what you want your users to do with their data).

Please feel free to post further here with what you want to happen and why. We'll get you on the right path.

Jim

Hey thanks for the reply Jim!

The two textboxes (txtVerses and txtDate) have control sources to a certain table. The user uses this subform to assign a reading to a certain member of the church on a certain day (the day is always a Sunday). But I give the user the opportunity to select which Sunday via the calendar tool. When they click a day on the calendar, i have code that makes that day appear in the txtDate textbox.

What I'm finding out is that the subform isn't error proof. If the user opens the Main form frmAssignDuties...the subform frmAssignReading is also on it down at the bottom. If the user wants to "have some fun" and click around like crazy on the calendar, just to see the date change, and then close the Main form without selecting AM or PM in the Time combo box or selecting a Member in the Member combo box, or typing in a set of assigned Bible verses to read (on that date, at that time, for that member) in the verses textbox...not even clicking SAVE...an empty record will be created and added to the table with just a date showing!

I don't want that.

Thanks again,
Joseph

[FishVal]

Hi, Joseph.

Please post code opening the form with calendar control and code handling Click event of your buttons. So far we know only that the buttons have captions Save and Cancel. Very helpful but not enough ;).

Kind regards,
Fish

[jmarcrum]

Hi, Joseph.

Please post code opening the form with calendar control and code handling Click event of your buttons. So far we know only that the buttons have captions Save and Cancel. Very helpful but not enough ;).

Kind regards,
Fish

Hey FishVal!

Thanks for your post!

Here's my code...

Private Sub Calendar_Click()
    'transfer the date chosen in the calendar to cmbDate
    txtDate.SetFocus
    txtDate.Text = Calendar.Value
End Sub

Private Sub cmdCancel_Click()
    'Cancel proposed changes
    Me.Undo
End Sub

Private Sub cmdSaveClose_Click()
'*************************************************************'
'Author: Joseph                               '
'Date: 3/2/2008                                               '
'Desc: Assign a Reading to a member                           '
'Modifications:                                               '
'Date       By Who      Desc                                  '
'                                                             '
'*************************************************************'
 
Dim Member As String
Dim Time As String
Dim Verses As String
Dim message As String
 
txtVerses.SetFocus
Verses = txtVerses.Text
 
cmbTime.SetFocus
Time = cmbTime.Text
 
cmbMember.SetFocus
Member = cmbMember.Text
 
If Verses = "" Then
    message = "Verses"
End If
 
If Member = "" Then
    If message = "" Then
        message = "Member"
    Else
        message = message & ", Member"
    End If
End If
 
If Time = "" Then
    If message = "" Then
        message = "Time"
    Else
        message = message & ", Time"
    End If
End If
 
'If message is not blank, tell the user to enter the fields in the message
If message <> "" Then
    message = "Please enter the following fields: " & message
    MsgBox message
    Exit Sub
End If
 
'Make sure the Reading has not already been assigned to the member
If ReadingExists(Me.cmbTime.Value, Me.txtDate.Value) Then
    MsgBox "This Reading has already been assigned."
    Exit Sub
End If
 
'Save the Record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
'clear the values for new entry
txtVerses.SetFocus
txtVerses.Text = ""
 
cmbTime.SetFocus
cmbTime.Text = ""
 
cmbMember.SetFocus
cmbMember.Text = ""
 
txtVerses.SetFocus
 
Exit Sub
 
End Sub
 
Public Function ReadingExists(TimeID As Integer, ReadDate As String) As Boolean
'*************************************************************'
'Author: Joseph                              '
'Date: 3/2/2008                                               '
'Desc: Returns True if Reading passed in exists in            '
'      tblRead; False otherwise                               '
'Modifications:                                               '
'Date       By Who      Desc                                  '
'                                                             '
'*************************************************************'
Dim strSQL As String
Dim dbs_curr As Database
Dim record As Recordset
 
Set dbs_curr = CurrentDb
 
strSQL = "SELECT tblRead.* FROM tblRead WHERE (((tblRead.TimeID)=" & TimeID & ") AND ((tblRead.ReadDate)='" & ReadDate & "'));"
Set record = dbs_curr.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)
 
If Not record.EOF Then
    ReadingExists = True
Else
    ReadingExists = False
End If
 
End Function

Private Sub Form_Load()
    Calendar.Value = Now()
End Sub

[FishVal]

Ok. I will look at your code as soon as I get home.

Just a quick question. Why did you place calendar control to a subform on the main form? Is it less suitable to place it on the main form as soon as it should be linked to the main form recordsource?

Additionally, I would discourage you from assigning default value by modifying field on form load - this immediately creates a new record, assigning default value for table field or form control is much more suitable.

One more point - if you use Now() function you should be aware that it returns time portion as well as date. If the values are expected to treat as dates only you'd better use Date() function to prevent future troubles with dates comparing
e.g.
#1/1/1#=#1/1/1# returns False just because
the first is #1/1/1 12:00:00#
and the second is #1/1/1 12:00:01#

Regards,
Fish

[Denburt]

It sounds like you need some validation checks put in place either in the form close event or in the table itself so if a field in the table is mandatory then you can't create the record unless that field is filled in.
If you use the on close event and not a table restriction the if the validation fails you can use something like the following.

if isnull(me!SomeField) or len(trim(me!SomeField) = 0 then
me.undo
end if