Avoid using DoCmd.RunCommand acCmdRefresh when adding a new record

[davegri]

After changing the checkbox, you can double click on the queryname in the navigation pane to see what it pulls. That will give you an idea of how it can be used for all the comboboxes and forms that need to see either active or inactive members.


Or you can simply watch the number of records at the bottom of the form's Navigation Buttons change as you click the checkbox to see how it works. It's all about how qry_MembersSelected is modified by the checkbox.

[WCStarks]

I copied the code in as written. When I attempt to uncheck the box I get the following error:
Run-Time error 3129 Invalid SQL statement; expected Delete, Insert, Procedure, Select, or Update, which occurs on the line: Qdf.SQL = ssql, in the If qExists Then statement. Am I supposed to put a Select in front of it?

Well, I didn't quite copy exactly as written. As I indicated above, I did modify the "ssql =" statement for when Is_Active is False, as it turns out incorrectly, to ssql = "Members", emulating that earlier code in post 11. Since that was the only difference, I went back, and after a couple of trial and errors, changed that line to ssql = "Select * Members" and now it seems to work, correctly. Sorry for the confusion. All is well, now. I am excited to take advantage of this new feature. Thanks again.

Not long ago, I had actually created a query which did essentially the same thing, but I could not figure out how to get it to run in background, so I was not able to use it. What you did, somehow causes the query to run in background. What part of the code does that? I really want to understand that.

[davegri]

Queries, like tables, present data on demand. In that sense they always are available to do that. My code simply creates a named query qry_MembersSelected every time the checkbox is clicked. The query will either include active members or not, depending on the state of the checkbox. Any form or combobox using that query will find it in whatever version was last created.

[WCStarks]

Well, it is a little embarrassing at how little I know about this stuff. Thank you for your explanation. I understand, you only create the query; you do not run it. My objects call the query as needed. Of course! I was only overwhelmed initially. As I studied the code more carefully, I can see how the code builds the SQL, but how these two queries are represented in Design View is not intuitive. I looked at the Design View and the SQL for each version of the query. When I first started with Access, I was more comfortable with the SQL view, as I was used to programming in Clipper (a dBase extension), which was all in lines of code. I eventually became comfortable with working in DV, because it helped me build the necessary SQL I could not think of on my own. But in these two instances, I don't understand how the SQL and the associated DV relate to each other.

In the true case, the SQL shows Select * From Members Where Active = True, the DV shows the source table and includes just the one field, Active = True. In the other case, DV shows no field selected from the table at all, when the SQL shows Select * From Members. What seems a little magical is, if I were going from the other direction and creating the query in DV, and that is all I put in, how does that produce Select * ..., when I didn't explicitly specify * in DV. This is not intuitive. But then, I guess Access has never been intuitive, at least not for me.

Normally, in DV, one must include all fields one wants to reference in the calling object. But that doesn't happen in these two cases. Somehow, one field (with the no show attribute), and no field in DV represents Select * in SQL. Hopefully, you can help me understand how that correlates. Thanks again for all your invaluable help. This is definitely a piece of code I will want to keep handy. It might be good to move the Function into a custom Module. I presume it will work there just as well?

[davegri]

The * is just a shortcut. It mean ALL the fields. It saves you the trouble of actually having to add the rather considerable number of individual fields to the query. If you run the query you will see all the fields are actually there in datasheet view.

[WCStarks]

Yes, and I have run both versions of the query. I know what Select * in SQL does. I just don't see how the resulting DV equates to the SQL Select * statement. Apparently one cannot get there via DV, but only via SQL.

[WCStarks]

Could something we did cause a report to break? I have a report, Stars_Awards, which calls Stars_Awards query, which calls Star_AW query. These queries run without error when run directly, but when I open the report, I get this error: "Run-time error: 3071; This expression is typed incorrectly, or it is too complex to be evaluated. For example ...." This report ran correctly the last time I ran it. It happens, that the Star_AW query makes three ELookups() to the Membership table, where we added an AfterUpdate procedure to the Status_Date control. I tried running the report with that statement commented out, but still get the error. The report has one expression: =IIf([Yrs]<5,DateAdd("yyyy",5,[Join]),[AwDate]). I tried temporarily removing that expression and ran the report, but the error still occurred. So, the error was not in that expression. I cannot tell from the error where it is occurring, and I cannot figure out how to run the report in debug mode.

