Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Yes there is!</p> <p>Obviously, you need to be able to relate the combo box selection to the value you wish to be populated into the other field(s). Assuming that you have a 1:1 relationship with the PK (since you want to display only one value in your form), you can use the AfterUpdate event plus the DLookup() function to retrieve a related value using the PK.</p> <p>As a simple example, I set up a table named Foods as follows:</p> <blockquote> <p>FoodID, FoodName, FoodCategory</p> <p>1, Orange, Fruits</p> <p>2, Chicken, Poultry</p> <p>3, Almond, Nuts</p> <p>4, Lettuce, Vegetables</p> </blockquote> <p>In the form, I have a control that selects the FoodID as the PK bound value named ComboFoods, and an unbound text box control named TextFoodCategory that we will populate with the FoodCategory from the Foods table.</p> <p>I've assigned the following code to the AfterUpdate event of the Combo Box so that when the value of the combo box changes, the text box will be populated:</p> <blockquote> <p>Private Sub ComboFoods_AfterUpdate()</p> <p>'Create a variable to store the combo box primary key selection</p> <p>Dim VarComboKey As Integer</p> <p>'Create a variable to store the DLookup results</p> <p>Dim VarFoodCat As Variant</p> <p>'Capture the primary key of the combo box</p> <p>VarComboKey = Me.ComboFoods.Value</p> <p>'Retrieve the related field value</p> <p>VarFoodCat = DLookup("[FoodCategory]", "[Foods]", "[FoodID] = " &amp; VarComboKey)</p> <p>'Set the value of the text box to the variable</p> <p>Me.TextFoodCategory.Value = VarFoodCat</p> </blockquote> <p>This will return the FoodCategory that is related to PK. This is all using one table, but the DLookup statement can be modified to reference any query or table that contains the PK.</p> <p>Please note that DLookup will only work properly when the PK is unique in the data you are referencing. It will not work in a one to many relationship unless you specify other criteria that restrict the results to one record. There are other ways to use SQL queries and recordsets within VBA if you need to return multiple records, but that this out of scope for this question.</p> <p>This worked when tested - best of luck! </p>
    singulars
    1. This table or related slice is empty.
    1. POAuto Populate fields in MS Access Form
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJAGAnalyst
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COim not very proficient in VBA, but i dont understand what "ComboFoods" is supposed to represent? and what does "Me." and "Value." actually mean?
      singulars
      1. PO
      1. USJT2013
    2. COIn my example, ComboFoods is the name of the Combo Box control that selects the PK. Me.* is shorthand for referencing the current database and form prior to referencing the control. You can fully qualify this instead if you prefer. Overall, the idea is to use DLookup or a query in conjunction with the AfterUpdate event.
      singulars
      1. PO
      1. USJAGAnalyst
    3. COive got this to work perfectly fine. However im realizing that i need to return multiple values (ie. 1 to many). I will ammend my question
      singulars
      1. PO
      1. USJT2013
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload