423,103 Members | 1,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,103 IT Pros & Developers. It's quick & easy.

My subform is not requerying

BHo15
100+
P: 132
If have a form with several controls on it, and a subform. When I use the control to choose date and strategy, and then hit a button, the query's sql adjusts properly, and the subform requeries great, and all is well.

BUT... When I select an option group instead, the query's sql DOES update properly, but the subform does not requery. I can't figure out why.

Here is the code


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdObtainHolding_Click()
  2.     ‘This is working as expected
  3.     CurrentDB.QueryDefs(“qry_Trades_Sum”).SQL = “SELECT Sum(tbl_Trades.Change_Amount) as SumOfChange_Amount, tbl_Trades.ID_Symbol_t FROM tbl_Trades WHERE (((tbl_Trades.Trade_Date) <= [Forms]![frm_Trades_View_Holder]![cboTradeDateSearch]) And ((tbl_Trades.ID_PIA_Strategy_t) = [Forms]![frm_Trades_View_Holder]![cboInvStratSearch])) GROUP BY tbl_Trades.ID_Symbol_t”
  4.  
  5.     Me.frm_Trades_Sum_Agg.Form.Requery
  6. End Sub
  7.  
  8. Private Sub optInvestmentStrategySelect_AfterUpdate()
  9.     ‘Does not requery the sub form as expected
  10.     CurrentDB.QueryDefs(“qry_Trades_Sum”).SQL = “SELECT Sum(tbl_Trades.Change_Amount) as SumOfChange_Amount, tbl_Trades.ID_Symbol_t FROM tbl_Trades WHERE ((tbl_Trades.ID_PIA_Strategy_t) = “ & 28 & “) GROUP BY tbl_Trades.ID_Symbol_t”
  11.  
  12.     Me.frm_Trades_Sum_Agg.Form.Requery
  13. End Sub
  14.  
Jun 9 '18 #1
Share this Question
Share on Google+
14 Replies


BHo15
100+
P: 132
I will say the option group code is simplified, in that there is not a Select Case to determine which option was selected. I just wanted to get it working to requery the form before building the Select Case.
Jun 9 '18 #2

PhilOfWalton
Expert 100+
P: 1,256
How is the subform related to the form?

Are there any LinkMasteFields & LinkChildFiels set?

Phil
Jun 9 '18 #3

NeoPa
Expert Mod 15k+
P: 30,909
You've posted code that can't possibly run. Probably because you've done something to it in a word processor. Please post the exact code so that we need not waste time on looking at problems that don't exist in your actual project.

Before you repost though, you may want to add lines that help you check that your procedures are actually triggered and run. The Requery code looks fine in both.
Jun 9 '18 #4

BHo15
100+
P: 132
My bad NeoPa. I got the code from a client's machine, and I just did a screen print of it and then retyped it. So unfortunately I can't just post the original code.

BUT... Here it is again. I put it in Notepad++ and cleaned it up, and had my wife help me proof read it.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdObtainHolding_Click()
  2. ‘This is working as expected
  3.     CurrentDB.QueryDefs(“qry_Trades_Sum”).SQL = “SELECT Sum(tbl_Trades.Change_Amount) as SumOfChange_Amount, tbl_Trades.ID_Symbol_t FROM tbl_Trades WHERE (((tbl_Trades.Trade_Date) <= [Forms]![frm_Trades_View_Holder]![cboTradeDateSearch]) And ((tbl_Trades.ID_PIA_Strategy_t) = [Forms]![frm_Trades_View_Holder]![cboInvStratSearch])) GROUP BY tbl_Trades.ID_Symbol_t”
  4.  
  5.     Me.frm_Trades_Sum_Agg.Form.Requery
  6. End Sub
  7.  
  8. Private Sub optInvestmentStrategySelect_AfterUpdate()
  9. ‘Does not requery the sub form as expected
  10.     CurrentDB.QueryDefs(“qry_Trades_Sum”).SQL = “SELECT Sum(tbl_Trades.Change_Amount) as SumOfChange_Amount, tbl_Trades.ID_Symbol_t FROM tbl_Trades WHERE ((tbl_Trades.ID_PIA_Strategy_t) = “ & 28 & “) GROUP BY tbl_Trades.ID_Symbol_t”
  11.  
  12.     Me.frm_Trades_Sum_Agg.Form.Requery
  13. End Sub
I will also say that the functional problem is no longer because I just wrote code to close the form and reopen it, and when it opened, the subform was correct. But... That doesn't explain why the requery was not working. Very odd.
Jun 10 '18 #5

