Requery subform with main form filter

[Mr. B]

I have attached a modified version of the database.

This time just try using your form and subform (not my new form). The only criteria that I have made work is the Reference field. With no more data than I had to work with I did not take the filtering any further.

I did change from having the command buttons to run macros to using VBA code. I think you will need to use VBA because of some of the assigning of your criteria and creating the string that you would use as the "Filter" for the sub form.

HTH

 

[tomocb]

Thanks Mr B, but the attachment appears to be invalid. Can you check it please.

 

[Mr. B]

tomocb,

Check your security settings or make sure that you have the database in a "Trusted Location". Either of these will keep it from working.

I downloaded it and tested it here and it does work and is a valid file.

I have attached it again, download it one more time and try it.

 

[tomocb]

It's the zip file that's thr problem and not the database. The zip file advises that there is nothing to extract and that it is invalid.

Your working version might be cached on your computer.

 

[Mr. B]

I downloaded the file to a different loacation and extracted the file.

I have no idea why it will not work for you.

I have recreated the zip file again and attached it. Please try one more time.

 

[tomocb]

I can access the file now, thanks.

 

[tomocb]

Thanks Mr B. What you've done proves that it's not the subform that's the problem.

But I noticed that it still uses a client-side form filter, which seems to be the only difference now between our two versions.

The question remains then; Why does a subform requery not include filter criteria from a form??

 

[Mr. B]

Man, I'm glad you were able to access the file. I was beginning to wonder what was going on.

To answer your queston; Normally a subform is linked to a main form through the Link Child field and the Link Master fields. These fields represent the PK of the data in the main form and the FK in the data in the sub form. When the main form is moved to a specific record, the sub form automatically presents records that match the link fields criteria.

Filtering a form is a totally different issue. Any filter must be specifically applied to a form or a sub form. You can actually display a single record by applying a filter to a form. Likewise, you can display all records in a sub form that are relative to the record in the main form or you can apply a filter to the sub form to display only a sub set of all of the records.

Because you do not have your main form bound to any data source you are really not in a situation where you really even need to use a sub form. You data could actually be bound to your form not the sub form. Then you would simply apply a filter to the data when needed. Although the structrue you have can be made to work (as demonstrated by the last example file) the coding is just a little more difficult to do.

Because there is always more than one way to do the same thing, doing things in a way that someone else might not do the same thing does not mean that the way you chose to accomplish your goad is wrong. The thing that I think is most important is, is the method used the most effecient method or does it meet the customers requirement in the most desirable manner possible.

I hope I have given you some 'food for thought'.

 

[Simon_MT]

I had a look at your database and my solution would be intercept the Service View form with a Form to Search the data.

If I want any information there is always a Artist Search Form:

Look at this:

http://www.designedlogic.com/

Simon

The advantage is that you can ask a multitude of questions using the same device.

Simon

 

[tomocb]

Thanks Mr B.

I'm not convinced that a subform has to have a parent/child link in order to work properly. I've not seen any evidence to support this.

Also, it's not possible to get the datasheet format I need without the use of a subform. A form with a header/footer in datasheet format does not display as a datasheet properly.

I've managed to get the form working with both the requery and data entry independently. It's only a problem when they're both used in conjunction.

 

[Mr. B]

I have never tried to convince you that a sub form Must be used with a link to a main form. I think you got it backwards. I have been saying all along that functionally you do not need the sub form. If it suits you to have one, then that is just fine. If you noticed in the form that I created in the demo database, I use the main form as a continuous form. Thus giving basically the same access to the data as you would get with the datasheet format, except that you gain control over each of the controls on the form for use with VBA and other actions that you might need.

As you can tell, I do not use datasheet views very often. There are places where they are good but mostly I use the continuous forms.

You certainly should use the type of presentation that you want to use.

I'm happy with it if you are. LOL

 

[Simon_MT]

You can format any form Detail section to emulate a datasheet format.

Simon

 

[tomocb]

Thanks Simon MT, but that is not the issue.

The issue is that I'm trying to identify why it is not possible to turn off data entry and then requery a subform based on one or more unbound fields in the main form.

Unless someone can tell me that this is not possible then it is sensible for me to work out how to get it to work as I have designed it rather than design around the problem.

Does anyone else have any thoughts on this please?

 

[Simon_MT]

My approach is to intercept the Selection of Service Jobs with a Search Form first and then decide on the Search Form whether or not you want if to Read Only or to allow edits.

You can dispense with the SubForm completely.

Simon

I use this Search facility frequently as you can see:

http://www.designedlogic.com

Simon

 

[tomocb]

I refuse to believe that there is no one who can solve this problem! Thank you to all so far who have offered alternative solutions, but please someone tell me what I'm doing wrong!

 

[tomocb]

My approach is to intercept the Selection of Service Jobs with a Search Form first and then decide on the Search Form whether or not you want if to Read Only or to allow edits.

You can dispense with the SubForm completely.

Simon

I use this Search facility frequently as you can see:

http://www.designedlogic.com

Simon

Just throwing this one back out there to see if anyone is any the wiser.

Simon - The form that I have constructed is actually a search form and as such is read-only.

Once the records have returned based on the filters selected in the unbound controls the user can then open that record in a new edit form.

The problem is simply that if you change the Data Entry property then Access ignores the criteria of the query by form.

Come on, this must be any easy one for may of you out there!!

 

[tomocb]

So just to summarise for any new readers to this thread:

Is it not therefore possible to use a combination of Query By Form on an unbound form and Data Entry = False property on a bound subform???

Surely this is how 'search' forms are supposed to work?!?

 

[tomocb]

Mods - Please close this.

Thanks for your help everyone who posted. I will post this problem on another forum.