Refresh button not working

[vikasmbmkankani]

HI,
When i am clicking on refresh button the query which is generated by drop downs is not updating in the form, but it was updating in the query table. How can i refresh the query on the form where drop downs are..:banghead:
my code is
forms![Opening]![RepQuery].Requery
where Opening is my form name, repquery is the query which i generated in form.
please help

 

[CJ_London]

Can you supply the SQL in your query - if not sure how to get it, open the query is design view and select SQL in the dropdown on the top right

 

[vikasmbmkankani]

hey CJ_london thanks for your response..i am able to see query in sql mode..
even i wrote all my query in vba and linked it with dynamic drop down combo boxes. Queries are updating with the drop down changes and getting the results in query table..but i want to publish this table in subform which is part of my form where my dynamic drop down box and refresh button are..how can i link these updating query with subform using refresh button?

 

[CJ_London]

Providing your query always produces the same recordset (ie. reports the same fields) which sounds like it is and your subform is using these same fields then do the following:

generate your query SQLstring as you are doing and then in the afterupdate of the refresh button enter (there is no need to save the query

Subformname.Form.RecordSource=SQLstring

This is the second thread on this subject today - you guys doing an exam or something?

 

[JHB]

Is the query already in the form's recordsource?
If yes, the try Me.Requery/Me.Refresh instead of "forms![Opening]![RepQuery].Requery"

 

[CJ_London]

Hi JHB,

Agree that will work - providing all the criteria in the query refer back to the form and are not 'hardwritten'

 

[vikasmbmkankani]

thanks for you reply CJ_London..
your code is working if the report fields are same..but we have dynamic report field and for that field it doesn't change the filed name and leave that field value as empty..

 

[vikasmbmkankani]

Hi JHB,
it is not updating dynamic query (using dynamic drop downs) in the form. I tried it but it leaves the column field name unchanged and empty value field of same column

 

[CJ_London]

but we have dynamic report field and for that field it doesn't change the filed name and leave that field value as empty..

Trying to understand - are you saying that one version of the query has a fieldX and and another version has a fieldY

 

[vikasmbmkankani]

Yes CJ_London..
even some version have 8 fields and for some query it can be 14 fields also..for that i want to update..

 

[CJ_London]

I think you are working on the same task as another thread

http://www.access-programmers.co.uk/forums/showthread.php?p=1240455&posted=1#post1240455

If so, please combine resources.

I will continue on this other link

 

[vikasmbmkankani]

yes CJ we are on the same task and please continue here..

 

[RainLover]

This is clearly a Double post.

Do you want us to do double the work.

 

[vikasmbmkankani]

Hi rain lover,
we don't want it..so do reply on this thread..we are looking for your precious answers here only..

 

[CJ_London]

OK I'll continue here

If the number of columns which could appear in your subform is finite, i.e. all the queries are based on the same base dataset then there is one solution. If not there is another more complex solution

Assuming the former do the following:

1. ensure your subform has a control for all possible fields which could appear.

2. in your beforeupdate event on your refresh button add the following Dims
Dim Ctrl as Control
Dim Fld as Field

3. After the Subformname.Form.RecordSource=SQLstring line enter the following

With subformname.form
    For each ctrl in .controls
        ctrl.columnhidden =true
        for each fld in .recordset
            if ctrl.name=fld.name then ctrl.columnhidden=false
        next fld
    next ctrl
end with

If on the other hand you do not have a finite set of fields to show then the only way is to build a new form (which can be done in vba) and then change subformname.sourceobject to the new form. The only problem with this is that if you are going to save your project as an accde then you cannot build new forms

 

[vikasmbmkankani]

thnks for your response CJ_LONDON..we will try it out

 

[vikasmbmkankani]

CJ_London- meanwhile can u help me in chart of access
on x-axis we have some time- % as variable and some time large number with comma formats.. how can we change it dynamically as it is also linked with query..

 

[RainLover]

Have you closed the other threadd or is it still open for somome else to waste their time on.

 

[vikasmbmkankani]

we posted a comment there this thread is closed now, do not reply on it..can you please help me on this now @Rain lover

 

[CJ_London]

Please can you start the chart question on new thread (just one)