subform does update on ms/access

Posted on 2016-11-26
Medium Priority
Last Modified: 2017-01-17
I have a "framing" form where I have a control that, when changed, its contents change the "where" clause of a query. They do, and when I review the query, it is OK - the correct set of records is there. This query is the data source of a  subform, that should show the set of selected records.

However, even though I use the "requery" method:

   strSetSQL = "SELECT tabancasBase.codigoTabanca, tabancasBase.NomeTabanca, tabancasBase.Programa, tabancasBase.habitatTabanca, tabancasDemografia.projPop2014, ticketCA.fechoTicket, regioesGuineBissau.nomeRegia, sectoresGuineBissau.nomeSector, tabancasBase.codigoSector "
    strSetSQL = strSetSQL & "FROM ((regioesGuineBissau INNER JOIN sectoresGuineBissau ON regioesGuineBissau.codigoRegiao = sectoresGuineBissau.codigoRegiao) INNER JOIN (tabancasBase LEFT JOIN ticketCA ON tabancasBase.codigoTabanca = ticketCA.codigoTabanca) ON (sectoresGuineBissau.codigoSector = tabancasBase.codigoSector) AND (regioesGuineBissau.codigoRegiao = tabancasBase.codigoRegiao)) LEFT JOIN tabancasDemografia ON tabancasBase.codigoTabanca = tabancasDemografia.RefTabanca "
    strSetSQL = strSetSQL & " WHERE (((sectoresGuineBissau.codigoRegiao)='"
    strSetSQL = strSetSQL & Me.fld_regiao & "'));"
    Set Q44_selectTabancaDef = sireshDB.QueryDefs("Q44_selectTabanca")
    Q44_selectTabancaDef.SQL = strSetSQL
    Set Q44_selectTabancaSet = sireshDB.OpenRecordset("Q44_selectTabanca", dbOpenDynaset)

can anyone help?
Question by:João serras-pereira
LVL 41

Accepted Solution

PatHartman earned 2000 total points
ID: 41902803
I haven't ever seen this particular technique.  It might be not working because it isn't causing the subform to reload the actual query but merely rerun the existing query that it has in memory.

I use saved querydefs that reference form fields.  That doesn't require the query definition to be resaved each time.

So, the saved querydef would have the following where clause

Where sectoresGuineBissau.codigoRegiao = Forms!yourformname!fld_regiao

Another technique that I know works is to use SQL Strings as the RecordSource.  You would then replace the RecordSource --

Me.mysubformname.Form.RecordSource = strSetSQL

Author Closing Comment

by:João serras-pereira
ID: 41903143
PERFECT solution. Thanks! (I have used the last option as I already had the SQL statement ready....

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question