Append Query Date Criteria Problem.

[Hunter57]

I need some help with an append query. The date criteria is not
working in the append query, but it works fine when I change it to a
Select query. Also, I can remove the problem Column in the RAD, or
just remove the critera in the column and the append query works fine.

I am using a continuous form to enter Sunday School attendance. I
want my query to add all the information for a particular class on a
particular day and the user just has to enter the Attendance. My
query has two criteria, which are obtained from unbound controls on
the form: cboSSClassID, and txtSSClassDate (the one giving me the
problem). All of my table fields and form controls for this field are
set to use this input mask: 99/99/0000;0;_.

I use a command button to open the query with the following code:

Private Sub btnAddNewRecords_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "QASSAttendRec"
DoCmd.SetWarnings True
End Sub

Here is my Query SQL:
INSERT INTO tblSSAttendance ( SSClassDate, SSClassID, SSMemberID,
MemberID, ContactID )
SELECT tblSSAttendance.SSClassDate, tblSSAttendance.SSClassID,
tblSSAttendance.SSMemberID, tblSSAttendance.MemberID,
tblSSAttendance.ContactID
FROM tblSSClasses INNER JOIN tblSSAttendance ON tblSSClasses.SSClassID
= tblSSAttendance.SSClassID
WHERE (((tblSSAttendance.SSClassDate)=[Forms]![frmSSAttendEntry]!
[txtSSClassDate]) AND ((tblSSAttendance.SSClassID)=[Forms].
[frmSSAttendEntry].[cboSSClassID]));

I have two other command buttons which filter by query: one to show
the records just added, and another to show all data in the table.
They use the exact same date criteria and work well.

I am using MS Access 2003 in 2000 format with Windows XP.

I would appreciate your help.

Hunter57

 

[Allen Browne]

If the append query is misunderstanding the date, there are several levels
to check.

1. Open table tblSSAttendance in design view.
Verify that the SSClassDate field is of type Date/Time (not Text.)
Verify that SSClassID is a Number field, of size Long Integer.
The follow steps assume these settings.

2. Open form frmSSAttendEntry in design view.
If txtSSClassDate is unbound, set its Format property to:
General Date
so Access knows it is a date.
If cboSSClassID is unbound, set its Format property to:
General Number

3. Open the query in design view.
Choose Parameters on the Query menu.
Access opens the paramters dialog.
Enter 2 rows:
[Forms]![frmSSAttendEntry]![txtSSClassDate] Date
[Forms].[frmSSAttendEntry].[cboSSClassID] Long

The unbound text box should now be read as a date, and the query should now
perform a date match.

More information on getting Access to recognise dates:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I need some help with an append query. The date criteria is not
working in the append query, but it works fine when I change it to a
Select query. Also, I can remove the problem Column in the RAD, or
just remove the critera in the column and the append query works fine.

I am using a continuous form to enter Sunday School attendance. I
want my query to add all the information for a particular class on a
particular day and the user just has to enter the Attendance. My
query has two criteria, which are obtained from unbound controls on
the form: cboSSClassID, and txtSSClassDate (the one giving me the
problem). All of my table fields and form controls for this field are
set to use this input mask: 99/99/0000;0;_.

I use a command button to open the query with the following code:

Private Sub btnAddNewRecords_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "QASSAttendRec"
DoCmd.SetWarnings True
End Sub

Here is my Query SQL:
INSERT INTO tblSSAttendance ( SSClassDate, SSClassID, SSMemberID,
MemberID, ContactID )
SELECT tblSSAttendance.SSClassDate, tblSSAttendance.SSClassID,
tblSSAttendance.SSMemberID, tblSSAttendance.MemberID,
tblSSAttendance.ContactID
FROM tblSSClasses INNER JOIN tblSSAttendance ON tblSSClasses.SSClassID
= tblSSAttendance.SSClassID
WHERE (((tblSSAttendance.SSClassDate)=[Forms]![frmSSAttendEntry]!
[txtSSClassDate]) AND ((tblSSAttendance.SSClassID)=[Forms].
[frmSSAttendEntry].[cboSSClassID]));