No longer relevant.

[davegri]

SCATeam-davegri-v02.zip
Wow. Navigation forms. I will NEVER EVER design a db using them. Tab controls are the way to go.
I spent hours getting the attached to properly (I hope) requery and refresh recordsources and rowsources in the navigation subforms when a value was changed on the navigation form itself. Sounds simple. It's not.
Don't bother pointing me to websites that explain all. Been there. Most of them appear to give advice on standard form/subform procedures, assuming navigation forms behave the same way. They do not. Even Microsoft's MSDN sites have no idea.
At any rate, here's what I came up with. Mr WCStarks can take it from here and run, with my blessing. I'm running too.

[WCStarks]

I did the Navigation forms before I knew better. If there were a way to convert these to Tab Controls without a lot of trauma, I would do it. It would be much easier. The query creator you wrote works wonderfully. I really appreciate leaning how it is done. This was my big take-away from this thread. I will study your attachment to see what else I can learn from it. I didn't mean for you to go to that much trouble. Thanks.

[WCStarks]

Feedback on post #29. I had implemented it as described and it seemed to work fine. However, I discovered, that under certain circumstances, not well understood, the Member form and its Combo box would get out of sync, so that even when the Is_Active was False, when I would select someone who was not active, the engine could not find it. If I closed and re-opened the db it would usually start working correctly again. While I haven't figured out what sequence causes the problem, I thought of a possible fix.

The code in post 29 is in the Top Main form which is always open. The Members Form is not always open, as I may work with other sibling Navigation forms. I figured a possible solution might be to put the following code into the On Load Event of the Members form:
Call Parent.Is_Active_AfterUpdate. It may be working, as I haven't experience a re-occurrence of the problem, yet. We'll see if it continues to go well.

[WCStarks]

Hello Davegri, (Your creating a Members query data set on the fly)

I had hoped that my effort in post 40 would have resolved the issue of the main Members form not working correctly with the qry_MembersSelected query data set, but it did not.
1) If I initially toggle off the Is_Active checkbox in the Main form, while the Members NavigationSubform is active, when I use the search Combo box to find an inactive person, the engine reports it could not find the record.
2) If I then switch to another NavigationSubform tab and then switch back to (reload) the Members NavigationSubform, the searches work as expected.
3) After step 2 above, while still on the Members NavigationSubform, I can now toggle the Is_Active checkbox on and off, at will, and the searches work as expected.
4) For some reason, the data source for the Members NavigationSubform does not properly update initially.
5) Both the Search ComboBox and the the Members NavigationSubform use the qry_MembersSelected query as their data source.

I am at a loss as to how to resolve this issue. Hoping you can help.

[davegri]

SCATeam-davegri-v03.zip
See if this works any better.
Changed the recordsource for combo106
Changed event of Find_Sort from on_click to AfterUpdate

[WCStarks]

As indicated in post 41, I had already changed to record source of Combo 106 to qry_MembersSelected. Combo 106 updates correctly and allows me to select inactives when Is_Active is unselected. But, when I select someone who is inactive, (initially), that record cannot be found (I presume) in the qry_MembersSelected data source for the Members form.

I changed the Find_Sort code from On Click to After Update, as suggested. However, I am not sure what that is suppose to do. Anyway, it did not change the behavior. I still get "No Entry Found", when I select an inactive member in Combo 106. It never has an issue finding an active member.

Again, after I do step 2 as outlined in post 41, I can do as indicated in step 3.

[davegri]

Did you download the DB in post #42? It fixes all the problems mentioned in post#41.

[WCStarks]

Yes, but it doesn't run without errors in the create the query module. So, I looked at the related code.