473,322 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

My subform is not requerying

BHo15
143 128KB
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
14 3981
BHo15
143 128KB
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
1,430 Expert 1GB
How is the subform related to the form?

Are there any LinkMasteFields & LinkChildFiels set?

Phil
Jun 9 '18 #3
NeoPa
32,556 Expert Mod 16PB
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
143 128KB
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
32,556 Expert Mod 16PB
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
143 128KB
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
143 128KB
BTW Phil... I never answered your question. The parent form is unbound.
Jun 10 '18 #8
PhilOfWalton
1,430 Expert 1GB
The question I asked was about the Subform

Phil
Jun 10 '18 #9
BHo15
143 128KB
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
1,430 Expert 1GB
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
143 128KB
Gotcha. Both of the link fields are blank. The parent form has no data (only controls).
Jun 10 '18 #12
PhilOfWalton
1,430 Expert 1GB
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
143 128KB
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
1,430 Expert 1GB
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

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

Similar topics

1
by: Txsg8r | last post by:
I have a main form with a subform on it. When I click a button on the main form, I perform some calculations on data contained in the subform's table (all related records actually). However,...
0
by: Ellen Manning | last post by:
I've got an A2K form with a subform based on a query. This query has a checkbox and an amount field and returns records if checkbox is checked. I Dsum the amount field and display on the main...
0
by: Jenni | last post by:
I currently have a query that is joining two distinct pools of information and turning them into one records with a unique identifier This query is dependent on criteria passed from a form in a...
1
by: Alienz | last post by:
Alien hello to whoever is reading today. Ill try to explain this as simply as possible its just a weird thing thats happening here in Access 2000 subforms.. I think something small is out of...
12
by: MLH | last post by:
I have created two forms: frmBrowseNegsMainform and frmBrowseNegsSubform. I put a subform control on the first of these. The SourceObject property for the subform control is, of course,...
4
by: mooseshoes | last post by:
All: Form A: Main_Form Form B: Sub_Form (has the control name: Sub_Form_Control_Name) Form C: Independent_Form Form C is based on a table which is also used by Form B. When Form C is used...
2
by: Melissa | last post by:
I have a single (not continuous) form with an Undo button for entering finished projects. On the form is also a subform that lists all finished projects for reference. When I enter the...
9
by: Tim | last post by:
Hi, has anyone experienced a spontaneous loss of a subform bookmark (i.e. resetting to the first record), when the computer is idle. I used to have a problem in NT4 when doing a couple of...
2
by: David W. Fenton | last post by:
I think at various times we've all encountered this problem: A subform is on a main form. From the code of the main form we refer to some property of/control on the child form thus: ...
5
by: ApexData | last post by:
My popup form can reference the MAINFORM by using either line of the code shown below: --------------------------------------------------------------------------------...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.