Results 1 to 4 of 4
  1. #1
    AccessFrenemy is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    2

    Automatically populating second field based on form response


    I have 3 populated fields in 2 tables: Field A1 in Table A, and Field B1 and Field B2 in Table B. I have created a form which will populate another field (Field A2 in Table A) based on users' selections from a list box, with the response options (i.e., row source) extracted from Field B1. However, Field B1 and (in following) Field A2 are text fields. To facilitate some analyses, I also want to record the ID code (Field B2) corresponding to Field B1.

    Thus, when a user makes a selection that populates (text) Field A2, I want the (numerical) Field A3 to be populated with the corresponding ID code. I imagine this requires a query, but query development is the weakest part of my very spotty, limited knowledge of Access.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    Following good database practices, you would only store the corresponding ID you want from table B in table A. You should restructure table A as follows

    tableA
    -pktblAID primary key, autonumber
    -A1
    -fktblBID foreign key to tableB long number integer field

    tableB
    -pktblBID primary key, autonumber
    -B2 text field

    When doing this in a form, you would base your form on table A and then use a combo box tied to fktblBID. The combo box would use table b as its row source with the bound field being pktblBID. You would include both pktblBID and B2 in the combo box. If you want to display the B2 field on the form (but you would not store it in table A), you can create a textbox on the form and reference the combo box column that holds the data

    =comboboxname.column(1)
    Access starts counting at column 0, so if the row source is as follows column(1) returns the value in B2

    SELECT pktblBID, B2 FROM tableB

    Make sure to establish the relationship between the two tables in the relationship window.

  3. #3
    AccessFrenemy is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Jul 2011
    Posts
    2
    Thanks for the advice! It's almost exactly what I was looking for. I've progressed successfully through the point of including pktblBID and B2 in the combo box. The resulting form records the numerical code, which is my goal. However, the text data from B2 is not recorded in Table A (I wasn't clear that I want this recorded as well). Before I go on to your additional advice, can you tell me how I can make sure the text from B2 gets recorded in Table A (the form wizard appears to allow only one of the two displayed fields to be recorded.)

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    However, the text data from B2 is not recorded in Table A (I wasn't clear that I want this recorded as well).
    Storing both the ID and the text of table B in table A violates normalization rules and should not be done.

    If you want to "see" everything together you would create a query that joins the two tables and displays the fields you want.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-18-2011, 07:18 AM
  2. Replies: 7
    Last Post: 11-26-2010, 07:54 PM
  3. Populating attachment field based on text box
    By justinwright in forum Forms
    Replies: 16
    Last Post: 09-22-2010, 08:38 AM
  4. Replies: 2
    Last Post: 09-20-2010, 09:02 PM
  5. Replies: 4
    Last Post: 01-19-2010, 05:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums