Access Form

[sritter]

Hi, here is my situation I have two tables one is called Primary which includes the fields UnitPrice and PartNo and a few other fields. I also have created another table called Prices with just the UnitPrice and PartNo fields. I have created a combobox that lists the Part Numbers from the table, when a Part Number is selected I want a text box called Price to show the appropriate UnitPrice. I've tried everything I can think of and it still will not work. I know this is basic.

Thanks
Sean

 

[RickSpr]

Actually, that you have UnitPrice and PartNo fields in more than one table makes me suspect that your table design might not be normalized. That can cause you bad problems in a relational database, unless you know just what you're doing. But it might also be that you have a valid reason for having fields with the same name in different tables.

There are two basic ways to do this, one involving more code than the other. The first way is to include the UnitPrice in your combo box, making it a hidden field if you don't want it to show. You can include the price by just adding the UnitPrice field to the Row Source for the combo box, and adding 1 to the Column Count. If you want it to be hidden, set the Column Widths to ";0" (assuming it's the 2nd column).

Now to get the price into your text box. In the combo box's AfterUpdate event procedure, code this:
Me!txtUnitPrice = Me!cboPart.Column(1, Me!cboPart.ListIndex)
This assigns the data from the second column of the selected row of the list to the txtUnitPrice control. (You use 1 for the second column because the Column property counts from 0).

The second method is to do a lookup of the price in the Prices table, by finding the record with a matching PartNo. You would also do this in the combo box's AfterUpdate event procedure. It would look like this:
Private Sub cboPart_AfterUpdate()
Dim db As Database, rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Parts", dbOpenDynaset)
rst.FindFirst "PartNo = '" & Me!cboPart.Value & "'"
If Not rst.NoMatch Then
txtUnitPrice = rst!UnitPrice
End If
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub Rick Sprague