Auto Populate field using expression Builder

[bharathsadanand]

Hi All,

I have 3 tables.
1) Products 2) Orders 3) Order Details.

I have created a form that has the order on top and it has a sub form with the order details in the bottom. (So basically 1 order will have multiple rows)

I have a combo box that shows "Retail" or "Project".
Based on what the user selects I want the Unit Price value in the order details sub form to be populated with either "UnitPriceRetail" or "UnitPriceProject" from the Products table.

Please help.

 

[pbaldy]

You could adapt this:

http://www.baldyweb.com/Autofill.htm

add a test of that combo and then grab the appropriate column.

 

[bharathsadanand]

Thanks for your reply but I have already gone through that thread.... and quite didnt know how to implement.

Dont know if im doing something wrong... need hand holding.

So what Ive got is
Parent Form with Field: RetailorProject - that has its record source as the Orders table. ( In the orders table it is a text box with Row Source as "Retail"; "Project")

Now I have a sub form which has the order details (A single order can have multiple order detail line items) Here I have a field called Unit Price.

Now depending on the value selected in the parent form for RetailorProject I want the Unit Price field to be populated with one of two values from the Products table ... Either UnitPriceRetail or UnitPriceProject.

 

[pbaldy]

Since I can't see what you've tried, I don't know if you're doing something wrong either.

 

[bharathsadanand]

Hi Paul,

I am herewith attaching my work so far... please let me know if I have built the db correctly. The problem Im having is with the form Orders.

Thanks for the help. Appreciate it.

 

[pbaldy]

I get a password prompt trying to unzip the db.

 

[bharathsadanand]

Paul if the zip file asks for a password... type in livingwatersystemz and it should get you through.

Regards
Bharath

 

[pbaldy]

Not the way I'd go, but it seems to be working. What's the problem?

 

[bharathsadanand]

Hmm... "not the way Id go"... is something that we need to discuss... coz I want this built proper.... I tried to normalize it as much as possible... but my concepts are a bit hazy.... dont want to build something that will break later... so plz comment on what I have done so far and I will implement the changes....

By the way ... just looked up John Galt ... looks like an interesting read.

Please give me your suggestions on how to improve the db... then we can take it the next stage.....

B

 

[pbaldy]

That comment was limited to the method used to get the price. I'd include the price fields in the combo as detailed in the link above. Your method uses DLookup to get the value. The combo has already made a trip to the products table to get data, so why make another trip with DLookup? Not saying it won't work, but it's less efficient.

In other words, my code would choose the appropriate column of the combo rowsource depending on the selection on the main form.

 

[bharathsadanand]

So im trying to put in "SELECT RetailorProject.Orders,UnitPriceRetail.Products,UnitPriceProject.Products From Orders, Products" into the Row Source of RetailorProduct combo box in the Orders form... is that correct... ]

 

[pbaldy]

No. It's currently

SELECT Products.ProductID, Products.ProductName
FROM Products;

and would change to

SELECT Products.ProductID, Products.ProductName, Products.UnitPriceRetail, Products.UnitPriceProject
FROM Products;

You would also have to adjust the column count and column width properties of the combo.

 

[bharathsadanand]

Ok I think i got that part right for ProductID in the sub form... next step is to try to link the result of the chosen value of RetailorProject to this link.....

I have typed in the following into the Control Source of "Unit Price" in the sub form.
=IIf([Forms]![Orders]![RetailorProject]="Retail",[UnitPrice]=[ProductID].[Column](3),[UnitPrice]=[ProductID].[Column](4))

 

[pbaldy]

You want the second method from that link, placing the value into the textbox. If you use that, the value won't save to the table. As I mentioned, you'd adapt it to test the main form combo.

 

[bharathsadanand]

Sorry to bug you Paul... but still struggling...

I have entered

=IIf([Forms]![Orders]![RetailorProject]="Retail",[Me].[UnitPrice]=[Me].[ProductID].[Column](3),[Me].[UnitPrice]=[Me].[ProductID].[Column](4))

in my control source for my UnitPrice text box field. Is that correct??

 

[pbaldy]

You're missing my point. The control source should still be the field name. In the code, instead of:

UnitPrice = IIf(Forms!Orders!RetailorProject = "Retail", DLookup("UnitPriceRetail", "Products", "ProductID=" & ProductID), DLookup("UnitPriceProject", "Products", "ProductID=" & ProductID))

you'd have:

  If Forms!Orders!RetailorProject = "Retail" Then
    UnitPrice = Me.ProductID.Column(2)
  Else
    UnitPrice = Me.ProductID.Column(3)
  End If

 

[bharathsadanand]

Thanks Paul... got it to work.

Used the following.

Private Sub RetailorProject_Change()
If Me.RetailorProject = "Retail" Then
Me.Order_Details_Subform.Form.UnitPrice = Me.Order_Details_Subform.Form.ProductID.Column(2)

Else
Me.Order_Details_Subform.Form.UnitPrice = Me.Order_Details_Subform.Form.ProductID.Column(3)
End If
End Sub

_________________________________________________

Private Sub ProductID_Change()
If Forms!Orders!RetailorProject = "Retail" Then
'MsgBox Me.ProductID.Column(2)
Me.UnitPrice = Me.ProductID.Column(2)
End If
If Forms!Orders!RetailorProject = "Project" Then
'MsgBox Me.ProductID.Column(3)
Me.UnitPrice = Me.ProductID.Column(3)
End If
End Sub

_______________________________________________

So if the user changes a product, based on the retail or project selection it updates the Unit price accordingly.

The other way works too but only for the current record.

i.e When I have a number of rows in my sub form if I change the status from retail to project in my parent form, only the active row price gets changed.
How do I make it change all the prices for all the rows in the sub form.

Regards
Bharath

 

[pbaldy]

Did you sort this out? I'd probably use an update query that used the order ID as a criteria, then requery the subform.