Changing form's query SQL and requerying through VBA not working 2

[Dor100]

I have a main form using a query as data source in Acc XP. Users have design permission on the query. Certain events or conditions determine the query's SQL through VBA. Why is it that when the SQL is changed through VBA, programmatically requerying the form is not working (I've also tried including a querydefs refresh). The only way I can get the form to show the correct data according to the clearly changed and verifiable underlying query's SQL is to include a routine that closes and reopens the form. Thanks to anyone who knows how to avoid that last step and get the requery to do what it should.

 

[PHV]

Perhaps resetting the RecordSource property of the form instead of calling the Requery method ?


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Dor100]

Thanks for your post, PHV. One thing I don't want to do is give design permission on the form itself, otherwise I would do what you've suggested above and have done that in other instances. I consider it important to limit the design permission to only the query.

I've been using a for/next to set the sql, such as the following:

------------
Dim q as querydef

For each q in currentdb.querydefs
if q.name = "The Query"
q.sql = "select yada yada yada..."
exit for
end if
next q

(might throw a querydefs refresh in here, too)

[form_The Form].requery
------------

You can then open the query itself and get the right data, look at the sql and verify that it's correctly reset, but requerying the form fails and still shows the previous sql's data until you close and reopen the form.

I'm wondering if perhaps there is another method to reset the sql that would solve this problem. It would also be good to know what's causing it to begin with. Have others experienced this with XP as well?

 

[JeremyNYC]

Dor,

Why the concern with design permission on the form? This is pretty much "the way it's done" in the professional Access developer world. I'm not even sure (plz take that literally, not as a snide comment) that you need to give folks this permission to make these changes. In any case, if you're concerned with locking down your database, there are plenty of ways to make sure they can't just open the form and futz around with it, but can do things like change recordsource on the fly.

Jeremy

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org

 

[LittleSmudge]

In cases like this I have a local procedure in the Form's code that generates the SQL string and then use form.RecordSource = SQLstring

This can be done in code WITHOUT the current user needing Design permission on the form

Call this local proc on Form_Load and any other occasion where the forms binding needs to be refreshed.

( Ibitially save the form from design mode with the Record Source blank )





'ope-that-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.