Sub forms in a tabbed control - referencing

[Rats]

I am trying to limit the number of records displaying in a subform [amendedLoans] by selecting only those records that have the same ClientID as the mainform [Customers] and the same loan number as the subforms parent form [ClientAccounts loan changes].[loanno].

I have no difficulty selecting records just using the clientid but as soon as I add the reference to the subform [loanno] field I get no selection of records. I suspect there is an error in the where clause for

[Amended Loans].Loan)=[Forms]![customers]![ClientAccounts loan changes].[Form]![LoanNo])

.

I have built the clause using the expression builder and have had many attempts all ending up with the same result. I'm now at a loss as to where to go from here.

SELECT [Amended Loans].AmendedLoanID, [Amended Loans].Loan, [Amended Loans].UpdateID, [Amended Loans].Newloanamount, [Amended Loans].Newpayment, [Amended Loans].NewRate, [Amended Loans].NewPeriod, [Amended Loans].NewLimit, IIf([newperiod]="w",[newpayment]*52/12,IIf([newperiod]="f",[newpayment]*26/12,IIf([newperiod]="m",[newpayment],0))) AS NewMonthAmount, [Amended Loans].from, [Amended Loans].ClientID
FROM [Amended Loans]
WHERE ((([Amended Loans].Loan)=[Forms]![customers]![ClientAccounts loan changes].[Form]![LoanNo]) AND (([Amended Loans].ClientID)=[forms]![customers]![clientid]))
ORDER BY [Amended Loans].UpdateID;

 

[gemma-the-husky]

not wholly sure, but in your forms ref should

[ClientAccounts loan changes] be the actual control name of the subform on the main form, instead of the source object.

look at access help, subforms, referring to in expressions

 

[RuralGuy]

Hi Peter,
To get the reference correct I usually put a test TextBox on the MainForm and set the ControlSource to point to the control I want and then keep playing with the reference until it displays the value I'm looking for. You don't use the Forms!MainFormName part in the reference doing it this way so:
=SubFormControlName.FORM!ControlNameOnTheSubForm
Remember the 1st element of the reference is the SubFormControlName and not the name of the SubForm. They usually default to the same name but it is not a requirement and I usually change the SubFormControl name so I know what I'm referencing.
Correct me if I'm wrong but the way you stated the question leads me to believe this is a SubForm within a SubForm On a MainForm. In other words, a SubForm nested within a SubForm, correct?

Edit: The original subject mentions a Tab Control. When referencing controls it is as if the Tab Control is *not* there. All of the controls on a every page of a Tab Control must have unique names.

 

[Rats]

Thanks RG and GTH for your reply.
Hey RG glad to here from you again. I'm still plugging away at this project. It's pretty much done and I'm now going back over it and making refinements to address user requests but overall it works very well ( thanks to your help along the way). I'll apply what you have both said and see if I can fix this thing. It's been bugging me for a few days now and I just can't seem to nail it down. Yes RG you are right about the structure question- is that an issue at all?
I'll let you know how I go.
Thanks again

Peter

 

[RuralGuy]

Hi Peter,
There is nothing wrong with the structure. I should have made that clearer when I mentioned it. I just wanted to make sure I had the structure right in my mind. Starting to enjoy the approaching summer yet? We've had light snow several times already.

 

[Rats]

Hi Al

Not having a lot of luck here. I think I have something wrong with the data. I set up the textbox as you suggested and the correct number appears. I then add the reference to the query as a criteria and get no records. If I reference another field from the same form using the same syntax I get the desired result for that field.

The field I want to use only contains a number value but has an "indirect relationship?" to another table in which that same field gets its data from a combo box. I think the problem lies there somewhere but it's eluding me at the moment.

Glad to hear your getting some precipitation. We have had the driest winter on record with only about 400mm in lieu of our normal 800mm, so not looking good for the farmers at all.

I'm about to head off caving for the weekend so that should be good.

 

[RuralGuy]

Have fun caving!

You're not using a "LookUp" field are you? The Evils of Lookup Fields in Tables

 

[Rats]

Hi Al
I think that may be the problem. The field I am using in the criteria is not actually a combo box but the data that is in it originally came from one. so I will need to revisit the structure to see how I can get the same info but using a different method.

Cheers

Peter