Workaround SharePoint Query Limit? (1 Viewer)

Hi there,

I really hope someone can help before I tear my remaining hair out!

I have a web form "ExchangeView" which is populated with data from the "Exchanges" table. Attached to "ExchangeView" is a subform "PCPList" which pulls data from the "PCPs" table and displays it in datasheet view. The form and subform parent/child link is a field called "UID".

This all works fine within Access but this is a web database hosted on SharePoint and the "PCPs" table has just under 25,000 records. This means that due to SharePoint's 5,000 query limit, when the database tries to filter the "PCPList" subform by the parent/child link "UID" it goes well over the 5,000 limit! Which in turn means I get the following error:

"Failed to retrieve list data. Please try again."

Obviously it doesnt matter how many times I try again. The only way to solve this is to remove most the records from the "PCPs" table so that there are only 5,000 left. Then the query can run successfully.

I've got one, really annoying solution which is instead of using the "PCPList" subform, I could put a command button on the form and set it so that it opens the "PCPs" form in a dialog box and filters it by the "UID" field from the "ExchangeView" form.

What I really want though is the subform option so that users can easily check which "Exchanges" records have an associated "PCPs" record without having to click the filter button everytime the main record is changed and then close the resulting dialog box when they find its not what they were looking for.

Is there any way I can do this with a web-compatible macro or module of some kind? I can remove the parent/child links so that the full "PCPs" table is shown in the subform but how can I get it to automatically filter based on the "UID" field on the "ExchangeView" form?

Please be gentle, Im pretty new to this whole access thingy.

Thanks for any help you can give!

Nick

Update: I tried changing the way my subform is filtered by setting a macro on the "on current" event of the main form "ExchangeView". Again, everything worked fine in Access but when I try to view the form on the SharePoint site, I get the "failed to retrieve list data" error whenever I invoke the "on current" event. After clicking the Ok button in the error dialogue box, the action failed dialog box appears, as seen in the attached image.

Not sure if any of that helps but I'll keep trying different things!

Thanks again everyone

Nick

Take a look at your macro that is opening your sub-form. You can provide the filter for the sub-form in the macro that opens it.

Sorry I'm not quite sure what you mean?

My subform is built into the main form, I dont use a macro to make it appear. Is that what I should be doing? If so I have no idea how to!

Could I use the Filter option in the property sheet to make this work? I've tried several formulas but cant get any of them to work. Any ideas what the correct syntax for this field is?

I'ver tried so many things that Im starting to wonder whether this functionality has to be coded? SharePoint just throws out errors about not being able to retrieve the list data, which means (I think) its hitting its 5,000 query limit thing?

I havent got the first clue about modules, class modules, vba or whatever else is available on the advanced access side of things.

Thanks again for the help!

Nick

Ooops. I don't know where my head was. Sorry for the confusion.

Just forget my previous post. Try adding the "UID" field to the Master/Child link between your sub form and the main form and see if that will give you what you want.

Again, sorry for the incorrect info.

Dont worry about the wrong info! I dont think I can get more confused anyway lol!

Putting "UID" as the Master/Child link was the very first thing I tried, but the "PCPs" table has over 5,000 records (24,000 to be exact).

From what I've read on the web, SharePoint has a 5,000 record query limit which is why I get the "Failed to retrieve list data" error. I've tested it and if I delete most of the "PCPsTable" records so I only have 5000 records left, the Master/Child link method works. If I then add another record and hit 5001 records and retry, I get the "failed to retrieve data" error again.

I guess I need to filter my data in a way that isnt classed as a query and I presume the only way Im going to do that is with a complicated macro or vba?

Ugh, I wish all my users had Access then I wouldnt have to work this one out

Cheers,

Nick