• 97 – Populating Field Based on Another Field

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » 97 – Populating Field Based on Another Field

    Author
    Topic
    WSMelanieB
    AskWoody Lounger
    March 5, 2001 at 6:33 pm #353537

    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!

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WSLonnieB
      AskWoody Lounger
      March 5, 2001 at 8:53 pm #517450

      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.

      Reply | Quote
    • WScharlotte
      AskWoody Lounger
      March 6, 2001 at 6:30 am #517504

      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.

      Reply | Quote
    Viewing 1 reply thread
    Reply To: 97 – Populating Field Based on Another Field

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.