combo boxes failure...HELP! (1 Viewer)

[MSUKevin]

This issue has been driving me bananas so if someone could help I would really appreciate it!!!
I have created a database to track sales. I have 3 tables: "Transactions" "Transaction Details" and "Products". The setup is very similar to the orders entry sample database. What I am having problems with is that I want to have a combo box list the product name and price per unit. I built a query for my product ID combo box that includes the fields: Product ID, Product Name, and Unit Price. When the combo box is opened it shows two columns: product name and unit price.
I am trying to set up this box so that when an entry is selected, the unit price is automatically updated from the combo box to the unit price text box next to it. This is necessary to prevent error. I have seen this type of operation done in the orders entry sample database in Access '97 but I can't for the life of me figure out how they do it.

Also... My knowledge of Access is limited at best (especially when it comes to macros and VBA) so if anyone has an idea to help please break it down to the very basics, or babysteps... (I know this is time consuming but I'm a real rookie

)

Thanks in advance...

"The Rookie"

 

[Pat Hartman]

In the AfterUpdate event of the combobox, put code similar to the following (change the object names as necessary):

Me.txtYourUnitPrice = Me.YourCombo.Column(2)

This code takes the value from the third column of the combo's recordsource (this is a zero based array so the third column is actually referenced as 2) and places it in the unit price control. Make sure that the Name property of the unit price control is different from the actual column name referenced by the control source. I normally just prefix the control source name with the letters "txt".

 

[annavp]

try this

combo0 is the combox and list to update
text3 is the textbox with the new value

Private Sub Combo0_AfterUpdate()
Dim SQL As String
Dim firstColumn As String
firstColumn = Combo0.Column(0)
SQL = "UPDATE table1 SET [test 2] = '" & Text3 & "' where [id test] =" & firstColumn
CurrentDb.Execute SQL
Combo0.Requery
End Sub

I hope this points you in the right direction
if not, send mail

anna