I have two other command buttons which filter by query: one to show
the records just added, and another to show all data in the table.
They use the exact same date criteria and work well.

I am using MS Access 2003 in 2000 format with Windows XP.

I would appreciate your help.

Hunter57

 

[Hunter57]

If the append query is misunderstanding the date, there are several levels
to check.

1. Open table tblSSAttendance in design view.
Verify that the SSClassDate field is of type Date/Time (not Text.)
Verify that SSClassID is a Number field, of size Long Integer.
The follow steps assume these settings.

2. Open form frmSSAttendEntry in design view.
If txtSSClassDate is unbound, set its Format property to:
General Date
so Access knows it is a date.
If cboSSClassID is unbound, set its Format property to:
General Number

3. Open the query in design view.
Choose Parameters on the Query menu.
Access opens the paramters dialog.
Enter 2 rows:
[Forms]![frmSSAttendEntry]![txtSSClassDate] Date
[Forms].[frmSSAttendEntry].[cboSSClassID] Long

The unbound text box should now be read as a date, and the query should now
perform a date match.

More information on getting Access to recognise dates:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I need some help with an append query. The date criteria is not
working in the append query, but it works fine when I change it to a
Select query. Also, I can remove the problem Column in the RAD, or
just remove the critera in the column and the append query works fine.

I am using a continuous form to enter Sunday School attendance. I
want my query to add all the information for a particular class on a
particular day and the user just has to enter the Attendance. My
query has two criteria, which are obtained from unbound controls on
the form: cboSSClassID, and txtSSClassDate (the one giving me the
problem). All of my table fields and form controls for this field are
set to use this input mask: 99/99/0000;0;_.

I use a command button to open the query with the following code:

Private Sub btnAddNewRecords_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "QASSAttendRec"
DoCmd.SetWarnings True
End Sub

Here is my Query SQL:
INSERT INTO tblSSAttendance ( SSClassDate, SSClassID, SSMemberID,
MemberID, ContactID )
SELECT tblSSAttendance.SSClassDate, tblSSAttendance.SSClassID,
tblSSAttendance.SSMemberID, tblSSAttendance.MemberID,
tblSSAttendance.ContactID
FROM tblSSClasses INNER JOIN tblSSAttendance ON tblSSClasses.SSClassID
= tblSSAttendance.SSClassID
WHERE (((tblSSAttendance.SSClassDate)=[Forms]![frmSSAttendEntry]!
[txtSSClassDate]) AND ((tblSSAttendance.SSClassID)=[Forms].
[frmSSAttendEntry].[cboSSClassID]));

I have two other command buttons which filter by query: one to show
the records just added, and another to show all data in the table.
They use the exact same date criteria and work well.

I am using MS Access 2003 in 2000 format with Windows XP.

I would appreciate your help.

Hunter57- Hide quoted text -

- Show quoted text -

Hi Allen,

Thanks very much for your help. However, I tried it all and then some
and still could not append to my table. I have removed the date from
my query and I am planning to add the date using something like this:

Private Sub btnAddDate_Click()
On Error GoTo Err_btnAddDate_Click

Do While Not EOF
If (IsNull(Me.SSClassDate) And (Me.Attendance = 0)) Then
Me.SSClassDate.SetFocus
Me.SSClassDate = Me.txtSSClassDate
DoCmd.FindNext
Loop

Else: GoTo Exit_btnAddDate_Click
End If


Exit_btnAddDate_Click:
Exit Sub

Err_btnAddDate_Click:
MsgBox Err.Description
Resume Exit_btnAddDate_Click

End Sub

I have to work on my VBA so I can make the code work. But it is way
past my bedtime so I will leave it for another day.

With Gratitude,

Hunter57

 

[Allen Browne]

Temporarily remove the line that sets warnings to False.
Then read the dialog boxes.

Does it report an error? Or does it say it's appending 0 records?

If there is a message about violating rules or not being able to append,
this might help you to track down the issue:
Why can't I append some records?
at:
http://allenbrowne.com/casu-19.html

If it says it's appending 0 records, we need to work on why it isn't
selecting the right records.

