Closing an Empty Form
in [Queries]
|
From: Steve Schapel on 27 Sep 2006 14:39 FJ, FJquestioner wrote: > The data is such that DEBTORID "13 = TGH" and ASSIGNORID "13 = Lifemed" > and CURRENCYID "1=CAD" will make the query and subforms work fine on their > own. However, when these same values are entered into the main Receipts form > I get nothing hapeening in the subforms. When I run the [Receipts Subform Invoices and Balances] query using the criteria you suggested, I get 11 records, 1 each for 11 different Invoices. However, in none of these 11 records is there anything in the RECEIPTID field. I guess this is because there is no entry in the Receipt Allocations table for any of these 11 Invoices? Well, RECEIPTID is supposed to be the basis of the relationship between the main form and the subform, according to the Link Master Fileds / Link Child Fields properties. So the subform will not show any records. Even if there were linking RECEIPTID data, it seems to me that the subform would (almost?) always only show one record. I am not sure exactly, but there seems to be a fundamental flaw in the logic here somewhere. I tried it like this... 1. Remove the three [Forms]![Receipts]!... criteria from the [Receipts Subform Invoices and Balances] query. 2. Set the subform's Link Master Fileds / Link Child Fields properties to: ASSIGNORID;DEBTORID;CURRENCYID The result of this, of course, is for the subform to show the 11 Receipts Subform Invoices and Balances records whenever the main form contains the matching data. So this applies for example to main form records 25-31. And one record (Invoice 4698) is shown in the subform for all main form records where Debtor is SB and Assignor is Lifemed. I am not sure if this is really what you are after - if so, it seems strange to me to have the subform showing the same records for multiple main form records, but hey - it's your database :-). -- Steve Schapel, Microsoft Access MVP
From: FJquestioner on 27 Sep 2006 19:05 Once again you've bailed me out! I can't believe I missed the obvious error of having the RECEIPTID field as the parent/child relationship!! I had tried doing what you did by using the DEBTORID, ASSIGNORID and CURRENCYID as the parent/child relationship but foolishly I left the RECEIPTID field in there too so naturally it didn't work. So by simply removing the RECIEPTID from the parent/child relationship the form works just fine and most importantly, it no longer crashes !!! I feel like a complete idiot but it sure is nice to have an objective set of eyes looking at things because sometimes its easy to miss the forest for the trees. So thanks once again. BTW, I saw your profile on the MVP webiste and I notice that you live in NZ. What a great place that is! I visited there for 3 weeks back in 2002 while living in Tokyo and absolutely loved it. In fact I made several steps toward seeking immigration papers but in the end decided to return to my homeland of Canada. However, I still think I might end up there some day..... "Steve Schapel" wrote: > FJ, > > FJquestioner wrote: > > The data is such that DEBTORID "13 = TGH" and ASSIGNORID "13 = Lifemed" > > and CURRENCYID "1=CAD" will make the query and subforms work fine on their > > own. However, when these same values are entered into the main Receipts form > > I get nothing hapeening in the subforms. > > When I run the [Receipts Subform Invoices and Balances] query using the > criteria you suggested, I get 11 records, 1 each for 11 different > Invoices. However, in none of these 11 records is there anything in the > RECEIPTID field. I guess this is because there is no entry in the > Receipt Allocations table for any of these 11 Invoices? Well, RECEIPTID > is supposed to be the basis of the relationship between the main form > and the subform, according to the Link Master Fileds / Link Child Fields > properties. So the subform will not show any records. Even if there > were linking RECEIPTID data, it seems to me that the subform would > (almost?) always only show one record. I am not sure exactly, but there > seems to be a fundamental flaw in the logic here somewhere. > > I tried it like this... > 1. Remove the three [Forms]![Receipts]!... criteria from the [Receipts > Subform Invoices and Balances] query. > 2. Set the subform's Link Master Fileds / Link Child Fields properties to: > ASSIGNORID;DEBTORID;CURRENCYID > > The result of this, of course, is for the subform to show the 11 > Receipts Subform Invoices and Balances records whenever the main form > contains the matching data. So this applies for example to main form > records 25-31. And one record (Invoice 4698) is shown in the subform > for all main form records where Debtor is SB and Assignor is Lifemed. I > am not sure if this is really what you are after - if so, it seems > strange to me to have the subform showing the same records for multiple > main form records, but hey - it's your database :-). > > -- > Steve Schapel, Microsoft Access MVP >
From: Steve Schapel on 27 Sep 2006 21:28 Very good, Jeff. Best wishes with the rest of the project. Glad you liked New Zealand. I am actually Australian, but have been living here since 1976. My daughter has just spent a year in Canada (Calgary), and it was a great experience for her. -- Steve Schapel, Microsoft Access MVP FJquestioner wrote: > Once again you've bailed me out! I can't believe I missed the obvious error > of having the RECEIPTID field as the parent/child relationship!! I had tried > doing what you did by using the DEBTORID, ASSIGNORID and CURRENCYID as the > parent/child relationship but foolishly I left the RECEIPTID field in there > too so naturally it didn't work. > > So by simply removing the RECIEPTID from the parent/child relationship the > form works just fine and most importantly, it no longer crashes !!! > > I feel like a complete idiot but it sure is nice to have an objective set of > eyes looking at things because sometimes its easy to miss the forest for the > trees. > > So thanks once again. > > BTW, I saw your profile on the MVP webiste and I notice that you live in NZ. > What a great place that is! I visited there for 3 weeks back in 2002 while > living in Tokyo and absolutely loved it. In fact I made several steps toward > seeking immigration papers but in the end decided to return to my homeland of > Canada. However, I still think I might end up there some day..... >
From: FJquestioner on 3 Oct 2006 16:28 Steve, On the bottom of my Receipts form I have a button which opens a different form called NFI. I'd like to create a macro such that each time the NFI form is closed, it triggers a requery of the query underlying the Receipts subform. However, a macro that is triggered by the close of the NFI form will only manipulate fields within that same form. How would I set this up? Thanks, Jeff "Steve Schapel" wrote: > Very good, Jeff. Best wishes with the rest of the project. > > Glad you liked New Zealand. I am actually Australian, but have been > living here since 1976. My daughter has just spent a year in Canada > (Calgary), and it was a great experience for her. > > -- > Steve Schapel, Microsoft Access MVP > > FJquestioner wrote: > > Once again you've bailed me out! I can't believe I missed the obvious error > > of having the RECEIPTID field as the parent/child relationship!! I had tried > > doing what you did by using the DEBTORID, ASSIGNORID and CURRENCYID as the > > parent/child relationship but foolishly I left the RECEIPTID field in there > > too so naturally it didn't work. > > > > So by simply removing the RECIEPTID from the parent/child relationship the > > form works just fine and most importantly, it no longer crashes !!! > > > > I feel like a complete idiot but it sure is nice to have an objective set of > > eyes looking at things because sometimes its easy to miss the forest for the > > trees. > > > > So thanks once again. > > > > BTW, I saw your profile on the MVP webiste and I notice that you live in NZ. > > What a great place that is! I visited there for 3 weeks back in 2002 while > > living in Tokyo and absolutely loved it. In fact I made several steps toward > > seeking immigration papers but in the end decided to return to my homeland of > > Canada. However, I still think I might end up there some day..... > > >
From: Steve Schapel on 8 Oct 2006 02:10
Jeff, That is not correct. You can specify anything you like in the Control Name argument of the Requery action, for example [Forms]![Receipts]![NameOfSubform] -- Steve Schapel, Microsoft Access MVP FJquestioner wrote: > Steve, > > On the bottom of my Receipts form I have a button which opens a different > form called NFI. I'd like to create a macro such that each time the NFI form > is closed, it triggers a requery of the query underlying the Receipts subform. > > However, a macro that is triggered by the close of the NFI form will only > manipulate fields within that same form. > > How would I set this up? > |