Requery subform with main form filter

[tomocb]

I hope this will be an easy one for any of you who have experienced this problem before. I've been stuck on this for days. Here goes:

Access 2007 with a form and subform that I use to search for records. The form has several unbound fields used to filter the data in the subform.

When the form is opened the subform data entry value is set to yes so that the form is efficient by not displaying records that the user may not require.

Once the user has populated the unbound filters on the form they click on a button in the footer of the form which sets the subform data entry to no and then requeries the subform.

What actually happens is the data entry value is changed to no, but it displays all of the records and does not filter the subform based on the form filters.

Both the data entry and requery commands work indepedently of each other, ie. when separated. But when they are combined they do not work.

Thanks in advance for any assistance anyone can provide.

 

[Mr. B]

Do you have your sub form linked to your main form by at least one field?

 

[tomocb]

Do you have your sub form linked to your main form by at least one field?

Hi Mr. B. There is no link between the main and subform. The main form contains only unbound fields which are referenced in the selection criteria of the subform query.

 

[Mr. B]

tomocb,

You might try just having that one report in the main report and see if you can make it work and then you could add the others.

You just might run into something that would help clear up the issue.

I don't have anthing more specific. It just sorta sounds like a data issue.

 

[tomocb]

tomocb,

You might try just having that one report in the main report and see if you can make it work and then you could add the others.

You just might run into something that would help clear up the issue.

I don't have anthing more specific. It just sorta sounds like a data issue.

There aren't any reports involved in this. What do you mean by "others"?
I'm certain it is not data related because the form works perfectly without either the data entry change automation or the requery. It's only when the two are used in conjunction that it won't filter the subform query correctly.

Any ideas?

 

[Mr. B]

tomocb,

Sorry, I placed my response on the wrong thread. Please disregard.

For your question:

Have you tried adding code to the After Update event of your main form controls that will requery the sub report?

Me.NameOfSubReport.Requery

Again, sorry for posting the wrong info to this thread.

 

[tomocb]

No problem. The requery and data entry commands are executed from an onclick trigger on a button in the footer of the main form.

I don't use the After Update even in any of the unbound filter fields because the form must only requery once the user has filled in all the filter fields that they require.

The onclick works fine if it's just either the requery or the data entry command, but not both together.

 

[Mr. B]

You must set the data entry property first, before doing the requery. As long as the Data Entry is set to yes, then the form requires data entry for a new record and will never display any existing data. You may need a DoEvents between the code that is setting the Data Entry to No and the Requery code.

I have not actually tested this, but the setting for Data Entry being set to Yes is the problem.

 

[tomocb]

You must set the data entry property first, before doing the requery. As long as the Data Entry is set to yes, then the form requires data entry for a new record and will never display any existing data. You may need a DoEvents between the code that is setting the Data Entry to No and the Requery code.

I have not actually tested this, but the setting for Data Entry being set to Yes is the problem.

The data entry is being changed first. I then use a Go To Control to focus on the subform and then I pass the Requery command.

There is no DoEvents in the Macro module, so I'm not convinced that it needs that command specifically. Is there another way of waiting for the Data Entry change to finish before the next command is executed?

 

[Mr. B]

I actually did test setting the Data Entry using VBA code and then doing a requery and there was no need for the DoEvents.

I only tested with a main form, not a sub form, but I don't think that there would be and difference.

I am not sure just what to suggest for you to try. Is it possible to post your database of at least the part that your having problems with?

 

[tomocb]

I actually did test setting the Data Entry using VBA code and then doing a requery and there was no need for the DoEvents.

I only tested with a main form, not a sub form, but I don't think that there would be and difference.

I am not sure just what to suggest for you to try. Is it possible to post your database of at least the part that your having problems with?

Here's the database. Though i have had to strip most of it out, the code that is left is all there is for these forms. Thanks.

 

[Mr. B]

tomocb,

I have downloaded your database file and will look at it when I get a few minutes.

 

[Mr. B]

tomocb,

I have attached a very modified version of your database.

When you open the file, take a look at the "frmNewServiceJobsView" form. I have redeigned it. It still has the same functionality as your original form except that there is no need for the sub form.

Sub forms are really designed to be used where you have a main form with records and a Many relationship to another set of records.

I did not include your date field as part of the Filtering process, but hopefully you will get the concept and the idea of just how this was done.

One more major change is that I used VBA code in the events of various controls to accomplish the tasks as needed. Macros are Ok, but they are very limiting and I would encourage you to move in the direction of VBA code.

As you have questions about what I did, and I know you will, just post back.

 

[tomocb]

Many thanks Mr B. Looks like you have done quite a bit with it.

The subform is used as a datasheet and is not actually a tabular form or similar. However, your comments are very interesting and I will look into that a bit, as I spent some time deciding on using a subform.

The first thing I notice from your code is that I believe you're using a form filter at runtime. This is my fault for misleading you in my original post. Ideally I need to have the query run server-side, which is why this prototype has the filter criteria in the query and not in the form's code.

I'm not sure, but this maybe why the excellent work you have done has been successful, even though it does it in a different way.

I tried retro fitted your code to my original version without success.

I'm still puzzled that you had to go through all the effort of changing the actual design of the form in order to get it to work. I take it didn't until you did make the necessary changes?

Lastly, you're right that I should be using VBA. The reason for still using Macros is due to the size of the original database and I'm trying to grow it extremely quickly. I will try and transpose my original macros into VBA in the same way that you applied you code and see what happens.

Will let you know. Many thanks.

 

[Mr. B]

Yes, Tom,

For some reason (and I am still not sure just why because I did not have time to spend on it) your Form/Subform design just would not filter the data. I even tried extracting the sql statement from your form and just running it and it still would not work. If I removed the reference to your form and just typed in say a 3 for the "Reference" then the query would return only the record where the Reference was a "3". By the way, I noticed that the Reference field is a text field. I'm sure you have a reason, but I just thought it was unusual.

I did not think that what I was doing would be exactly what you needed, I just wanted to kinda give you a nudge in the right direction. Hopefully I have done that.

Post back if you need more info or assistance.

 

[tomocb]

The Reference field is a text box by default. Are you refrring to the format of the number within the field or comparing it with the combo box you had in your version?

I'm not intending on using a combo box due to the number of potential records. Similar to the data entry approach - I'm trying to make the form as efficient as possible.

 

[Mr. B]

I am referiong to the value of the combo box. You would just need to refer to the value of the text box.

You might want to consider having some code that when the user has entered the Reference code, a check would be made to be sure that that reference value exists.

Just a thought.

 

[tomocb]

Still no luck getting this to work with your code and my design, so just summarising where we are with this so that anyone else can offer their thoughts:

So far it appears that you are not able to turn off data entry on a subform before using a requery command to refresh the data based on an unbound filter in the main form????

This cannot be correct. Any thoughts anyone? Thanks.

 

[Mr. B]

tomocb,

Is there some specific reason that you want or have some requirement that you use a subform? I you just absolutely must have the subform then I can go back and look at making it work.

The reason that I changed the design of your form is that for what you are doing you do not need a subform.

I have attached a modified version of my redisigned form. This time it uses a text box and not a combo box for user input.

 

[tomocb]

I think the Subform is a red herring and is not the issue.

Also, I will be using a combination of text boxes, combo boxes, etc as filters. So this is also something that we should not get hung up on.

The purpose of the subform is to display records in a datasheet format. It is not possible to di it any other way.

I believe that the data entry change is causing the data source to become disconnected in someway, which is why it simply displays all of the data and does not factor in any of the filters from the form header.