trailblazzyr55
asked on
MS Access drop down to populate other fields in form
Hello,
I'm working with an MS Access form and have a drop down thats getting values from a table, I'm selecting three columns
Items (table) :FIELDS: ItemID(PK) , ItemName, ItemPrice
My form updates another table called "Sales"
Sales (table) : FIELDS: SaleID(PK), SaleDate, ItemID(should populate from drop down id), ItemPrice(Should update from field which was populated by the drop down)
Currently on my drop down I have it named "ITEM" and the control source is: "ItemID" and the row source is being populated from a select statement grabbing the three columns mentioned above from the Items table.
The price field which is populated from the drop down has the control source: =ITEM.column(2) which is the prices from the select statement populating the drop down.
HERE's the issue, now that the price field has the control source as =ITEM.column(2), it's not updating the field in the table for the price. If I add the name of the column to update in the table for price which is "ItemPrice" then I don't have the combo updating the price, but it gets saved in the table becuase the control source is now pointing there.
How do I tell the combo to set that price field based on what item's selected, but then have that price field still update the record in my table correctly....?
Thanks in advance,
~trail
I'm working with an MS Access form and have a drop down thats getting values from a table, I'm selecting three columns
Items (table) :FIELDS: ItemID(PK) , ItemName, ItemPrice
My form updates another table called "Sales"
Sales (table) : FIELDS: SaleID(PK), SaleDate, ItemID(should populate from drop down id), ItemPrice(Should update from field which was populated by the drop down)
Currently on my drop down I have it named "ITEM" and the control source is: "ItemID" and the row source is being populated from a select statement grabbing the three columns mentioned above from the Items table.
The price field which is populated from the drop down has the control source: =ITEM.column(2) which is the prices from the select statement populating the drop down.
HERE's the issue, now that the price field has the control source as =ITEM.column(2), it's not updating the field in the table for the price. If I add the name of the column to update in the table for price which is "ItemPrice" then I don't have the combo updating the price, but it gets saved in the table becuase the control source is now pointing there.
How do I tell the combo to set that price field based on what item's selected, but then have that price field still update the record in my table correctly....?
Thanks in advance,
~trail
Forgot to say, the code :
Sub ITEM_AfterUpdate()
Me!PriceField = ITEM.column(2)
End Sub
to add the code to the drop down, open the properties of the Dropdown, then from the Events Tab, you will see a row call AfterUpdate, should be the second one down. Select [Event Procedure] and then click the button that appears with the 3 dots.
Paste the code above into the window that opens.
Sub ITEM_AfterUpdate()
Me!PriceField = ITEM.column(2)
End Sub
to add the code to the drop down, open the properties of the Dropdown, then from the Events Tab, you will see a row call AfterUpdate, should be the second one down. Select [Event Procedure] and then click the button that appears with the 3 dots.
Paste the code above into the window that opens.
ASKER
I tried that, but for some reason now when I exit out and come back to the same form, the drop down is not populated anymore with the value from that record. If I have just the straight control source in there wihout the query and that code then it seems to work, is there something I need to set for the combo box that will grab the value from the query when it loads?
Thanks!
Thanks!
what is the control source of the ITEM list?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you get sorted? if so please close the question. otherwise let me know if you still need help
Mac
Mac
ASKER
Sorry for the delay macsta, I've been moving house cross country. I was able to get it working based on you advice, thanks!
~trail
~trail
most welcome
Set the control source of PriceField to a Price Field in the table.
Sub ITEM_AfterUpdate()
Me!PriceField = ITEM.column(2)
End Sub
Then when you change the dropdown the price field will automaticly update and store the value.
Hope this helps