Stumped for now - could it be Access 2007? References?

[Fred Boer]

Dear Ken:

Thank you! That's great advice - and something new I've learned!


--
Fred Boer - Amateur Access Enthusiast
Interests: Library software / Z39.50 / Web Services
Freeware Small Library Application available here:
http://www3.sympatico.ca/lornarourke/

Fred -

In cases like these, I explicting declare the parameters in the query to
avoid data mismatching that won't give an error but don't produce any
records either (e.g., a date/time value that is interpreted as something
other than a date/time value). Try this for the subform's RecordSource
query (assuming that ClassID is a Long Integer data type):


PARAMETERS [Forms]![frmNewDayBook]![txtLessonDate] DateTime,
[Forms]![frmNewDayBook]![txtClassID] Long;
SELECT tblAttendance.AttendanceID, tblAttendance.StudentClassID,
tblAttendance.AttendanceCode, tblAttendance.ClassDate,
tblStudentClasses.ClassID, tblstudents.StudentLastname & ", " &
tblStudents.StudentFirstName & " " & tblstudents.StudentMidName AS
FullName
FROM (tblStudents RIGHT JOIN tblStudentClasses ON tblStudents.StudentID =
tblStudentClasses.StudentID) LEFT JOIN tblAttendance ON
tblStudentClasses.StudentClassID = tblAttendance.StudentClassID
WHERE (((tblAttendance.ClassDate)=[Forms]![frmNewDayBook]![txtLessonDate])
AND ((tblStudentClasses.ClassID)=[Forms]![frmNewDayBook]![txtClassID]))
ORDER BY tblStudents.StudentLastName, tblStudents.StudentFirstName;


--

Ken Snell
<MS ACCESS MVP>

Hi Ken:

Sorry if I wasn't clear! The form is called "frmNewDaybook" is actually
based on a table called "tblLessons", with the fields:

LessonID (Autonumber PK)
ClassID
LessonDate
LessonTopic
LessonNotes

frmNewDaybook is based on a filtered version of the table, based on
choices of class and date made by made in frmSelection, which is the
first form shown, and which actually opens frmNewDaybook.

The attendance subform is based on the following query:

SELECT tblAttendance.AttendanceID, tblAttendance.StudentClassID,
tblAttendance.AttendanceCode, tblAttendance.ClassDate,
tblStudentClasses.ClassID, tblstudents.StudentLastname & ", " &
tblStudents.StudentFirstName & " " & tblstudents.StudentMidName AS
FullName
FROM (tblStudents RIGHT JOIN tblStudentClasses ON tblStudents.StudentID =
tblStudentClasses.StudentID) LEFT JOIN tblAttendance ON
tblStudentClasses.StudentClassID = tblAttendance.StudentClassID
WHERE
(((tblAttendance.ClassDate)=[Forms]![frmNewDayBook]![txtLessonDate]) AND
((tblStudentClasses.ClassID)=[Forms]![frmNewDayBook]![txtClassID]))
ORDER BY tblStudents.StudentLastName, tblStudents.StudentFirstName;

The application has the following tables:

tblAttendance (AttendanceID, StudentClassID, AttendanceCode, ClassDate)
tblClasses (ClassID, ClassName)
tblLessons (LessonID, ClassID, Lesson Date, LessonTopic, LessonNotes)
tblStudentClasses (StudentClassID, StudentID, ClassID)
tblStudents (StudentID, StudentLastname...)

Cheers!
Fred

Ken, I've been thinking, (although I am not going to think anymore
after this post <g> - at least not tonight). If we assume the the
"error" is that the form is opening without data - then timing isn't an
issue, since it will open blank whether I step through the code or not.
Does that make sense? Error messages do occur if I try to do anything
once the form is open, but it opens without error messages (though the
blankness is itself an "error").


I misunderstood your info then -- I thought I'd read that the problem
didn't occur when you stepped through the code, but did occur when your
code ran on its own.

If the form is opening without records, then likely the query is not
reading the parameter or WHERE filter correctly. Can you post the SQL
statement of the form's Recordsource query?

 

[Fred Boer]

Just FYI:

I tried Decompile/Recompile, with no change in bad behaviour. I also tried
the parameters suggestion from Ken, but no better luck. I tried opening the
form directly with values hard-coded into the controls and query - and the
form opened with data. So the problem may be something in the process of
loading the values. I haven't had time to try Ken's openargs suggestion, and
that is on my list next chance I get. I am also going to look very hard at
the way the whole thing is put together and the processes it uses in the
hopes of finding something. Thanks for the suggestions - I am still fighting
the good fight. I'll keep you posted!

Cheers!
Fred


--
Fred Boer - Amateur Access Enthusiast
Interests: Library software / Z39.50 / Web Services
Freeware Small Library Application available here:
http://www3.sympatico.ca/lornarourke/

