How can I display another table's customer data on an order form?

[bricklebrit]

Hello,

I have an order form with a subform with order details. The orderform allows you to select the customer's name and then input all the order details in the subform.

I would like to create fields that once I select the Customer's Name, it plugs all customer's query information into corresponding field on the same main form:

My variables in the form are:
[CustomerID] (links to the order & order details)

In the Customer Query, the variables are:
[CustomerName]

[Email2]
[Address]
[Country]
[Notes]
[Sum Of PaidInFull] (this is a true/false)
[Sum Of Sampler1] (this is a true/false)
[Sum of TotalOrderCost]
[Count Of Orders]

So... onUpdate[CustomerID], I want all the corresponding Customer Query information to display on the same form -- could anybody help me out?

Thanks in advance!

P.S. Would it be best to create another subform inside the main form so onUpdate of the CustomerID I could edit the data in the same form? Any help or advance is appreciated!

 

[Rich]

Don't understand, what does this mean
[Sum Of PaidInFull] (this is a true/false)?

 

[bricklebrit]

Hi Rich,

Wow! Thanks for your prompt response.

Sorry about the poor description. The value for [Sum of PaidInFull] is a true or false statement like a checkbox.

The [Sum of Sampler1] value is actually a yes/no statement -- or a checkbox.

I hope that clarifies things. I also realized that calling a subform up wouldn't be a good idea because I can't edit the query its pulling all the data from. I guess, the perfect system would:

onUpdate[CustomerID] ...

1. pull up a subform with corresponding values:
[CustomerName]

[Email2]
[Address]
[Country]
[Notes]

2. plug the values from a query into blank fields on the form for the values:
[Sum Of PaidInFull]
[Sum Of Sampler1]
[Sum of TotalOrderCost]
[Count Of Orders]

I hope I'm not asking too much. Please let me know if you have any suggestions. Thanks again,
Jon

 

[Pat Hartman]

Read the help entry for "auto lookup queries". It explains how this feature works.

 

[bricklebrit]

Thanks for those keywords -- I didn't know exactly where to start!

Anyway, I did get the subforms, but it doesn't automatically update the address fields. I have to click forward and then back on the record to see the update

Is there some script I can perform to:

onUpdate of the CustomerID field, it will update the subform [CustomersQuery]

Also the subforms all have the standard buttons at the bottom of their frames that look something like:

"Record |< < 1 >"

Is there a way to turn the subform buttons off so it looks like just part of the form?

Thanks in advance for your help.
Jon

 

[DBL]

You can turn the navigation buttons off in the form properties for the subform.

Try

Me.refresh

on the After Update event of Customer ID field

 

[bricklebrit]

DBL -

Thanks very much -- both solutions worked beautifully!

Jon