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 frmAssignReadin g 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...

[CODE=VB.NET]Private Sub Calendar_Click( )
'transfer the date chosen in the calendar to cmbDate
txtDate.SetFocu s
txtDate.Text = Calendar.Value
End Sub[/CODE]

[CODE=VB.NET]Private Sub cmdCancel_Click ()
'Cancel proposed changes
Me.Undo
End Sub[/CODE]

[CODE=VB.NET]Private Sub cmdSaveClose_Cl ick()
'************** *************** *************** *************** **'
'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.SetFo cus
Verses = txtVerses.Text

cmbTime.SetFocu s
Time = cmbTime.Text

cmbMember.SetFo cus
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(M e.cmbTime.Value , Me.txtDate.Valu e) Then
MsgBox "This Reading has already been assigned."
Exit Sub
End If

'Save the Record
DoCmd.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'clear the values for new entry
txtVerses.SetFo cus
txtVerses.Text = ""

cmbTime.SetFocu s
cmbTime.Text = ""

cmbMember.SetFo cus
cmbMember.Text = ""

txtVerses.SetFo cus

Exit Sub

End Sub

Public Function ReadingExists(T imeID 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.Time ID)=" & TimeID & ") AND ((tblRead.ReadD ate)='" & ReadDate & "'));"
Set record = dbs_curr.OpenRe cordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)

If Not record.EOF Then
ReadingExists = True
Else
ReadingExists = False
End If

End Function[/CODE]

[CODE=VB.NET]Private Sub Form_Load()
Calendar.Value = Now()
End Sub[/CODE]

[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!SomeF ield) or len(trim(me!Som eField) = 0 then
me.undo
end if