Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Refering to a control on another form

Status
Not open for further replies.

BCre8iv

Programmer
Joined
May 21, 2002
Messages
28
Location
CA
I have a products form where my control source for MSRP is as follows.

=IIf(IsNull([MSRPUSD]),[MSRPCDD],([MSRPUSD]*Forms![Conversion Rate]!FieldValue))

This works great and gives me exactly what I want, mathematically, except for the fact that the Conversion Rate form has to be open. Is there anyway that I can get the MSRP field on my Products Form to do the above without having to have the Conversion Rate Form open? Any help would be greatly appreciated.
Thx in advance.
Tina
 
You're asking the formula to use the current value in a form, so I don't believe you can have it closed. Without knowing exactly what you're trying to do, I would suggest find a way to use the form's table field value instead; or, if there is any open form at the time the time your code executes, come up with a creative way to deposit the closed form's value into perhaps an unbound text box on the open form, and then change your code to work with that value.
 
When I have tired to use the table field value I was getting a #name? error. This is what I was using.
=IIf(IsNull([MSRPUSD]),[MSRPCDD],([MSRPUSD]*[FieldValue]))
MSRPUSD and MSRPCDD are two fields available to the user, either one will always have an amount in them. MSRP is the final amount which is two draw one of the other two into it. However if the MSRPUSD is used than that amount needs to be converted using the rate of exchange that the user inputs into the Conversion Rate Form. does that help you to better understand what I am trying to do?
Tina
 
I'm wondering why you need to have the other form closed.

If you can keep it open but only want it to 'disappear,' you can enter a VBA event procedure for the Conversion Rate field on After_Update, such as:

[form_ProductsFormName].setfocus (if your prod. form is already open)

or:

[form_ProductsFormName].open
[form_ProductsFormName].MSRP.setfocus
[form_ProductsFormName].MSRP.requery(I'm pretty sure you can write this one, or at least a variant of it such as:
[form_ProductsFormName].requery (should work)

By the way, the syntax above for referring to forms &
their field values will change your life if you don't already use it (in code). The other "Forms!" way is a 100 times less useful. The "dot" gives you a drop-down menu of options.
 
Oops! If you need to open the products forms, you'd be better off with this:

DoCmd.OpenForm "ProductsFormName"
 
Gotta go now. Hope I've helped a bit. Wish I could continue, but perhaps tomorrow.

--Dor100
 
thx i will try those...and we just might continue tomorrow
Later
 
Check out the DLookup function in help. Sounds like that is what you are looking for.
 
Thx for the help...it is working gr8.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top