Dear Ken:

Thank you! That's great advice - and something new I've learned!


--
Fred Boer - Amateur Access Enthusiast
Interests: Library software / Z39.50 / Web Services
Freeware Small Library Application available here:
http://www3.sympatico.ca/lornarourke/

Fred -

In cases like these, I explicting declare the parameters in the query to
avoid data mismatching that won't give an error but don't produce any
records either (e.g., a date/time value that is interpreted as something
other than a date/time value). Try this for the subform's RecordSource
query (assuming that ClassID is a Long Integer data type):


PARAMETERS [Forms]![frmNewDayBook]![txtLessonDate] DateTime,
[Forms]![frmNewDayBook]![txtClassID] Long;
SELECT tblAttendance.AttendanceID, tblAttendance.StudentClassID,
tblAttendance.AttendanceCode, tblAttendance.ClassDate,
tblStudentClasses.ClassID, tblstudents.StudentLastname & ", " &
tblStudents.StudentFirstName & " " & tblstudents.StudentMidName AS
FullName
FROM (tblStudents RIGHT JOIN tblStudentClasses ON tblStudents.StudentID =
tblStudentClasses.StudentID) LEFT JOIN tblAttendance ON
tblStudentClasses.StudentClassID = tblAttendance.StudentClassID
WHERE
(((tblAttendance.ClassDate)=[Forms]![frmNewDayBook]![txtLessonDate])
AND ((tblStudentClasses.ClassID)=[Forms]![frmNewDayBook]![txtClassID]))
ORDER BY tblStudents.StudentLastName, tblStudents.StudentFirstName;


--

Ken Snell
<MS ACCESS MVP>

Hi Ken:

Sorry if I wasn't clear! The form is called "frmNewDaybook" is actually
based on a table called "tblLessons", with the fields:

LessonID (Autonumber PK)
ClassID
LessonDate
LessonTopic
LessonNotes

frmNewDaybook is based on a filtered version of the table, based on
choices of class and date made by made in frmSelection, which is the
first form shown, and which actually opens frmNewDaybook.

The attendance subform is based on the following query:

SELECT tblAttendance.AttendanceID, tblAttendance.StudentClassID,
tblAttendance.AttendanceCode, tblAttendance.ClassDate,
tblStudentClasses.ClassID, tblstudents.StudentLastname & ", " &
tblStudents.StudentFirstName & " " & tblstudents.StudentMidName AS
FullName
FROM (tblStudents RIGHT JOIN tblStudentClasses ON tblStudents.StudentID
= tblStudentClasses.StudentID) LEFT JOIN tblAttendance ON
tblStudentClasses.StudentClassID = tblAttendance.StudentClassID
WHERE
(((tblAttendance.ClassDate)=[Forms]![frmNewDayBook]![txtLessonDate]) AND
((tblStudentClasses.ClassID)=[Forms]![frmNewDayBook]![txtClassID]))
ORDER BY tblStudents.StudentLastName, tblStudents.StudentFirstName;

The application has the following tables:

tblAttendance (AttendanceID, StudentClassID, AttendanceCode, ClassDate)
tblClasses (ClassID, ClassName)
tblLessons (LessonID, ClassID, Lesson Date, LessonTopic, LessonNotes)
tblStudentClasses (StudentClassID, StudentID, ClassID)
tblStudents (StudentID, StudentLastname...)

Cheers!
Fred

Ken, I've been thinking, (although I am not going to think anymore
after this post <g> - at least not tonight). If we assume the the
"error" is that the form is opening without data - then timing isn't
an issue, since it will open blank whether I step through the code or
not. Does that make sense? Error messages do occur if I try to do
anything once the form is open, but it opens without error messages
(though the blankness is itself an "error").


I misunderstood your info then -- I thought I'd read that the problem
didn't occur when you stepped through the code, but did occur when your
code ran on its own.

If the form is opening without records, then likely the query is not
reading the parameter or WHERE filter correctly. Can you post the SQL
statement of the form's Recordsource query?

 

[Ken Snell \(MVP\)]

I tried opening the form directly with values hard-coded into the controls
and query - and the form opened with data.

This suggests that the query is not "seeing" the parameter values in the
correct data type, or that the form does not have non-Null values in those
controls when you run the query (related to my initial thought about timing,
perhaps).

Let's try an experiment. Modify the subform's RecordSource query to be this:

