Form auto-updating from a query.

[Guinea]

Ok I thought about it for awhile and I think I found a solution, but I just don't know how to execute it. When the mainForm loads, the secondForm loads inside the Subform...easy concept.



So lets just ignore the Mainform altogether. Forget it is even there. Let's say there is a form, and a query. The results of the query changes depending on criteria. How can I get the form to update its data based on that query? So whenever the form loads, it requeries and displays the new information.

[RuralGuy]

What I gave you in Post #8 should work here. If not then something else is going on that you have not described yet.

[Guinea]

Ok that makes perfect sense. How would that be written properly though? I have a form and the recordSource is the query Selected General Meeting Roster. So under OnCurrent....what would the code be to make it where when the form loads, it re-queries the record source.

[RuralGuy]

When a form loads it *always* requeries the RecordSource of the form. No extra effort on your part is required.

[Guinea]

Well the recordsource of the form is a query and the query's results change constantly. So why does my form still only display the results of the first query instead of requery'ing and displaying the new results?

[RuralGuy]

We've been here before. What are you doing to cause "the query's results change constantly"? That does not naturally happen when you load a form. It usually just sits there waiting for the user to do something.

[Guinea]

The query has criteria based on the date of the form that is open. So the results of the query change whenever I view a different date.

The form I made....is a form FROM the query...and it displays the results of the query in a cleaner format. So how can I get the form to display the new query results, without building an entirely new form from the query? I just want it to update to the new records.

[RuralGuy]

What are you doing to change the date?

[Guinea]

The date is something that I input on the form itself. I type in a date and query uses that date to check against another table's dates

[RuralGuy]

How does it do that? Do you have some code in the AfterUpdate event of the Date control?

[Guinea]

If I explain it, it will just make things more complicated because then it goes back to the Form/Subform issue. Basically there is a column in the query that checks the date. The criteria for that column is that it is equal to the date in an open form. So whatever form is open, it grabs the date from a "Date" block and uses that as criteria to change the results.

But that is in the Main Form. The form I am trying to update is the form that is created from the query. So basically whenever THAT form loads, the query outputs its new results into that form.

[RuralGuy]

Maybe it is time to post your db so we can look at it. Remove any sensitive data and zip it up before attaching it to a post.

[Guinea]

Ok here is the file.

Let me explain what you are looking at. The Essentials group has 3 forms. The student list is the roster. If you click a student, you can see an Attendance tab. Under there, I select a date and can set Present or not present.

If you click the General meeting list....you can open up the meeting. The meeting details sheet has a date on it and a subform for the attendance of THAT meeting based on the date. I set up a query "Selected General Meeting Roster". The query has 2 criteria...it checks the status column for "Present" and checks the date column for the date of the current form. I think, that is what it is doing....maybe not. It seemed to work ok the first time.

Now the General Meeting Details sheet has a subform... In the subform, there is a form displayed. That form that is displayed was created from the query detailed above. The problem is....if I select another date for a meeting...I want the query to rerun (every time) and then update the form that is inside the subform. So if I open a new date for a meeting...it will rerun the query...check the date of the new meeting and use that for criteria...then update the "Selected General Meeting Roster" form to the new data. So when I open the new meeting...it will show a new roster for those who showed up on THAT date...

And all of that is based on the attendance information I input on the Student list. Hopefully looking at will give you an idea of what I am looking to do...

[RuralGuy]

I'm not ignoring you but your system is not that easy for me to understand. Having the user learn the features of Access *and* your system seems like a steep learning curve for anyone.

[Guinea]

I understand how it may seem a bit confusing. There is a lot of extra content because it was all based on templates. I will clean it up as I go and remove all the unnecessary stuff. But I want to get the core functions working first. The database will ONLY be used by me...so no learning curve is required. I am looking at autofill commands perhaps. Basically the form "Selected General Meeting Roster" needs to have a command of onLoad or onCurrent, that as soon as THAT form is open, it runs the query and updates the records to the results of that query. The query name is Selected General Meeting Roster as well. So when the form loads, the form auto queries the query and updates its contents to the new queries results. That would work perfectly, but I am having trouble finding a proper code to execute that.