Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    97 - Populating Field Based on Another Field

    I have a database in Access 97 tracking land use. There is a combo box in which a user can select the transaction (Permit, Easement, Lease...). There are 6 options, limited to list. I want to have another field with a single-letter type/key based on the selection in the Transaction field.

    For instance, the user selects Permit. I want another field (Type) that will enter a P (permit) or E (Easement) or L (lease), etc.

    I don't know how to use VB, so I'm hoping someone can tell me how to set this up so the user doesn't have to remember to enter the letter code in the Type field - it is automatically entered based on the Transaction selected.

    Thanks!

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 97 - Populating Field Based on Another Field

    This is how I would do it. Open your form in design mode. Left click on the combo box, right click and open Properties. Click on the Events tab.

    On the After Update or On Lost Focus, left click anywhere on the row. At the far right of the row, a little box becomes visible that has three periods in it (...). Left click on the little box. When you do that, a Choose Builder box opens. Select Code Builder.

    That will open a Class Module. Don't get excited. Your cursor will be between two lines of text: Private Sub Form_AfterUpdate() (or Private Sub Form_LostFocus()) and End Sub.

    Between these two lines, use an If, Then, Else statement, something like this:

    If Me![Transaction] = "Permit" Then
    Me![Type] = "P"
    ElseIf
    Me![Transaction] = "Easement" Then
    Me![Type] = "E"
    ElseIf
    and so on until you have all transactions listed. The ElseIf after the fifth Transaction can just be Me![Type] = "SixthOne", whatever it is called.

    You must end the If statement with an End If. If you don't, Access will tell you.

    Hope this helps. If this is wrong or if there is a simpler way to do what you want, someone else on this forum will let you know.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 97 - Populating Field Based on Another Field

    Is the combobox bound to a field? If so, why would you enter what amounts to the same information in another field? That's poor design. If you want to display the full text (i.e. Permit) but you want to store the "P", that's easy to do by simply including both the single letter and the name in the values list that populates your combobox. So the Values list string might look like this:

    P;Permit;E;Easement;L;Lease ... etc.

    If you set up your combox box for 2 columns and make the width of the first column 0", the user will see the word but actually select the letter, which is what will be stored in the underying field.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •