Totals in a subform (continuous Form)

[scoe]

Sorry, new in 2010 so have just found out how to saveit as a mdb.

FS 1a.zip

[rpeare]

What I mentioned in one of my previous posts is exactly what's happening.

Your 'search' boxes are a course code and a student ID, but you are not limiting your 'main' record to these two search criteria, thus your 'main' record is basically showing every record in your functional skills entry level table and your subform is showing every record associated with every 'main' record.

it looks to me like you're trying to narrow your search to both a course and student on both form and subform, is that correct? if so you have to have a criteria in your MAIN form ROWSOURCE

In your studentID field [forms]![formname]![studentsearchfield] where formname is the name of your form and studentsearchfield is the NAME of the field that contains your student ID you're searching for

same goes for your course code field.

Then issue a me.requery command in the ON EXIT property of both your search fields.

When your form comes up it won't show any data, you have to pick both items for it to show data from then on.

[scoe]

Thank you for getting back, the idea is that they can either search by name or by course depending on whether they have an odd student to update or several within their course, I don't think they will often select both.

[scoe]

Thank you so much, I've cracked it at last both boxes work, just need to set the reuery command. Thank you for your time and patience.

[scoe]

My mistake it still wont work on the student filter, as you said it is filtering on both not just one or the other.

[rpeare]

you can use the like statement

like * & [forms]![formname]![studentsearchfield] & *

Because you're picking from a static list and not allowing someone to type in the value the like value will allow you to choose a specific value OR, if nothing is selected choose all values.

[scoe]

Thank you, unfortunately nothing has changed, it still is not filtering on the course on the subform only the student. The student filter is working perfectly however when I select the course ENFTX1-SLUKY I should have 9 records but only 2 are filtered.

[rpeare]

You'll have to post an updated database, I suspect you're still not correctly linking mainform to subform but I'd need an updated example to say for sure.

[scoe]

FS 1b.zipThank you again for all your help, very much out of my depth with this one, I know it should be straight forward but I don't seem to be able to get it to work. I have attached another up to date copy.

[rpeare]

Don't use filters :P

What are you trying to do with this query/form? you have it set up as a form/subform but it looks (from what I can see) as though you don't want that, you just want to be able to look up by class OR student and see all information related to that choice. It doesn't look like you care about looking up a specific student for a specific class and typically when you have a form/subform you want the ability to modify the main form record as well as any records associated with that main record but your main form has no data entry components. In fact it looks like the query driving the form and subform are virtually identical which doesn't make sense for a form/subform.

If you want to look up data that is related to a class or a student you don't need the arrangement you have. You just need a main form so if you can clarify EXACTLY what you want to be able to do with this form (see all related information to a CLASS, see all related information related to a STUDENT, see all related information related to a CLASS *AND* STUDENT, see all related information related to a CLASS *OR* STUDENT, etc) I can tell you want to do.

[scoe]

Originally it was just a main form, however they use it for claims and requested a warning message to come up before leaving the department selection or student reminding them to run the claims form, however this is generated by the hidden box which sums up the number of outstanding claim forms to print. As a main form this meant that the message caim up as soon as they entered the form and as you can imagine is driving them to drink. By slitting the form it solves the problem as it only generates the number of forms once the filter is on..... Most staff go in via the department as they have been given forms from a tutor relating to this but will sometimes be sent individual forms so it is easier just to go straight into the student, hence the two boxes.

Thank you for your time with this, it seems as always, you cure one problem but find yourself trying to sort another.

[scoe]

I have solved this, in a fashion, by breaking it down to two deifferent search screens, thank you for your help.