Move field from main to subform

[rpbsr]

I have a main form (Members) and a subform (CourseRegistration). A query
computes a discount based on Income and PeopleNumber using tblDiscounts.
PeopleNumber and Income are fields in tblMembers. The following expression
works to output the discount to a textbox in the Members form:

=DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" & [PeopleNumber] & "
And " & [Income] & "<[MaxIncome]")

I would like to have the discount appear in the subform instead. I have
tried an expression I read somewhere to move it:

[Members].Form![Discount]

But this returns a #Name? error. I want to have the discount appear in the
subform and to use it to compute the actual cost.

Any help will be appreciated.

 

[rpbsr]

Bonnie,

The source of the subform is a table tblCourseRegistration, which is related
to tblMembers and tblCourses. The subform will be used to register clients
for courses and calculate tuition.

What is the data source on the subform?

Bonnie
http://www.dataplus-svc.com

I have a main form (Members) and a subform (CourseRegistration). A query
computes a discount based on Income and PeopleNumber using tblDiscounts.
PeopleNumber and Income are fields in tblMembers. The following expression
works to output the discount to a textbox in the Members form:

=DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" & [PeopleNumber] & "
And " & [Income] & "<[MaxIncome]")

I would like to have the discount appear in the subform instead. I have
tried an expression I read somewhere to move it:

[Members].Form![Discount]

But this returns a #Name? error. I want to have the discount appear in the
subform and to use it to compute the actual cost.

Any help will be appreciated.

 

[rpbsr]

That didn't work either. In the subform I added unbound forms I used:

Forms![Members].[PeopleNumber] OR
Form![Members].[PeopleNumber]

Forms![Members].[Income] OR
Form![Members].[Income]

I even tried this variant:

[Members].Form![PeopleNumber], etc.

All with the same result in the subform: #Name?

How about just adding an unbound control to your subform with the same
DLOOKUP but add the full reference to the Members form where needed.

Forms![Members].[PeopleNumber], etc.


Bonnie
http://www.dataplus-svc.com

Bonnie,

The source of the subform is a table tblCourseRegistration, which is related
to tblMembers and tblCourses. The subform will be used to register clients
for courses and calculate tuition.

What is the data source on the subform?

[quoted text clipped - 18 lines]

Any help will be appreciated.

 

[Ken Sheridan]

You should be able to reference the controls on the parent form in the
subform with:

Parent("PeopleNumber")
Parent("Income")

so the full expression would be:

DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" &
Parent("PeopleNumber") & " And " & Parent("Income") & "<[MaxIncome]")

If you are then using the returned value in an arithmetical expression to
compute the net cost you'll need to use the full expression. You can't use
the above expression as the ControlSource property of a control, and then
reference that control in another expression. So the net cost would be
something like this if the discount is expressed as a currency value:

=[GrossCost] - DLookUp("DiscountAmt", "qryDiscounts", "PeopleNumber = " &
Parent("PeopleNumber") & " And " & Parent("Income") & " < [MaxIncome]")

Or if the discount is expressed as a fractional value e.g. 0.1 (i.e. I0%)

=[GrossCost] * (1 - DLookUp("DiscountAmt", "qryDiscounts", "PeopleNumber =
" & Parent("PeopleNumber") & " And " & Parent("Income") & " < [MaxIncome]"))

Ken Sheridan
Stafford, England

That didn't work either. In the subform I added unbound forms I used:

Forms![Members].[PeopleNumber] OR
Form![Members].[PeopleNumber]

Forms![Members].[Income] OR
Form![Members].[Income]

I even tried this variant:

[Members].Form![PeopleNumber], etc.

All with the same result in the subform: #Name?

How about just adding an unbound control to your subform with the same
DLOOKUP but add the full reference to the Members form where needed.

Forms![Members].[PeopleNumber], etc.


Bonnie
http://www.dataplus-svc.com

Bonnie,

The source of the subform is a table tblCourseRegistration, which is related
to tblMembers and tblCourses. The subform will be used to register clients
for courses and calculate tuition.

What is the data source on the subform?

[quoted text clipped - 18 lines]

Any help will be appreciated.

 

[rpbsr]

Ken,
It still isn't working out for me. For "Parent" I'm using the name of the
main form, "Members".

DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" &
Members("PeopleNumber") & " And " & Members("Income") & "<[MaxIncome]")


I've tried it with and without the unbound fields:
=Forms!Members.PeopleNumber
=Forms!Members.Income

What am I missing?

You should be able to reference the controls on the parent form in the
subform with:

Parent("PeopleNumber")
Parent("Income")

so the full expression would be:

DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" &
Parent("PeopleNumber") & " And " & Parent("Income") & "<[MaxIncome]")

If you are then using the returned value in an arithmetical expression to
compute the net cost you'll need to use the full expression. You can't use
the above expression as the ControlSource property of a control, and then
reference that control in another expression. So the net cost would be
something like this if the discount is expressed as a currency value:

=[GrossCost] - DLookUp("DiscountAmt", "qryDiscounts", "PeopleNumber = " &
Parent("PeopleNumber") & " And " & Parent("Income") & " < [MaxIncome]")

Or if the discount is expressed as a fractional value e.g. 0.1 (i.e. I0%)

=[GrossCost] * (1 - DLookUp("DiscountAmt", "qryDiscounts", "PeopleNumber =
" & Parent("PeopleNumber") & " And " & Parent("Income") & " < [MaxIncome]"))

Ken Sheridan
Stafford, England

That didn't work either. In the subform I added unbound forms I used:

Forms![Members].[PeopleNumber] OR
Form![Members].[PeopleNumber]

Forms![Members].[Income] OR
Form![Members].[Income]

I even tried this variant:

[Members].Form![PeopleNumber], etc.

All with the same result in the subform: #Name?

How about just adding an unbound control to your subform with the same
DLOOKUP but add the full reference to the Members form where needed.

Forms![Members].[PeopleNumber], etc.


Bonnie
http://www.dataplus-svc.com

rbsr wrote:
Bonnie,

The source of the subform is a table tblCourseRegistration, which is related
to tblMembers and tblCourses. The subform will be used to register clients
for courses and calculate tuition.

What is the data source on the subform?

[quoted text clipped - 18 lines]

Any help will be appreciated.

 

[John W. Vinson]

It still isn't working out for me. For "Parent" I'm using the name of the
main form, "Members".

That's why you're having the problem. Parent is a shortcut for "the parent
form of this current form, whatever that might be" - just as Me! means "this
current form, whatever its name is".

 

[rpbsr]

Duh! Of course I didn't think to take Parent literally - neat!
Thanks Bonnie, Ken, and John.