autofill field from selection in combo box

[bdapsu]

Hi all,

I am fairly experienced with Access but am stumped. I have a table with a primary key ID field, product name, and product price. On an order form, I want to select the product from a combo box and have that product's price automatically populate a text box beside it for further calculations. Any help would be much appreciated, thank you!

 

[RuralGuy]

Private Sub ComboBox1_AfterUpdate

Me.TextBox = ComboBox1.Column(3) ' the column with the price in it.

End Sub

 

[ShaneMan]

Access starts numbering columns at 0, so if you have 3 columns and the price is in the third column then you would want:

Me.TextBox = ComboBoxName.Column(2)

Hope this helps,
Shane

 

[bdapsu]

Thanks, it autofilled a price for the product, but for some reason it rounds up or down to the nearest dollar, i.e. the product price in my table is $27.36 and it automatically fills in $27.00. Any thoughts?

Thanks for the autofill tips; I'm printing this thread for future reference!

 

[RuralGuy]

Best guess? Me.TextBox is bound to a field that is an integer.

 

[bdapsu]

You guessed correctly! I changed it to single and it works fine now. Thanks again.

 

[RuralGuy]

You're welcome. Might I suggest the Currency data type for any money values.

 

[ST4RCUTTER]

Can you update multiple text boxes simultaneously? I have a combo that needs to update two text boxes. The first is working correctly but the second is not. Here is the code I am using:

Private Sub cboTapeUsed_AfterUpdate()
Me.txtTapecost = cboTapeUsed.Column(1)
Me.txtTapevalue = cboTapeUsed.Column(2)
End Sub

This is where:
column 0 is the tape type
column 1 is the tape cost
column 3 is the tape market value

Do these need to be in separate Private Sub's - - Scratch that...I see that causes ambiguosity due to having multiple After_Updates

 

[RuralGuy]

What you posted should work but I would add the Me. scoping prefix.

Private Sub cboTapeUsed_AfterUpdate()
Me.txtTapecost = [b]Me.[/b]cboTapeUsed.Column(1)
Me.txtTapevalue = [b]Me.[/b]cboTapeUsed.Column(2)
End Sub

You should be able to put as many in there as you want or need.

 

[ST4RCUTTER]

Thanks again RuralGuy. You are the best! I'll try it out.

 

[ST4RCUTTER]

Well, I added the leading "Me." but this did not correct the issue. If I change the VBA to read:

Private Sub cboTapeUsed_AfterUpdate()
Me.txtTapecost = Me.cboTapeUsed.Column(1)
Me.txtTapevalue = Me.cboTapeUsed.Column(1)
End Sub

...then I do get updates in the text box. This tells me that there is nothing wrong with the text box but something in the way I'm selecting data in the combo box cboTapeUsed. Here is my statement:

SELECT Media.MediaType, Media.Cost, Media.TapeValue FROM Media ORDER BY [MediaType];

Strange...it seems that I am selecting the correct fields, and when I set the list field count to 3 and the list field width wide enough to see all 3 fields I see that the TapeValue column (2) is there to be selected. The value is just not making it over to the text box.

 

[RuralGuy]

Something is strange. Try the following diagnostic code:

Private Sub cboTapeUsed_AfterUpdate()
Me.txtTapecost = Me.cboTapeUsed.Column(1)
[COLOR="Red"]MsgBox "Tape Value should be [" & Me.cboTapeUsed.Column(2) & "]"[/COLOR]
Me.txtTapevalue = Me.cboTapeUsed.Column(2)
End Sub

 

[ST4RCUTTER]

A dialog box pops up that says:

Tape Value should be []

 

[RuralGuy]

You *are* leaving the Column Count at three, right?

 

[ST4RCUTTER]

Egg...on...face.

No, I had adjusted it to 1 to only show the first field in the drop down. I assume that there is a better way to adjust the list width.

Thanks again man...you have solved my delima and shown me more.

 

[RuralGuy]

You can set the Column Width to 1";0";0" and all three are still available but only the 1st column will display.