Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

subform does update on ms/access

Posted on 2016-11-26
2
Medium Priority
?
66 Views
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)
   
    Me.[sform_listaTabancas].Form.Requery


can anyone help?
0
Comment
Question by:João serras-pereira
2 Comments
 
LVL 40

Accepted Solution

by:
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
0
 

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....
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

581 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