Dropdown that updates two adjacent fields

[Shemp]

I really need some help on this, please. I need to be able to use
dropdown that links to a database, and based on the selection ID, I
want to update values in two adjacent fields.

Here's how to set it up:
1 - Create a database in Access (or however you want) with just three
fields: Part No., Description, and UnitPrice. Give it some values, so
it looks like this:
PartNo Description UnitPrice
1001 WidgetA 100
1002 WidgetB 200
1003 WidgetC 300
Save it and export to XML.

2 - Create a blank new form with a Repeating Table containing 3
columns. Name the three textboxes txtPartNo, ddnDescription, and
txtUnitPrice, respectively.

3 - Change the middle textbox to a dropdown, and point its data source
to the XML table you created in 1.

So far, so good. What I want to do is be able to change the
description in ddnDescription and have txtPartNo and txtUnitPrice
change along with it. I can get the UnitPrice to work but not the
PartNo as well. Can anyone help?

If you want to see more of what I am doing, add a Quantity column and
an Extended Price column as well. The math is fairly easy on that, but
making the other three fields synch up is confusing to me.

If you would like me to email you with a small sample page that
illustrates this, get back with me at Gmail. Thanks!

 

[Scott L. Heim [MSFT]]

Hi Jeff,

Here are the steps I completed to make this work:

- Followed your instructions for making the database, form, table, naming
convention, etc.
- Set the data source of ddnDescription to the XML file and set both the
"Value" and "Display Name" fields to: Description

** New steps **

- Right-click on ddnDescription and choose Properties
- Click the Rules button
- Click Add
- Click Add Action
- From the Action box choose: Set a field's value
- Click the button next to Field and choose txtPartNo from your "Main" data
source and then click OK
- Click the builder button next to Value
- Click Insert Field or Group
- From the data source box select your secondary data source
- Select PartNo
- Click the Filter Data button
- Click Add
- In the 1st drop-down box choose: Description
- In the 2nd drop-down box choose: Is Equal To
- In the 3rd drop-down box choose: Insert Field or Group
- From the data source box select Main data source
- Expand the groups until you can select: ddnDescription and click OK
- Click OK again
- Click Modify (this is to modify the filter we just set)
- From the 1st drop-down box choose: Expression - you should now see the
XPath expression for what we just created:

../Description =
xdXDocument:get-DOM()/my:myFields/my:group1/my:group2/my:ddnDescription

- Modify this to the following:

../Description = current()/../my:ddnDescription

- Click OK 5 times to get you back to the "Rule" dialog box
- Repeat the above steps (beginning with Add Action) with the only change
being to set the "txtUnitPrice" field - the filter steps will remain the
same.
- Save and Preview the form - you should be able to select a description
from ddnDescription and the PartNo and UnitPrice fields should complete as
expected!

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Shemp]

That did the trick, Scott!

Sorry, I've been on vacation and otherwise unable to test this yet, but
I am so appreciative for all the help you've given me on this project.
This was my last significant bug on this form, and now I can move on to
other things.

You totally rock my lame ass!