Making price appear automatically in form

[ryanwales]

Hi and thanks for reading! Hopefully you can help me,

I have a form, namely QuoteForm, where the user enters data resulting in saving a record and printing out the Quote.

The user has the ability to choose multiple products via a subform (QuoteDetails) that has a master/child link with the QuoteForm.

Here goes - In QuoteDetails, the user selects the product name, quantity, list price, discount. What I would like to happen is once the user selects the product, the list price should automatically appear

All products are stored in the Products table with assigned prices. What I am after is once the product is chosen, the ListPrice in the Products table appears on ListPrice in quoteDetails.

There are relationships throughout the form and subform listed below:
Quote -> CustomerID (Customer Table)
Salesperson (Employee Table)

QuoteDetails -> QuoteID (Quote)
ProductID -> ID (Products Table)
ProductName -> ProductName (")



I have looked around but and I see I may have to create a query and create the subform from that? Or is there a way to make the ListPrice display another way?

Thank you in advance

[pbaldy]

Sounds like this would work:

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

[ryanwales]

that looks tasty. Hopefully I can nip this in the bud! Thanks

[pbaldy]

No problem; post back if you get stuck.

[ryanwales]

Ignore this, wrote the wrong thing in this field and cant find delete post :S

[ryanwales]

works like a charm. thank you very much!

On from this..there is a sum made (in the footer of the subform) from the totals of the products which is then pushed to the Subtotal in the main form. At the moment I just have the textbox (txtSum) as a control source in txtSubtotal. What I need however, much like the initial problem, is for it to push to the subtotal and be stored in subtotal.

Do I create an after Update in the txtSum field in VB just like the list price?
Something like :

QuoteNew.txtSubtotal = Me.txtSum ?

Just tried the code above, no errors yet no result. problem with how i'm calling the master form perhaps?

[pbaldy]

Looks like it. Here's a good reference on the proper syntax:

Forms Refer to Form and Subform properties and controls

[ryanwales]

I looked at this but I wasnt sure which one would be correct for a field from one subform to the main form?

[pbaldy]

Then to be safest you can always use the full reference from the "Not in these forms" section.

[ryanwales]

I've tried various ones. none come up with an error however none show the data! Do any of these work for text-box's? Any way you can post an example? i want txtSum's data from the subform QuoteDetails (Child10) to be txtQuoteSubtotal in the QuoteNew form

[pbaldy]

Forms!QuoteNew.txtQuoteSubtotal = Me.txtSum

Though I probably wouldn't save the subtotal with the main form's data. I've done it, and users have an uncanny ability to find ways to get the calculated value out of whack with what it should be.

[ryanwales]

it doesnt show in the form! i even tried it in a completely different text box.

what would you suggest?

[pbaldy]

Where do you have the code?

[ryanwales]

Where do you have the code?

I placed the code in the after update section of txtSum. SHould it be somewhere else?

[pbaldy]

A calculated control's update event doesn't fire when the value changes. You need to use another event, perhaps the after update event of the control the user inputs into. This is one of the many reasons we recommend not saving calculated values.