Another way to get information about the error is to use the Execute method,
and read the error message:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

If the append query is misunderstanding the date, there are several
levels
to check.

1. Open table tblSSAttendance in design view.
Verify that the SSClassDate field is of type Date/Time (not Text.)
Verify that SSClassID is a Number field, of size Long Integer.
The follow steps assume these settings.

2. Open form frmSSAttendEntry in design view.
If txtSSClassDate is unbound, set its Format property to:
General Date
so Access knows it is a date.
If cboSSClassID is unbound, set its Format property to:
General Number

3. Open the query in design view.
Choose Parameters on the Query menu.
Access opens the paramters dialog.
Enter 2 rows:
[Forms]![frmSSAttendEntry]![txtSSClassDate] Date
[Forms].[frmSSAttendEntry].[cboSSClassID] Long

The unbound text box should now be read as a date, and the query should
now
perform a date match.

More information on getting Access to recognise dates:
http://allenbrowne.com/ser-36.html

I need some help with an append query. The date criteria is not
working in the append query, but it works fine when I change it to a
Select query. Also, I can remove the problem Column in the RAD, or
just remove the critera in the column and the append query works fine.

I am using a continuous form to enter Sunday School attendance. I
want my query to add all the information for a particular class on a
particular day and the user just has to enter the Attendance. My
query has two criteria, which are obtained from unbound controls on
the form: cboSSClassID, and txtSSClassDate (the one giving me the
problem). All of my table fields and form controls for this field are
set to use this input mask: 99/99/0000;0;_.

I use a command button to open the query with the following code:

Private Sub btnAddNewRecords_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "QASSAttendRec"
DoCmd.SetWarnings True
End Sub

Here is my Query SQL:
INSERT INTO tblSSAttendance ( SSClassDate, SSClassID, SSMemberID,
MemberID, ContactID )
SELECT tblSSAttendance.SSClassDate, tblSSAttendance.SSClassID,
tblSSAttendance.SSMemberID, tblSSAttendance.MemberID,
tblSSAttendance.ContactID
FROM tblSSClasses INNER JOIN tblSSAttendance ON tblSSClasses.SSClassID
= tblSSAttendance.SSClassID
WHERE (((tblSSAttendance.SSClassDate)=[Forms]![frmSSAttendEntry]!
[txtSSClassDate]) AND ((tblSSAttendance.SSClassID)=[Forms].
[frmSSAttendEntry].[cboSSClassID]));

I have two other command buttons which filter by query: one to show
the records just added, and another to show all data in the table.
They use the exact same date criteria and work well.

I am using MS Access 2003 in 2000 format with Windows XP.

I would appreciate your help.

Hunter57- Hide quoted text -

- Show quoted text -

Hi Allen,

Thanks very much for your help. However, I tried it all and then some
and still could not append to my table. I have removed the date from
my query and I am planning to add the date using something like this:

Private Sub btnAddDate_Click()
On Error GoTo Err_btnAddDate_Click

Do While Not EOF
If (IsNull(Me.SSClassDate) And (Me.Attendance = 0)) Then
Me.SSClassDate.SetFocus
Me.SSClassDate = Me.txtSSClassDate
DoCmd.FindNext
Loop

Else: GoTo Exit_btnAddDate_Click
End If


Exit_btnAddDate_Click:
Exit Sub

Err_btnAddDate_Click:
MsgBox Err.Description
Resume Exit_btnAddDate_Click

End Sub

I have to work on my VBA so I can make the code work. But it is way
past my bedtime so I will leave it for another day.

 

[Hunter57]

Temporarily remove the line that sets warnings to False.
Then read the dialog boxes.

Does it report an error? Or does it say it's appending 0 records?

If there is a message about violating rules or not being able to append,
this might help you to track down the issue:
Why can't I append some records?
at:
http://allenbrowne.com/casu-19.html

If it says it's appending 0 records, we need to work on why it isn't
selecting the right records.

Another way to get information about the error is to use the Execute method,
and read the error message:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

The message I am receiving is: "You are about to append 0 row(s)."

 

