Unanswered: Setting Filter or server filter using VBA
I have a 8 .adp applications (Each Application belongs to a unique office). 7 of them have their own local acces DB's and work fine. The 8th one is supposed to be the Uber-all powerful Application, that can see or filter data from the other 7. We have a Merge Replication Scheme set - up with the first 7 as pull subscribers and the 8th (lets call it the HQ Office) as the King publisher.
All the forms in the ADP applications really only interface with 2 tables (caseform and chrono) and a View (Vw_reoport). Each table has a "post_id" field that is automatically set by a function (UDF) based on a look-up table. So basically each office has a different post_id. At the offices all data is local to the office, but once data is merged from the different offices to HQ then what I'll have is 2 tables with consolidated data containing records with different post_id's. Each Table acts as a recordset for a form. so where dealing really with just 2 forms here.
Now the .ADP application of the HQ access file is going to have the capacity to see all data, or filter down and deal with the data of any of the offices. I intend to have a drop down field with each office listed, and when the office is chosen, I would like a filter to be applied to both tables setting Post_id = (whatever dynamic number i choose) and also I would like to alter my database View by adding an additional predicate (e.g. AND Post_id = 5) at the end of the Where Clause, so that the VIEW is dynamically rebuilt. This way the HQ form will now be dealing directly with filtered replicated data from said office. Whew!! hope I explained this well.
Now my question is this:
Is there anyway in VBA to dyanmically apply a filter to the recordsets of the forms (caseform and chrono)?
to what propoerty would this be applied to "Filter" or "Server Filter" what other properties would need to be set? I noticed an "Allow Filters" property and a "Server filter by Form" property.
Also Can I run a DDL Sql statement to alter the View from Access???
I know how to define SQL strings and execute them using "CurrentProject.Connection.Execute (AlterSQL)" where "AlterSQL" can be defined as whatever you want. So for example my code could look like this
__________________________________________________ __________________________________________________ ___
Dim AlterSQL As String
Dim db As New ADODB.Connection
Dim postid as integer