Form and query return different record sets

[Daryl2106]

Is there a common answer or design mistake that would cause a form to return a different (much higher) record set than that if the query is ran by itself. The query is the control source for the form.



Thanks and take care,

Daryl

[Rod]

No. All other things being equal they should be the same, that is, return identical result/record sets. Are you saying the the form returns more records?

The query is the control source for the form.

Is that a slip of the tongue? A form has a record source but no control source. It of cource is possible to have a query as a row source for a combo/list box control.

Has the query any variables in the WHERE clause? Does the query use dates?

Can't think of anything else for now. Can you post your db? (As v2007 if possible.)

[Daryl2106]

Hi Rod,

Thnaks for the reply. Yes the form is returning 729 records to the queries 126 (which is the correct number). It did mean record source. Here is the SQL. The form is using a combobox where date is selected and the StudentAttendanceT has a date stored with each record. The DB is 2010. I'll try to covert and post in the morning. Once agian thanks for having a look.

Take care,

Daryl


SELECT StudentAttendance.StudentID, StudentAttendance.AttendancePeriodID, StudentAttendance.ClassesAttended, StudentAttendance.ClassesOffered, StudentAttendance.FacultyID, ContactInformation.FirstName, ContactInformation.LastName
FROM ContactInformation RIGHT JOIN StudentAttendance ON ContactInformation.StudentNumber = StudentAttendance.StudentID
WHERE (((StudentAttendance.AttendancePeriodID)=[Forms]![EditAttendanceQuery]![AttendanceP]) AND ((StudentAttendance.FacultyID)=[Forms]![EditAttendanceQuery]![FacultyID]));

[Rod]

Conversion is easy - just use the 'Save As' option.

There's a discrepancy in the table names: you use 'StudentAttendanceT' in your write-up and ' StudentAttendance' in your SQL. However I think this is another typo?

I assume 'AttendanceP' is the combo box. Make sure the combo box is bound to the correct column; the other side of the comparison looks like an id and not a date.

I am intrigued as to why the form is giving more records. Less records I can understand and there may be many reasons for that, but more? If either 'AttendanceP' or 'FacultyID' is null then the query should produce nothing - or at least it will with an inner join; I'm less sure about outer joins when the WHERE comparisons refer only to the minor table attributes. Queries can generate many, many records but this happens when there is no join - not the case here.

I assume we're not getting confused with a subform count?

I assume that, as your control (i.e. 126 records), you are running the query from the query design grid with the form loaded and not entering the parameter values manually.

Are there many VBA procedures or macros associated with the form?

Weird - it has to be something to do with the form itself. Have you tried designing a new, simple test form, clean of everything except binding the record source to the query? You will need the AttendanceP and FacultyID controls.