lookup calculation value from a different form

[Guest]

Hi. I am building a database for my new businses.

I have the client form ("Client")with a subform called "Request Account
Subform" that adds the price (Price) of all the requests and has a sum to
calculate the total (PriceTotal), in the subform footer. The client form then
pulls the "PriceTotal" figure to show as the total for that client (Account
Balance).

I am then doing another form (Request Form) which is where I want to be able
to put in the membernumber and it show the "accountbalance" figure from the
client form.

I have a text box open in the request form. I can get it to work if i type
the membernumber in the expression, like this:

=DSum("[Price]","[Request]","[MemberNumber] = '1234'")

But i want it to be able to locate the member number that i have entered
into the form and only pull the data relevant for a certain member number
(eg. so i dont define it). I have it like this:

=DSum("[Price]","[Request]","[MemberNumber] =" & Forms![Request
Form]!MemberNumber)

But it shows #Error and i've tried everything i can think of and i cant
figure it out.

If you could offer me any help - i would really really appreciate it!!

Thanks so much (in advance )

Selina.

 

[Allen Browne]

You put quotes around the number in the example that works.
This implies that MemberNumber is a Text field (not a Number field.)

Add the quotes like this:
=DSum("[Price]","[Request]", "[MemberNumber] =""" &
Forms![Request Form]!MemberNumber & """")

If that doesn't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

 

[Marshall Barton]

Hi. I am building a database for my new businses.

I have the client form ("Client")with a subform called "Request Account
Subform" that adds the price (Price) of all the requests and has a sum to
calculate the total (PriceTotal), in the subform footer. The client form then
pulls the "PriceTotal" figure to show as the total for that client (Account
Balance).

I am then doing another form (Request Form) which is where I want to be able
to put in the membernumber and it show the "accountbalance" figure from the
client form.

I have a text box open in the request form. I can get it to work if i type
the membernumber in the expression, like this:

=DSum("[Price]","[Request]","[MemberNumber] = '1234'")

But i want it to be able to locate the member number that i have entered
into the form and only pull the data relevant for a certain member number
(eg. so i dont define it). I have it like this:

=DSum("[Price]","[Request]","[MemberNumber] =" & Forms![Request
Form]!MemberNumber)

But it shows #Error and i've tried everything i can think of and i cant
figure it out.

If the MemberNumber field in the Request table is a text
type field, then the criteria value must be enclosed in
quotes, as you have in the one that works. If that's the
problem, you can write the expression this way:

=DSum("[Price]","[Request]", "[MemberNumber]=""" _
& Forms![Request Form]!MemberNumber & """")

 

[Guest]

That worked!

Thanks so both of you for helping me out! I really appreciate it!

Hi. I am building a database for my new businses.

I have the client form ("Client")with a subform called "Request Account
Subform" that adds the price (Price) of all the requests and has a sum to
calculate the total (PriceTotal), in the subform footer. The client form then
pulls the "PriceTotal" figure to show as the total for that client (Account
Balance).

I am then doing another form (Request Form) which is where I want to be able
to put in the membernumber and it show the "accountbalance" figure from the
client form.

I have a text box open in the request form. I can get it to work if i type
the membernumber in the expression, like this:

=DSum("[Price]","[Request]","[MemberNumber] = '1234'")

But i want it to be able to locate the member number that i have entered
into the form and only pull the data relevant for a certain member number
(eg. so i dont define it). I have it like this:

=DSum("[Price]","[Request]","[MemberNumber] =" & Forms![Request
Form]!MemberNumber)

But it shows #Error and i've tried everything i can think of and i cant
figure it out.

If the MemberNumber field in the Request table is a text
type field, then the criteria value must be enclosed in
quotes, as you have in the one that works. If that's the
problem, you can write the expression this way:

=DSum("[Price]","[Request]", "[MemberNumber]=""" _
& Forms![Request Form]!MemberNumber & """")