Code works on Form, just not when form is a subform 1

[MightyRoo]

Hello all,
I would sure appreciate some help with this.

Here is my current code

 Me!UnitPrice = DLookup("Price", "TblPricesWoodBlinds", "NominalSize = forms![subfrmLineItemsVBFR ---Wood Blinds---]!NominalSize")

The code works when I am opening the
"subfrmLineItemsVBFR ---Wood Blinds---"
as the only form, no problems at all

When the form "frmworkorders" is used which has the
"subfrmLineItemsVBFR ---Wood Blinds---"
I produce an error like "The object doesn't contain the automation object"

Ideally I need a way of not referring to the "frmworkorders" as the subform is used on many forms

I really appreciate the help, I am in over my head!
Thanks!
Scott

 

[TheAceMan1]

How are ya SierraBlind . . .

. . . and ths:

[blue]Me!UnitPrice = DLookup("Price", "TblPricesWoodBlinds", "NominalSize = Forms!frmWorkOrders![subfrmLineItemsVBFR ---Wood Blinds---].Form!NominalSize")[/blue]

See Ya! . . . . . .

 

[MightyRoo]

AceMan,
Thanks for the input! Is there a way of doing this without referencing the frmworkorders? The problem is if I reference this in the code on the subform I will get an error if the subform is opened with another form (not frmworkorders).
Thanks again,
Scott

 

[TheAceMan1]

SierraBlind . . .

However you do it, your goal is to substitute the mainform in [purple]purple[/purple]:

[blue]Me!UnitPrice = DLookup("Price", "TblPricesWoodBlinds", "NominalSize = Forms![purple][b]MainFormName[/b][/purple]![subfrmLineItemsVBFR ---Wood Blinds---].Form!NominalSize")[/blue]

See Ya! . . . . . .

 

[MightyRoo]

Aceman1,
I guess I must not be seeing the solutions. Since the code is in a subform that is used by multiple forms
FrmWorkOrders
FrmWorkOrders104
FrmWorkOrders105 etc...
All use this subform

I am trying to see if there is a way of doing this without refrencing the frmworkorders. Since, when the subform is used on FrmWorkOrders104 the code would be looking for the FrmWorkOrders.

Unfortuntely the code above won't do it since the code is part of the subform and the subform doesn't change the code based on the form that is showing it.

The only way I can see of doing it is by having multiple subforms that each refrence the mainform. This will get really messy.

Is there a better way?
Thanks!
Scott

 

[TheAceMan1]

SierraBlind . . .

Wow! . . . I'm not even gonna ask about [blue]table relationships[/blue], let alone the purpose of the extensively long subform name!

Be aware: a subform on a mainform is not a form. Its a control! And the only way to reference it is thru the mainform!

In any case you need to decipher the mainform name and return the value of DLookUp in a function. So in the code module of [blue]subfrmLineItemsVBFR ---Wood Blinds---][/blue], copy/paste the following function:

[blue]Public Function UPrice()
   Dim Criteria As String
   
   Criteria = "[NominalSize] = Forms(" & Me.Parent.Name & ")([subfrmLineItemsVBFR ---Wood Blinds---]).form!NominalSize"
   UPrice = DLookup("Price", "TblPricesWoodBlinds", Criteria)
 
 End Function[/blue]

Then set your unbound textbox to the function:

[blue]   Me!UnitPrice = UPrice[/blue]

See Ya! . . . . . .

 

[danvlas]

Assuming UnitPrice lies on any form that has the subform named in the same way, the below formula should work.

Me!UnitPrice = DLookup("Price", "TblPricesWoodBlinds", "NominalSize = " & [SubformName].Form![ControlName])

But...are you sure there is no other way to get the unit price except DLookup?


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[MightyRoo]

Thanks again for the help
Aceman1,
The code that you supplied looks like what I need. I copied and pasted in and I get the following error when it is running.

"Invalid used of '.','!', or '()'. In query expressions '[Nominalsize]=Forms(frmworkorders)([subfrmlineitemsvbfr ---wood blinds---]).form!NominalSize'.

You have me going in the right direction, I just can't figure out what is going on.

As far as your comments on the database. I really have a mess going, the original programmer lost intrest in the project and I have taken over. With a number of books and the web, I am just plugging away. I am sure it is not up to standards and as I gather more information I am debating fixing/starting fresh.


Daniel,
Thanks for the input. I think Acerman has got me in the right direction since the event is trigered on the subform.

As far as the Dlookup, All of our prices are based on grids. A 46.5 X 47" blind uses the same price as a 44x48" blind. The price of a 46.5 X 42 blind is different. So I have the code using nested if statements to get me a nominal width and length. I then take the two and combine them to form "48B48". Then the dlookup finds the price for a 48x48 on the as it would be on the price grids. The only options I came up with other then the d/e lookup were to place the pricing in my code which would be a pain to update. Do you have any other recomendations?

Thanks all!
Scott

 

[TheAceMan1]

and this:

[blue] Criteria = "[NominalSize] = Forms!" & Screen.ActiveForm.Name & "![subfrmLineItemsVBFR ---Wood Blinds---].form!NominalSize"[/blue]

See Ya! . . . . . .

 

[MightyRoo]

Works great! Thank you so much!
Thanks!
Scott

 

[PHV]

Since the code is in a subform
Why not simply this ?[tt]
Me!UnitPrice = DLookup("Price", "TblPricesWoodBlinds", "NominalSize='" & Me!NominalSize & "'")[/tt]

If NominalSize is defined as numeric in TblPricesWoodBlinds then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886