Okay, so now you know that the problem is with recognising matching the
dates.
Skip the code: it will be best to get it working in the query.
* You have verified that the field in the table is a Date/Time type, so it
can't be the fields.
* You have verified that the Format property of the text boxes on the form
is General Date, so it can't be the text boxes.
* You have entered the parameters into the query, so it can't be in the
query.
I don't see where it's failing.
Post the updated query statement.
Normally I would not send such a large post, but at this point I do
not know what may be relevant to the problem. I am quite puzzled at
this problem. As this is a work in progress I still have some VBA
error handling, ect., to do. I am self taught, and started learning
to use Access, VBA, and SQL less than a year ago. So if you see some
areas that need attention I would welcome your advice.
This may or may not be helpful. I can select a date that has already
been stored in the table and my update query works! But if I try to
add a record with a date that is not anywhere in the table the query
fails.
___________________________________
Append Query QUSSAttendRec:
PARAMETERS [Forms].[frmSSAttendEntry].[cboSSClassID] Long, [Forms]!
[frmSSAttendEntry]![txtSSClassDate] DateTime;
INSERT INTO tblSSAttendance ( SSClassID, SSClassDate, SSMemberID,
MemberID, ContactID )
SELECT tblSSAttendance.SSClassID, tblSSAttendance.SSClassDate,
tblSSAttendance.SSMemberID, tblSSAttendance.MemberID,
tblSSAttendance.ContactID
FROM tblSSClasses INNER JOIN tblSSAttendance ON tblSSClasses.SSClassID
= tblSSAttendance.SSClassID
WHERE (((tblSSAttendance.SSClassID)=[Forms].[frmSSAttendEntry].
[cboSSClassID]) AND ((tblSSAttendance.SSClassDate)=[Forms]!
[frmSSAttendEntry]![txtSSClassDate]));
_________________________________________________
Queries used as RecordSource and Filters:
This is the RecordSource I use to show all of the data in the table
and to fill some text boxes. Everyone's names and personal
information is in one of two tables: tblMembers or tblContacts.
tblSSClasses has the class ID, Class Name, Room No. and Class Worker's
ID numbers. tblSSMembers has the Sunday School Members ID number,
Class ID number, MemberID, and ContactID, but not the names. (I know
the MemberID & ContactID in that table is redundant information, but
this makes getting their names on the form much easier for me.)
QSSSfrmAttendEntry:
SELECT tblSSAttendance.SSAttendRecID, tblSSAttendance.SSClassDate,
tblSSAttendance.SSClassID, tblSSClasses.SSClassName,
tblSSAttendance.SSMemberID, tblSSAttendance.MemberID,
IIf(IsNull(tblMembers.LastName),tblMembers.LastName,tblMembers.LastName
& ", " & tblMembers.FirstName & " " & tblMembers.Suffix) AS
MemberName, tblSSAttendance.ContactID,
IIf(IsNull(tblContacts.LastName),tblContacts.LastName,tblContacts.LastName
& ", " & tblContacts.FirstName & " " & tblContacts.Suffix) AS
ContactName, tblSSAttendance.Attendance
FROM tblSSClasses RIGHT JOIN ((tblSSAttendance LEFT JOIN tblContacts
ON tblSSAttendance.ContactID = tblContacts.ContactID) LEFT JOIN
tblMembers ON tblSSAttendance.MemberID = tblMembers.MemberID) ON
tblSSClasses.SSClassID = tblSSAttendance.SSClassID
ORDER BY tblSSAttendance.SSClassDate DESC;
___________________________
QSSSfrmAttendEntryFilter:
SELECT tblSSAttendance.SSAttendRecID, tblSSAttendance.SSClassDate,
tblSSAttendance.SSClassID, tblSSClasses.SSClassName,
tblSSAttendance.SSMemberID, tblSSAttendance.MemberID,
IIf(IsNull(tblMembers.LastName),tblMembers.LastName,tblMembers.LastName
& ", " & tblMembers.FirstName & " " & tblMembers.Suffix) AS
MemberName, tblSSAttendance.ContactID,
IIf(IsNull(tblContacts.LastName),tblContacts.LastName,tblContacts.LastName
& ", " & tblContacts.FirstName & " " & tblContacts.Suffix) AS
ContactName, tblSSAttendance.Attendance
FROM tblSSClasses RIGHT JOIN ((tblSSAttendance LEFT JOIN tblContacts
ON tblSSAttendance.ContactID = tblContacts.ContactID) LEFT JOIN
tblMembers ON tblSSAttendance.MemberID = tblMembers.MemberID) ON
tblSSClasses.SSClassID = tblSSAttendance.SSClassID
WHERE (((tblSSAttendance.SSClassDate) Is Null) AND
((tblSSAttendance.SSClassID)=[Forms]![frmSSAttendEntry]!
[cboSSClassID]) AND ((tblSSAttendance.Attendance)=0));
_________________________________________________
tblSSAttendance Properites
SSClassDate Field:
Data Type Date/Time
General
Format General Date
Input Mask
Caption
Default Value
Validation Rule
Validation Text
Required No
Indexed No
IME Mode No Control
IME Sentence Mode None
Smart Tags
SSClassID Field:
Data Type Number
Field Size Long Integer
Format
Decimal Places Auto
Input Mask
Caption
Default Value
Validation Rule
Validation Text
Required No
Indexed Yes (Duplicates OK)
Smart Tags
tblSSAttendance Relationships:
(I may have more of these than I need)
Relationship Table tblSSClasses
Relationship Field SSClassID
Relationship Foreign Table tblSSAttendance
Relationship Foreign Field SSClassID
Relationship Type One to Many
Relationship Join Type Inner Join
Enforce Referential Integrity False
Cascade Update False
Cascade Delete False
Relationship Table tblSSMembers
Relationship Field SSMemberID
Relationship Foreign Table tblSSAttendance
Relationship Foreign Field SSMemberID
Relationship Type One to Many
Relationship Join Type Inner Join
Enforce Referential Integrity False
Cascade Update False
Cascade Delete False
Relationship Table tblMembers
Relationship Field MemberID
Relationship Foreign Table tblSSAttendance
Relationship Foreign Field MemberID
Relationship Type One to Many
Relationship Join Type Inner Join
Enforce Referential Integrity False
Cascade Update False
Cascade Delete False
Relationship Table tblContacts
Relationship Field ContactID
Relationship Foreign Table tblSSAttendance
Relationship Foreign Field ContactID
Relationship Type One to Many
Relationship Join Type Inner Join
Enforce Referential Integrity False
Cascade Update False
Cascade Delete False
________________________________________________
frmSSAttendEntry Properties:
Form Tab:
Format
Caption . . . Sunday School Attendance Entry Form
Default View . . . . Continuous Forms
Allow Form View . . Yes
Allow Datasheet View . . Yes
Allow PivotTable View . . No
Allow PivotChart View . . No
Scroll Bars . . . . . . Both
Record Selectors . . . . . Yes
Navigation Buttons . . . . Yes
Dividing Lines . . . . Yes
Auto Resize . . . . . Yes
Auto Center . . . . Yes
Border Style . . . . Sizable
Control Box . . . . . Yes
Min Max Buttons . . . . . Both Enabled
Close Button . . . . Yes
Whats This Button . . . . No
Width . . . . . 7.7083"
Picture . . . . (none)
Picture Type . . . . Embedded
Picture Size Mode . . . . . Clip
Picture Alignment . . . . . Center
Picture Tiling . . . . No
Grid X . . . . . 24
Grid Y . . . . . 24
Layout for Print . . No
Subdatasheet Height . . 0"
Subdatasheet Expanded . . . . No
Palette Source . . . (Default)
Orientation . . . . . Left-to-Right
Moveable . . . . . . Yes
Form Data Tab:
Record Source . . . QSSSfrmAttendEntry
Filter . . . . .
Order By . . . tblSSAttendance.SSClassDate DESC
Allow Filters . . . . . Yes
Allow Edits . . . . . Yes
Allow Deletions . . Yes
Allow Additions . . Yes
Data Entry . . . . . No
Recordset Type . . Dynaset
Record Locks . . . . No Locks
Fetch Defaults . . . Yes
Only one Form Event Proceedure:
On Close
Form Other Tab:
Pop Up . . . . No
Modal . . . . . No
Cycle . . . . . All Records
Menu Bar . . . . . .
Toolbar . . . .
Shortcut Menu . . . Yes
Shortcut Menu Bar . . . .
Fast Laser Printing . . . . Yes
Help File . . .
Help Context Id . . 0
Tag . . . . . .
Has Module . . . . . Yes
Allow Design Changes . . All Views
____________________________________________
Text Box: txtSSClassDate Properties
txtSSClassDate Format Tab:
Format . . . . General Date
Decimal Places . . . Auto
Visible . . . . . Yes
Display When . . . Always
Scroll Bars . . . . . . None
Can Grow . . . . . . No
Can Shrink . . . . . No
Left . . . . . . 0.9583"
Top . . . . . . 0.125"
Width . . . . . 0.875"
Height . . . . 0.1667"
Back Style . . . . . . Normal
Back Color . . . . . . -2147483643
Special Effect . . . Sunken
Border Style . . . . Solid
Border Color . . . . 0
Border Width . . . . Hairline
Fore Color . . . . . . -2147483640
Font Name . . . . . Tahoma
Font Size . . . . . . 9
Font Weight . . . . Normal
Font talic . . . . . . No
Font Underline . . . No
Text Align . . . . . . General
Reading Order . . . Context
Keyboard Language . . . System
Scroll Bar Align . . . System
Numeral Shapes . . System
Left Margin . . . . . 0"
Top Margin . . . . . 0"
Right Margin . . . . 0"
Bottom Margin . . . 0"
Line Spacing . . . . 0"
Is Hyperlink . . . . . No
txtSSClassDate Data Tab:
Control Source . . .
Input Mask . . . . .
Default Value . . . .
Validation Rule . . .
Validation Text . . .
Enabled . . . . . Yes
Locked . . . . . . No
Filter Lookup . . . . Database Default
Smart Tags . . . . .
txtSSClassDate Event Tab:
--No Event Proceedures
txtSSClassDate Other Tab:
Name . . . . . txtSSClassDate
IME Hold . . . No
IME Mode . . . . . . No Control
IME Sentence Mode . . . None
Status Bar Text . .
Enter Key Behavior . . . . Default
Allow AutoCorrect . . . . No
Vertical . . . . No
Auto Tab . . . . . . No
Tab Stop . . . Yes
Tab Index . . . . . . 0
Shortcut Menu Bar . . . .
ControlTip Text . .
Help Context Id . . 0
Tag . . . . . .
___________________________
TextBox SSClassDate Properties:
(This is the field I am trying to fill)
SSClassDate Format Tab:
Format . . . . General Date
Decimal Places . . . Auto
Visible . . . . . Yes
Display When . . . Always
Scroll Bars . . . . . . None
Can Grow . . . . . . No
Can Shrink . . . . . No
Left . . . . . . 0.5417"
Top . . . . . . 0.0417"
Width . . . . . 0.875"
Height . . . . 0.1771"
Back Style . . . . . . Normal
Back Color . . . . . . -2147483643
Special Effect . . . Sunken
Border Style . . . . Solid
Border Color . . . . 0
Border Width . . . . Hairline
Fore Color . . . . . . -2147483640
Font Name . . . . . MS Sans Serif
Font Size . . . . . . 8
Font Weight . . . . . . Normal
Font Italic . . . . . . No
Font Underline . . . No
Text Align . . . . . . General
Reading Order . . . Context
Keyboard Language . . . System
Scroll Bar Align . . . System
Numeral Shapes . . System
Left Margin . . . . . 0"
Top Margin . . . . . 0"
Right Margin . . . . 0"
Bottom Margin . . . 0"
Line Spacing . . . . 0"
Is Hyperlink . . . . . No
SSClassDate Data Tab:
Control Source . . . SSClassDate
Input Mask . . . . .
Default Value . . . .
Validation Rule . . .
Validation Text . . .
Enabled . . . Yes
Locked . . . . No
Filter Lookup . . . . Database Default
Smart Tags . . . . .
SSClassDate Event Tab:
--No Event Proceedures
SSClassDate Other Tab:
Name . . . . . SSClassDate
IME Hold . . . No
IME Mode . . . . . . No Control
IME Sentence Mode . . . None
Status Bar Text . .
Enter Key Behavior . . . . Default
Allow AutoCorrect . . . . No
Vertical . . . . No
Auto Tab . . . . . . No
Tab Stop . . . Yes
Tab Index . . . . . . 1
Shortcut Menu Bar . . . .
ControlTip Text . .
Help Context Id . . 0
Tag . . . . . .
___________________________
Combo Box: cboSSClassID Properties
cboSSClassID Format Tab:
Format . . . . General Number
Decimal Places . . . Auto
Column Count . . . 2
Column Heads . . . Yes
Column Widths . . . 0.7", 1.5"
List Rows . . . . . . 20
List Width . . . . . . 2.5"
Visible . . . . . Yes
Display When . . . Always
Left . . . . . . 0.9583"
Top . . . . . . 0.375"
Width . . . . . 0.875"
Height . . . . 0.1667"
Back Style . . . . . . Normal
Back Color . . . . . . -2147483643
Special Effect . . . Sunken
Border Style . . . . Solid
Border Color . . . . 0
Border Width . . . . Hairline
Fore Color . . . . . . -2147483640
Font Name . . . . . MS Sans Serif
Font Size . . . . . . 8
Font Weight . . . . Normal
Font Italic . . . . . . No
Font Underline . . . No
Text Align . . . . . . General
Reading Order . . . Context
Keyboard Language . . . System
Scroll Bar Align . . . System
Numeral Shapes . . System
Is Hyperlink . . . . . No
Data Tab:
Control Source . . .
Input Mask . . . . .
Row Source Type . . . . . Table/Query
Row Source . . . . . QSSSClassID2
Bound Column . . . 1
Limit To List . . . . . Yes
Auto Expand . . . . Yes
Default Value . . . .
Validation Rule . . .
Validation Text . . .
Enabled . . . Yes
Locked . . . . No
Smart Tags . . . . .
Other Tab:
Name . . . . . cboSSClassID
Status Bar Text . .
IME Hold . . . No
IME Mode . . . . . . No Control
IME Sentence Mode . . . None
Allow AutoCorrect . . . . Yes
Tab Stop . . . Yes
Tab Index . . . . . . 2
Shortcut Menu Bar . . . .
ControlTip Text . .
Help Context Id . . 0
Tag . . . . . .
____________________________________________
frmSSAttendEntry MODULE:
Option Compare Database
Option Explicit
________________________________________________
Private Sub btnAddNewRecords_Click()
'DoCmd.SetWarnings False 'Access Warnings are now enabled
DoCmd.OpenQuery "QASSAttendRec"
'DoCmd.SetWarnings True 'Not needed if Access Warnings are not
disabled
End Sub
____________________________________________
Private Sub btnRemoveFilter_Click()
On Error GoTo Err_btnRemoveFilter_Click
Me.RecordSource = "QSSSfrmAttendEntry"
DoCmd.ApplyFilter "QSSSfrmAttendEntry", ""
Exit_btnRemoveFilter_Click:
Exit Sub
Err_btnRemoveFilter_Click:
MsgBox Err.Description
Resume Exit_btnRemoveFilter_Click
End Sub
_____________________________________
Private Sub btnSSClassDate_Click()
Call myDatePicker("frmSSAttendEntry", "txtSSClassDate")
End Sub
___________________________________________
Private Sub Form_Close()
Me.RecordSource = "QSSSfrmAttendEntry"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End Sub
_________________________________________
Private Sub btnFilterForm_Click()
Me.RecordSource = "QSSSfrmAttendEntryFilter"
DoCmd.ApplyFilter "QSSSfrmAttendEntryFilter"
End Sub
_________________________________________
MODULE Global Code:
(This enables the calendar --Calendar 10-- that I use to set the date
in txtSSClassDate)
Public Sub myDatePicker(sFormName As String, _
sFieldName As String)
DoCmd.OpenForm "frmCalPick"
With Forms!frmCalPick
.CallingForm = sFormName
.CallingField = sFieldName
If IsNull(Forms(sFormName). _
Form(sFieldName).Value) Then
!CalendarControl.Value = Date
Else
!CalendarControl.Value = _
Forms(sFormName).Form(sFieldName).Value
End If
End With
End Sub
Thanks for your help.
Hunter57