NeoPa
Expert Mod 15k+
P: 30,909
I have to smile. Very good reason for not copying directly ;-) And apologies if I put you to a lot of further trouble. There are occasions where Copy/Paste can't be done and that's perfectly acceptable. In this case the clue is in the quotes (' & "). Notice that in your code they're slanted, as you often see in ordinary text. In VBA they're always straight down, as you'll see with the proper ones when viewed in a [CODE] window. The slanted ones won't work in VBA. If you try to compile that code it will fail. Just something to be aware of.

Anyway, I suspect that the reason your .Requery() failed to work was because, at the time, the Event had not been linked with the code so no code was actually run in those circumstances. If you still had that version then you could check the properties for that Event and see if it was set. If it were running then you might need to use a breakpoint (Debugging in VBA) in the code to monitor it as it runs.
Jun 10 '18 #6

BHo15
100+
P: 132
Good thought, but I actuallly did breaks several times. I saw clearly that it was updating the query SQL code. It also complied very nicely.

Oh well, I’ll just chalk this one up to the unexplained. At least closing and reopening the form worked.

Thanks.
Jun 10 '18 #7

BHo15
100+
P: 132
BTW Phil... I never answered your question. The parent form is unbound.
Jun 10 '18 #8

PhilOfWalton
Expert 100+
P: 1,256
The question I asked was about the Subform

Phil
Jun 10 '18 #9

BHo15
100+
P: 132
Yes sir. The parent is unbound, and the child is a bound datasheet. It is the bound datasheet that is not requerying.
Jun 10 '18 #10

PhilOfWalton
Expert 100+
P: 1,256
Sorry, still not getting the information I need.

I need to know whether there any values in LinkMasteFields & LinkChildFields.

To illustrate what I mean, please see below.



You will see there are values of Kontact_ID and Kontakt_ID_f.
It is my opinion that they should both be blank for your subform.
This will mean that as you move from record to record on your main form, the data in your Subform won't change. Is this what should happen?

Should the Subform only change as you change the Date or Strategy or Option?

Phil
Jun 10 '18 #11

BHo15
100+
P: 132
Gotcha. Both of the link fields are blank. The parent form has no data (only controls).
Jun 10 '18 #12

PhilOfWalton
Expert 100+
P: 1,256
Great, hard work, but we got there in the end.

So forget anything about QueryDefs, they are not wanted.

You have posted 2 SQLs Do they both give the results you are looking for, if so, you need to set the RecordSource of the subform to the SQL

Use something like
Expand|Select|Wrap|Line Numbers
  1. StrSQL = "SELECT Sum(tbl_Trades.Change_Amount) as SumOfChange_Amount, tbl_Trades.ID_Symbol_t FROM tbl_Trades WHERE (((tbl_Trades.Trade_Date) <= [Forms]![frm_Trades_View_Holder]![cboTradeDateSearch]) And ((tbl_Trades.ID_PIA_Strategy_t) = [Forms]![frm_Trades_View_Holder]![cboInvStratSearch])) GROUP BY tbl_Trades.ID_Symbol_t"
  2.  
  3. Me!SubformName.Form.RecordSource = StrSQL
  4.  
  5.  
The next question I must ask is why you are using a main form with apparently a few fields to enter parameters and a datasheet Subform.
My preference would be to have NO main form and a continuous Form (frm_Trades_Sum_Agg.Form) with the fields that were on your main form in the header or footer of your new continuous form.

By and large, you can do a lot more with formatting and controling continuous subforms than datasheets.

Let me know how you get on.

Phil
Jun 11 '18 #13

BHo15
100+
P: 132
My client LOVES datasheet forms and wants no part of standard or continuos forms. So to be able to use controls (text boxes, combo boxes, list boxes, etc) we put them on a parent form.

Your idea do form.recordsource is great, but in this case it was a supporting query to the recordsource that needed to be adjusted, and not the recordsource query itself.

Thanks.
Jun 11 '18 #14

PhilOfWalton
Expert 100+
P: 1,256
I wouldn't dare be rude about your client's tastes. Obviously hasn't moved on from Excel.

Without the full details of your recordsource, it is difficult to give further help. However there are loads of articles on creating Queries with SubQueries which is situation that you have.
It would appear that you have already created the subquery.

Create the SQL and make that the recordsource for your form.

Phil
Jun 11 '18 #15

Post your reply

Sign in to post your reply or Sign up for a free account.