Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access Form

Access Form

Access Form

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.


RE: Access Form

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
        Set rst = Nothing
        Set db = Nothing
    End Sub

Rick Sprague

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close