PARAMETERS [Forms]![frmNewDayBook]![txtLessonDate] DateTime,
[Forms]![frmNewDayBook]![txtClassID] Long;
SELECT [Forms]![frmNewDayBook]![txtLessonDate] As txtLessonDateP,
[Forms]![frmNewDayBook]![txtClassID] As txtClassIDP,
tblAttendance.AttendanceID, tblAttendance.StudentClassID,
tblAttendance.AttendanceCode, tblAttendance.ClassDate,
tblStudentClasses.ClassID, tblstudents.StudentLastname & ", " &
tblStudents.StudentFirstName & " " & tblstudents.StudentMidName AS FullName
FROM (tblStudents RIGHT JOIN tblStudentClasses ON tblStudents.StudentID =
tblStudentClasses.StudentID) LEFT JOIN tblAttendance ON
tblStudentClasses.StudentClassID = tblAttendance.StudentClassID
WHERE (((tblAttendance.ClassDate)=[Forms]![frmNewDayBook]![txtLessonDate])
AND ((tblStudentClasses.ClassID)=[Forms]![frmNewDayBook]![txtClassID]))
ORDER BY tblStudents.StudentLastName, tblStudents.StudentFirstName;


Then add two visible textboxes to your subform and bind them to the two new
fields that I added (which are the parameter values).

Now run your code to open the form. What values do you see on the subform in
those two controls? And watch the controls carefully -- do they fill in with
different values shortly after the subform has been opened?

 

[Fred Boer]

Dear Ken:

You are kind to stick with me! I will try to find time tomorrow to implement
your suggestion.

Oh, and tonight I realized that I have a computer at home which has only
Access 2003 and does not have Access 2007. While my family thought I was
working on tomorrow's lessons, I snuck onto that computer and tried it and
was *not* able to reproduce the problem - which makes me again suspicious
that there might be something on the network at work which is causing an
issue. I will have to pin the systems officer to the wall and quadruple
check my permissions!

However, at the moment I am taking a quick breather from trying to help my
son with his Grade 10 math homework. (I barely passed Grade 10 math, oh, 30
years ago or so...) He has a non-equilateral triangle with two bisecting
lines that are parallel. He knows that ST - QR is 8 cm...

I'm toast...

--
Fred Boer - Amateur Access Enthusiast
Interests: Library software / Z39.50 / Web Services
Freeware Small Library Application available here:
http://www3.sympatico.ca/lornarourke/

I tried opening the form directly with values hard-coded into the
controls and query - and the form opened with data.

This suggests that the query is not "seeing" the parameter values in the
correct data type, or that the form does not have non-Null values in those
controls when you run the query (related to my initial thought about
timing, perhaps).

Let's try an experiment. Modify the subform's RecordSource query to be
this:

PARAMETERS [Forms]![frmNewDayBook]![txtLessonDate] DateTime,
[Forms]![frmNewDayBook]![txtClassID] Long;
SELECT [Forms]![frmNewDayBook]![txtLessonDate] As txtLessonDateP,
[Forms]![frmNewDayBook]![txtClassID] As txtClassIDP,
tblAttendance.AttendanceID, tblAttendance.StudentClassID,
tblAttendance.AttendanceCode, tblAttendance.ClassDate,
tblStudentClasses.ClassID, tblstudents.StudentLastname & ", " &
tblStudents.StudentFirstName & " " & tblstudents.StudentMidName AS
FullName
FROM (tblStudents RIGHT JOIN tblStudentClasses ON tblStudents.StudentID =
tblStudentClasses.StudentID) LEFT JOIN tblAttendance ON
tblStudentClasses.StudentClassID = tblAttendance.StudentClassID
WHERE (((tblAttendance.ClassDate)=[Forms]![frmNewDayBook]![txtLessonDate])
AND ((tblStudentClasses.ClassID)=[Forms]![frmNewDayBook]![txtClassID]))
ORDER BY tblStudents.StudentLastName, tblStudents.StudentFirstName;


Then add two visible textboxes to your subform and bind them to the two
new fields that I added (which are the parameter values).

Now run your code to open the form. What values do you see on the subform
in those two controls? And watch the controls carefully -- do they fill in
with different values shortly after the subform has been opened?

 

[Ken Snell \(MVP\)]

However, at the moment I am taking a quick breather from trying to help my
son with his Grade 10 math homework. (I barely passed Grade 10 math, oh,
30 years ago or so...) He has a non-equilateral triangle with two
bisecting lines that are parallel. He knows that ST - QR is 8 cm...

I remember my 10th grade geometry class -- got an A in it (was an "honors"
class, too) -- sigh, and that was 37 years ago for me.....

Good luck.

 

[Fred Boer]

Dear Ken, Sascha, Pieter and Steve:

Problem has been solved - and, of course, I feel really stupid. It was a
combination of regional settings mismatch and a default value of zero which
I somehow hadn't removed from a field in the tables and which Access had
kindly inserted.

Ken, you were on the money in one of your first posts about the format of
the date. I *know* I tested for that, but obviously I made some kind of
mistake..

I am very grateful for all the help and suggestions!

Cheers!
Fred

 

[Ken Snell \(MVP\)]

You're very welcome. We've all experienced that same "feeling" before.... <
g >