[Allen Browne]

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.

 

[Hunter57]

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

 

[Hunter57]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.







- Show quoted text -

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.

This info is too large to send in one post. Others will follow.
Normally I would not send such large posts, but at this point I do not
know what may be relevant to the 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.
___________________________________
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

Thanks,

Hunter57

 

[John W. Vinson]

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.

Well, that's the problem right there.

An update query *updates existing records*. That's all it does. It will not
and cannot create a new record.

John W. Vinson [MVP]

 

[Hunter57]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.







- Show quoted text -

I see that my large post finally got through after all. Thanks to
the guys & gals who make this form possible.

Correction: I wrote: (I know the MemberID & ContactID in that table
is redundant information, but this makes getting their names on the
form much easier for me.)
I shound have written that it was redundant in the tblAttendance
table.

Thanks again,

Hunter57

 

[Guest]

Well, that's the problem right there.

An update query *updates existing records*. That's all it does. It will not
and cannot create a new record.

John W. Vinson [MVP]

That is what is so puzzling. Access tells me I am using an Append query and
the SQL is "INSERT INTO" and not "UPDATE". Also, if I remove the SSClassDate
with the criteria in the Column from the RAD the query does Append new
records to the table. Thanks for your input.

BTW, please disregard my previous post in a new thread. I found a way to
access this thread again.

Thanks,

Hunter57

 

[Guest]

Correction: Here is the SQL for my filter Query 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)=[Forms]![frmSSAttendEntry]![txtSSClassDate])
AND ((tblSSAttendance.SSClassID)=[Forms]![frmSSAttendEntry]![cboSSClassID]));

 

[Allen Browne]

Hunter57, I don't have a clear understanding of what you are aiming to do
here.

John Vinson's response is the same as I would have replied to that part of
your thead, but apparently that's not what is going on either.

This is probably the kind of issue that you will need more professional help
with, so someone can examine your database, listen to what you are seeking
to achieve, and then help you find a way forward. It doesn't seem to be
something we can solve for you quickly in the newsgroups.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Correction: Here is the SQL for my filter Query 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)=[Forms]![frmSSAttendEntry]![txtSSClassDate])
AND
((tblSSAttendance.SSClassID)=[Forms]![frmSSAttendEntry]![cboSSClassID]));

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.

 

[Guest]

I do appreciate very much the time and effort you have put into this. You
have helped me greatly in that now I will not be so worried that I had made
some obvious dumb mistake with my DB.

Of course you were correct when you wrote that it would be much better to
use the update query if we could get it to work. All I know to do now is to
remove the part of the query that will not work, run the modified query, and
add the dates to the newly appended records with VBA. (I could not get an
Update query using the txtSSClassDate criteria to work either). Then I will
try to make it as user-friendly as possible since this DB is being designed
for use by small to mid-size churches where the user may know little or
nothing about Access.

One plus is that with your help I am getting a great education about queries!

Greatfully yours,

Hunter57

Hunter57, I don't have a clear understanding of what you are aiming to do
here.

John Vinson's response is the same as I would have replied to that part of
your thead, but apparently that's not what is going on either.

This is probably the kind of issue that you will need more professional help
with, so someone can examine your database, listen to what you are seeking
to achieve, and then help you find a way forward. It doesn't seem to be
something we can solve for you quickly in the newsgroups.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Correction: Here is the SQL for my filter Query 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)=[Forms]![frmSSAttendEntry]![txtSSClassDate])
AND
((tblSSAttendance.SSClassID)=[Forms]![frmSSAttendEntry]![cboSSClassID]));

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.

Temporarily remove the line that sets warnings to False.
Then read the dialog boxes.

Does it report an error? Or does it say it's appending 0 records?

If there is a message about violating rules or not being able to
append,
this might help you to track down the issue:
Why can't I append some records?
at:
http://allenbrowne.com/casu-19.html

If it says it's appending 0 records, we need to work on why it isn't
selecting the right records.

Another way to get information about the error is to use the Execute
method,
and read the error message:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


The message I am receiving is: "You are about to append 0 row(s)."