Running VBA code on opening form BEFORE underlying query is run - How?
Tester
For the "Open" event of a form, the Access 2000 Help file states,
"When you open a form based on an underlying query, Microsoft Access runs
the underlying query for the form before it runs the Open macro or event
procedure. "
How can I avoid this? I say this because I want to run a filter (which is
set from VBA code) before the query is run. This is because if I run the
query all the records from the query will be returned (I believe) and this
is far too many and will slow things down.
I did read before about setting the query of the form to be something that
returns null values, but I find that when I set the recordsource from code I
end up with a recordset that is not updateable.
Any comments appreciated.
Regards
martijn hertog
What would the filter be you want to use? Is it a selected value in a formfield
or the result from another query, etc. etc.? If the filter it is not too
complicated, you can include the filter in the query that is used for the form.
Please let me know, and I will help you with the code.
Regards,
Martijn
Tester
news:3E3543BC...@oracle.com...
> Hi,
>
> What would the filter be you want to use? Is it a selected value in a
formfield
Hi martijn hertog,
Thanks for the prompt reply.
Well actually, the filter I want to initially reply it a filter that returns
no results. This is so that when the user first opens the form, there is
no/very little network traffic. Therefore I want to set the filter to
something that will return no results like REcordID = 0 which I know does
not exist.
Then the user can restrict the search by entering search strings into
unbound text boxes on the form which are used to build a new filter that
will return only matching results.
This way I never waste network traffic by initially sending down every
result when the user is only ever going to be interested in seeing a few
results.
For further details this is how I have (want) it set up,
I have a main search form that has no record source. This contains a number
of unobound text boxes that can be used to build a filter (via vba). I have
two sub forms each on a different page of a tab control.
One subform is a continous list view that is bound to the table/query that I
am interested in. This is a summary version of all the matching records. The
2nd subform is a detailed view of the particular record that uis selected in
the first sub form and this is acheived by setting the record set of the 2nd
subform to that of the first.
Therefore, when I first open the master form, I do not want the 1st sub form
which is bound to my underlying query/table to go off and get every record,
because that defeats the whole purpose of why I set up this master form to
only retrieve the records the user wants.
I did try first setting the record source of the 1st sub form to return null
values for each field and therefore not actually making a call to a query ,
e.g.
Select Top 1 null as name, null as surname etc.
but I found that when I changed the recordsource to the one I wanted
dynammically, I ended up with an uneditable recordsource which meant I was
unable to edit a selected record. (I must say I was a bit confused by this).
Anyway, any suggestions are still welcome. That's a bit more then I intended
to write but hopefully it fully explains what I am trying to acheive.
Regards
Michael (michka) Kaplan
You can set the recordsource and the filter in your code which will now run first.
--
MichKa
This posting is provided "AS IS" with
no warranties, and confers no rights.
"Tester" <No Spam Please> wrote in message news:3e354cf0$1...@news1.homechoice.co.uk...
Steve Jorgensen
work around it by clearing the Record Source property in design view,
then setting it in the form's Open event handler. The query cannot be
run before the form is bound, and it will run as soon as you give it
the RecordSource value.
Tester
news:3ona3v4jd19msdrjp...@4ax.com...
> I believe that behavior only apples to ADPs.
Forgive my ignorance by what is an ADP?
I am at most 2 months old to Access although I was a VB programmer.
David W. Fenton
wrote in <3e35563a$1...@news.microsoft.com>:
>Actually, thye best answer is for there to be NO resordsource.
>Then you code will run right away. You can set the recordsource
>and the filter in your code which will now run first.
Yes, but then you'll have #NAME in the the fields with bound
controlsource properties.
Of course, if you open the form with acHidden and set the actual
recordset in the form's Open event, and reveal it only after that,
then it will be OK.
Another approach is to have an empty recordsource. The way I do
this is to create a TOP 1 query on the smallest table (and,
preferably, a local one) in an application and then have the fields
all be aliased and return NULL:
SELECT TOP 1 Null As PersonID, Null As LastName, Null As
FirstName FROM [your small table];
This returns a single record that is itself non-editable while
leaving the form itself completely editable.
I generally use a constant in the form's module to store this empty
recordsource so that any time I do a find to load a new record and
there's no match I can just load this empty rowsource.
But that's just my approach -- others may not find that useful.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
k y @stanleyassociates.com Stephen K. Young
> SELECT TOP 1 Null As PersonID, Null As LastName, Null As
> FirstName FROM [your small table];
>
> This returns a single record that is itself non-editable while
> leaving the form itself completely editable.
>
all. The following query gives you a single non-editable row, and it works
as a form RecordSource:
Select "A" as A, "B" as B.
- Steve
David W. Fenton
<b144se$va55v$1...@ID-65843.news.dfncis.de>:
Interesting. It's invalid SQL.
Of no use to me, of course, as I don't program in A2K.
Steve Jorgensen
newer. It is designed to be a tight integration between Access and
SQL server and stores many things directly in the back-end rather than
the front. Tables, for instance, are not linked, but rather accessed
directly - the ADP stores nothing about them internally.